I'm attempting to build a query to provide the following points of information
Machine Name, Serial Number, Bitlocker Status, Last Security Update Date, Last Reboot, AV Version, Last AV Signature Update
I'm new to SCCM, but pretty familiar with MSSQL and DML, but I'm honestly a little flummoxed by the data structure and depth in SCCM. I'm betting I cannot get AV information (which is fine, as I can merge the SCCM with a separate report for that data), but it's worth a shot to ask. What I'm looking for is how to properly group, join, and select each of those items so that they roll up for each machine.
So, for each Machine Name, list the other items. I took a crack at the last security update, but was predominantly unsuccessful:
SELECT
SCCM_Ext.vex_R_System.Name0 as 'System'
,MAX(SCCM_Ext.vex_GS_ADD_REMOVE_PROGRAMS.InstallDate0) as 'Last Security Update'
FROM
SCCM_Ext.vex_GS_ADD_REMOVE_PROGRAMS
INNER JOIN SCCM_Ext.vex_R_System
ON SCCM_Ext.vex_GS_ADD_REMOVE_PROGRAMS.ResourceID = SCCM_Ext.vex_R_System.ResourceID
WHERE
SCCM_Ext.vex_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE N'Security Update%'
GROUP BY SCCM_Ext.vex_R_System.Name0
I would deeply appreciate some pointers, or if someone has drafted something like that, I'd love to see how. I feel like I'm going to make a mistake on assumptions about the data architecture given my inexperience with SCCM.
Cheers in advance,
A.