CMPivot last patch date

Question

CMPivot last patch date

Steve Zeke 21

Im new to using CMPivot and trying to create a query that shows the computer and latest patch date only, but stuck. I can get multiple entries of all the dates, but basically looking ot have the computer name in one column and the last patch date on the right...Ive made some progress, bur another snag is how to convert the time to ISO 8601 so i can sort by year-month-day....Any help is appreciated.

0 comments

1 answer

Your answer

Answer 1

AllenLiu-MSFT 49,441 Microsoft External Staff

@Steve Zeke
Thank you for posting in Microsoft Q&A forum.
Try this query:
QuickFixEngineering | summarize max(installedon) by Device
And I'm trying my best to find a way to modify the time type.

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

AllenLiu-MSFT 49,441 Reputation points Microsoft External Staff

2020-11-18T05:54:32.987+00:00

Base on my research, the return type of InstalledOn is string, and it could not be used for date sorting, when I try to use todatetime to convert it to date type, but failed because it's not usable for CMPivot, although todatetime is a scalar function of Kusto query language.

So maybe we can only export the result with below query and try to use excel to get the expected results.
QuickFixEngineering | project Device,InstalledOn
Steve Zeke 21 Reputation points

2020-11-18T11:45:11.75+00:00

Thanks for the response. I ran your query against one PC and it gave me the result of 8/24/20, but when I ran a query on all the updates on that PC (below), you can see 10/26/20 should be the result.

3/12/2019 12:00:00 AM
3/12/2019 12:00:00 AM
8/14/2020 12:00:00 AM
8/14/2020 12:00:00 AM
8/24/2020 12:00:00 AM
10/3/2020 12:00:00 AM
10/26/2020 12:00:00 AM
10/26/2020 12:00:00 AM
10/26/2020 12:00:00 AM
Steve Zeke 21 Reputation points

2020-11-18T11:46:22.57+00:00

I believe that is my problem. Need to change the date format to year-month-day but was unable to convert it either.
AllenLiu-MSFT 49,441 Reputation points Microsoft External Staff

2020-11-19T08:41:39.317+00:00

The return type of InstalledOn is String in stead of Time, so the max value is not accurate. If we can convert it to Time type, it should be work. But we can not.
So my recommendation is to use hardware inventory to collect the info, you just need to enable class Win32_QuickFixEngineering to your hardware inventory and trigger a hardware inventory for clients.
After these information collected, you can use SQL query against v_GS_CM_UPDATEDETAILS views to get the latest date.

Share via

CMPivot last patch date

1 answer

Your answer