CODEWITHSUNDEEP
azure-data-explorerkql
Vinny
Vinny

Reputation: 629

KQL Help: Need to trim the Datetime value

I need to trim the Datetime value in KQL.

I have Timer Trigger based Azure function which runs every 30 mins

("0 */30 * * * *")]

I have 2 datetime columns StartTime and EndTime. I am getting the Runtimes of Azure Function by summarizing min(StartTime) - max(EndTime). I want the min(StartTime) to trimmed to the actual start time of the Azure Function.

Example: If the min(StartTime) Column Value is "2021-10-25 10:02:26.7630995" then the StartTime should be "2021-10-25 10:00:00.000000"

And

If the min(StartTime) Column Value is "2021-10-25 10:32:26.7630995" then the StartTime should be "2021-10-25 10:30:00.000000"

My Code so far: (I need help in line #4 ) 

MyKustoTable | where isnotempty(RunID) and RunID > 41
| project RunID, CollectionTime, IngestionTime = ingestion_time()-30m
| summarize StartTime = min(CollectionTime), EndTime = max(IngestionTime) by RunID
| extend RBACDurationInMins = case((EndTime - StartTime)/1m > 30, "Trimmed StartTime", StartTime) 
| extend RBACDurationInMins = (EndTime - StartTime)/1m, ResourceType = "RBAC"
| project ResourceType, RunID, StartTime, EndTime, RBACDurationInMins
| sort by RunID desc

Upvotes: 1

Views: 687

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could use the bin() function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/binfunction

if the min(StartTime) Column Value is "2021-10-25 10:02:26.7630995" then the StartTime should be "2021-10-25 10:00:00.000000"

If the min(StartTime) Column Value is "2021-10-25 10:32:26.7630995" then the StartTime should be "2021-10-25 10:30:00.000000"

print dt1 = datetime(2021-10-25 10:02:26.7630995), 
      dt2 = datetime(2021-10-25 10:32:26.7630995)
| project result1 = bin(dt1, 30m),
          result2 = bin(dt2, 30m)
result1 result2
2021-10-25 10:00:00.0000000 2021-10-25 10:30:00.0000000

Upvotes: 2

Related Questions