How to split the datetime using KQL

Question

How to split the datetime using KQL

Son 316

Hi,

I am using a KQL query to retrieve Azure Update Manager patching reports. I am trying to change the format the date column is displayed in to simply YYYY-MM-DD. By default, the time is being displayed as YYYY-MM-DD:T00:00:00.000Z. I need to know how I can split this out. I tried using the split function but that did not allow me to extend the data out as it was expecting datetime.

Here is the KQL:

// Azure Update Management - Patch status
// Click the "Run query" command above to execute the query and see results.
patchinstallationresources
| where type has "softwarepatches" and properties !has "version"
| extend machineName = tostring(split(id, "/", 8)), resourceType = tostring(split(type, "/", 0)), tostring(rgName = split(id, "/", 4)), tostring(RunID = split(id, "/", 10))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications)
| where lTime > ago(30d)
| project lTime, machineName, patchName, kbId, classifications, installationState //, rgName, resourceType, RunID
|where classifications has "" "Security" or classifications has "Critical"
| where installationState has "Installed" or installationState has "Failed"
| order by ['machineName'] asc

You can see at the start of line 8 we are grabbing the time from the JSON properties.

| extend lTime = todatetime(prop.lastModifiedDateTime)

How do we make this work in the required format?

Example of how the data is displayed currently:

datetime

Son 316

I actually have a workaround for this but I don't think it is the most efficient way of doing it.

By adding the split later in the code after we have processed most of the query we achieve the desired result:

// Azure Update Management - Patch status
// Click the "Run query" command above to execute the query and see results.
patchinstallationresources
| where type has "softwarepatches" and properties !has "version"
| extend machineName = tostring(split(id, "/", 8)), resourceType = tostring(split(type, "/", 0)), tostring(rgName = split(id, "/", 4)), tostring(RunID = split(id, "/", 10))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications)
| where lTime > ago(7d)
| extend lTime = tostring(split(prop.lastModifiedDateTime,"T",0))
| project lTime, machineName, patchName, kbId, classifications, installationState //, rgName, resourceType, RunID
|where classifications has "" "Security" or classifications has "Critical"
| where installationState has "Installed" or installationState has "Failed"
| order by ['machineName'] asc

Answer accepted by question author

0 additional answers

Your answer

Son 316 Reputation points

2023-11-08T10:26:09.59+00:00

I actually have a workaround for this but I don't think it is the most efficient way of doing it.

By adding the split later in the code after we have processed most of the query we achieve the desired result:

// Azure Update Management - Patch status // Click the "Run query" command above to execute the query and see results. patchinstallationresources | where type has "softwarepatches" and properties !has "version" | extend machineName = tostring(split(id, "/", 8)), resourceType = tostring(split(type, "/", 0)), tostring(rgName = split(id, "/", 4)), tostring(RunID = split(id, "/", 10)) | extend prop = parse_json(properties) | extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications) | where lTime > ago(7d) | extend lTime = tostring(split(prop.lastModifiedDateTime,"T",0)) | project lTime, machineName, patchName, kbId, classifications, installationState //, rgName, resourceType, RunID |where classifications has "" "Security" or classifications has "Critical" | where installationState has "Installed" or installationState has "Failed" | order by ['machineName'] asc

Answer 1

SwathiDhanwada-MSFT 19,073 Moderator

Son To change the format of the date column, you can use the format_datetime() function in your KQL query. Here's an example query that formats the date column to display only the date in the YYYY-MM-DD format:

extend times = format_datetime(todatetime(properties.lastModifiedDateTime),'yyyy-MM-dd')

patchassessmentresources
| where type !has "softwarepatches"
| extend prop = parse_json(properties)
| extend lastTime = properties.lastModifiedDateTime
| extend times = format_datetime(todatetime(properties.lastModifiedDateTime),'yyyy-MM-dd')
| extend updateRollupCount = prop.availablePatchCountByClassification.updateRollup, featurePackCount = prop.availablePatchCountByClassification.featurePack, servicePackCount = prop.availablePatchCountByClassification.servicePack, definitionCount = prop.availablePatchCountByClassification.definition, securityCount = prop.availablePatchCountByClassification.security, criticalCount = prop.availablePatchCountByClassification.critical, updatesCount = prop.availablePatchCountByClassification.updates, toolsCount = prop.availablePatchCountByClassification.tools, otherCount = prop.availablePatchCountByClassification.other, OS = prop.osType
| project lastTime,times, id, OS, updateRollupCount, featurePackCount, servicePackCount, definitionCount, securityCount, criticalCount, updatesCount, toolsCount, otherCount

User's image

Son 316 Reputation points

2023-11-09T07:17:28.21+00:00

Hi, thanks for sending that through, unfortunately it does not work with the rest of my query

On line 9 I need to retain the ability to search for updates over a period of time as this is going to be a monthly automation which is sent out with the last 30 days worth of results.

Any idea how I can still make that work?

SwathiDhanwada-MSFT 19,073 Moderator

Son I made small modification to your query. I have added the filter condition before formatting the date. Kindly check it out if that works for you.

// Azure Update Management - Patch status

patchinstallationresources
| where type has "softwarepatches" and properties !has "version"
| extend machineName = tostring(split(id, "/", 8)), resourceType = tostring(split(type, "/", 0)), tostring(rgName = split(id, "/", 4)), tostring(RunID = split(id, "/", 10))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime)
| where lTime > ago(30d)
| extend lastTime= format_datetime(lTime,'yyyy-MM-dd'),patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications)
| project lastTime, machineName, patchName, kbId, classifications, installationState //, rgName, resourceType, RunID
|where classifications has "" "Security" or classifications has "Critical"
| where installationState has "Installed" or installationState has "Failed"
| order by ['machineName'] asc

Son 316 Reputation points

2023-11-09T10:37:50.5133333+00:00

Thanks Swathi, that worked. I had to change lastTime to matc lTime as it broke my JSON schema in the Logic App but all is working with that suggestion, appreciate the support!

Share via

How to split the datetime using KQL

0 additional answers

Your answer