Oct 10 2022 07:59 AM
I'm looking for windows cert expiry data for total days left by using below kusto query. I can get the remaining days left but it is not in right format. Need only days left.
KQL Query
Event
| where EventLog == "Microsoft-Windows-CertificateServicesClient-Lifecycle-System/Operational"
| where EventID == 1003
| parse EventData with * "<SubjectName>" subject: string "<" *
| parse EventData with * "<NotValidAfter>" CertExpDate: datetime "<" *
| extend DaysLeft = TimeGenerated - CertExpDate
| project Computer, CertExpDate, DaysLeft,EventID, RenderedDescription, subject, Message
//| top 20 by DaysLeft asc
| order by DaysLeft asc
Output "29.23:44:22.9440000"
I need to trim to above value in days format only. I'e "29".
I'm available on Microsoft Teams @email address removed for privacy reasons if anyone can help me instantly.
Thanks in advance.
Oct 11 2022 02:32 AM
Hello @Bhagyesh1984,
You can use "parse" operator to parse the output and then "project" only the number of days.
Jan 19 2023 08:22 PM
Jan 23 2023 01:57 AM
@kusto666 Yes,
Here is the query..
Event
| where EventLog == "Microsoft-Windows-CertificateServicesClient-Lifecycle-System/Operational"
| where EventID == 1003
| parse EventData with * "<SubjectName>" subject: string "<" *
| parse EventData with * "<NotValidAfter>" CertExpDate: datetime "<" *
| extend remainingday = CertExpDate - TimeGenerated
//| extend val = format_timespan(DaysLeft, 'd')
| extend DaysLeft = datetime_diff('day', CertExpDate, TimeGenerated)
//| summarize count() by subject, CertExpDate, val, RenderedDescription,Message, Computer
| where DaysLeft <= 45
| summarize max(TimeGenerated)by DaysLeft, Computer, CertExpDate, EventID,subject
//| summarize count() by subject, CertExpDate,EventID, RenderedDescription,Message, DaysLeft, Computer
//| top 20 by DaysLeft asc
//| order by DaysLeft asc