Suggestions for simple queries from Intune Data Warehouse tables

[Anonymous]

Hello,

 I am having some challenges with the Intune Data Warehouse tables. It seems most of what my users are asking for in the BI Service and NQL are not working as expected. I presume this is due to the large amount of relationship keys in all the various tables but I'm not sure that I have all of these as I did not bring in all 30+ tables.

This is probably a simple question for the community but using the Intune Data Warehouse tables with a star schema and collection here -  https://docs.microsoft.com/en-us/mem/intune/developer/intune-data-warehouse-collections

How do my users ask the following questions of the data? How would someone recommend I write these queries? 

(I understand I may need to place a filter down on a visual to show "active" or IsDeleted=False and what not).

Pecentage of Total Active Devices that have not checked in or reported in within seven (7) days?

Percentage (or Total Count) of devices with osVersion (this field has keys not the values) that are not current?

Pecentage of Total Active Devices with Crtical Vulnerabilties that are more than thirty (30) days old?

I would welcome any thoughts or assistance in this as almost all of the values are a field key, rather than the data itself. All the relationships are there as well. Thank you!

[Anonymous]

@lbendlin  thanks for the reply. Hopefully, you are familar with the Intune Data Warehouse. It's a star-schema that is very messy and looks like this (I cannot pull in all the tables it offers due to sheer memory and performance constraints).

I would like to create some visualizations on these devices in Table 'devices" but much of the data are keys to other tables. For instance, how would I write out a Natural Language Query that is

Pecentage of Total Active Devices with Crtical Vulnerabilties that are more than thirty (30) days old?

The Total count of deviceId from the Device table filtered by IsEnabled=True but ? what next ? which table from the schema provides this?

I was not able to find a table I could use for the remainder of the data (or unsure of which this is). So instead I downloaded this from Intune and unfortunately, Microsoft failed to provide a key in that data to relate to, so I only have a count of endpoints in each vulnerability (see second header image)(notice everything is a key to another table... reminds me of the Microsoft licensing model 🤣).

If you have a table you can suggest I use that is standard to the Intune Data Warehouse, I'll use that with the rest of the query.

If not, we would have to instead use the exported data from Intune - limited to the following fields (name is not the name of the device but the vulnerability CVE number)

Name

Severity

CVSS v3

Age (days)

Published

Updated

Has Exploit

Has Known Threats

Has Associated Alerts

Related Software

Exposed Machines

With these fields, I don't see a means to use Active or anything of that (wish there was a key for this or a table I could actually pulldown that didn't blow up BI). So in this case how can I write this?

Perecentage or number (since we don't know which vulnerability name and counts overlap) of the devices with critical (Severity) >= Age of 30 

Hopefully this makes sense?

[lbendlin]

Not exactly clear what your challenge is.  What have you tried and where are you stuck?