KQL query pass current time

Question

I have a requirement to capture changes done between two timeframes. I have powershell script that selects the $starttime and $endtime based on the script start time.

$startTime = "08:30:00"

$endTime = "11:30:00"

$queryChangeAn = @"

resourcechanges

| extend changeTime = todatetime(properties.changeAttributes.timestamp),

| where changeTime between (datetime($startTime) .. datetime($endTime))

| project changeTime, changedBy, properties, ['id'], name, tenantId, resourceGroup, subscriptionId,correlationId

"@

How do I pass the local time starttime and endtime? to KQL. I believe it is converting it to UTC.

Answer 1

Hi,

You can convert the time from utc to any timezone you desire via datetime_utc_to_local() function.

$startTime = "08:30:00"

$endTime = "11:30:00"

$queryChangeAn = @"

resourcechanges

| extend changeTime = datetime_utc_to_local(todatetime(properties.changeAttributes.timestamp),'America/Argentina/Salta'),

| where changeTime between (datetime($startTime) .. datetime($endTime))

| project changeTime, changedBy, properties, ['id'], name, tenantId, resourceGroup, subscriptionId,correlationId

"@

Note that you need to convert the time of the change to your local time and the dates you provided will be in local time already.

Other option is to convert your time inputs into utc and than you will not have to use datetime_utc_to_local() for the changeTime.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.