Hi,
I need a way of getting 4 columns out: Hostname, Last Logon Timestamp, Latest CU in SCCM, Installed CU
I've got both an SCCM and SQL Query that gets the first 2 columns easily, but not sure the best way of getting the 3rd and 4th. I don't want to do this per-collection or per-update, but I do want it split out in to Servers and Workstations.
Workstations List:
Win7-PC1 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win7-PC2 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC1 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC2 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC3 (1903) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC4 (1909) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC5 (2004) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Server List:
WinServer2012R2-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2012R2-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2016-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2016-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2019-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2019-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
and so on. I don't actually need the OS version listed... that's just to show that I need everything in one simple list. I then export this to another Excel sheet for VLOOKUP purposes against another data set. The other data is from AD with Get-ADComputer.
I'm fluent in SQL generally so that makes sense (especially as I could query it from my existing PowerShell with AD data). But not so fluent in the SCCM DB. If someone could just point me in the right direction that would be great.
I know:
How to get the Hostname and from what table
How to get the Last Logon and from what table
I don't know:
How to determine what SCCM has as the latest CU KB number for a given OS in a clean string: KBzzzzzzz (Article ID perhaps)
Work out what the latest CU KB is for each device
How to get the latest CU KB installed on a device
Hope all that makes sense.
Thanks!
I need a way of getting 4 columns out: Hostname, Last Logon Timestamp, Latest CU in SCCM, Installed CU
I've got both an SCCM and SQL Query that gets the first 2 columns easily, but not sure the best way of getting the 3rd and 4th. I don't want to do this per-collection or per-update, but I do want it split out in to Servers and Workstations.
Workstations List:
Win7-PC1 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win7-PC2 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC1 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC2 (1809) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC3 (1903) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC4 (1909) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Win10-PC5 (2004) | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
Server List:
WinServer2012R2-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2012R2-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2016-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2016-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2019-01 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
WinServer2019-02 | 01/01/1900 00:00:00 | KBxxxxxxx | KByyyyyyy
and so on. I don't actually need the OS version listed... that's just to show that I need everything in one simple list. I then export this to another Excel sheet for VLOOKUP purposes against another data set. The other data is from AD with Get-ADComputer.
I'm fluent in SQL generally so that makes sense (especially as I could query it from my existing PowerShell with AD data). But not so fluent in the SCCM DB. If someone could just point me in the right direction that would be great.
I know:
How to get the Hostname and from what table
How to get the Last Logon and from what table
I don't know:
How to determine what SCCM has as the latest CU KB number for a given OS in a clean string: KBzzzzzzz (Article ID perhaps)
Work out what the latest CU KB is for each device
How to get the latest CU KB installed on a device
Hope all that makes sense.
Thanks!
