Kusto language. Get one value only if the previous value in time is not the same

Question

Context to my very vague title: I have 4 virtual machines that send their logs to application insights. I retrieve the logs and transform this in a table with kusto language.

Table of outcome

Query: 
AzureActivity
| where ResourceProvider == "Microsoft.Compute" and ActivityStatus == "Succeeded" and OperationName == "Deallocate Virtual Machine"
| project DeallocateResource=Resource ,DeallocatedDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd') ,DeallocatedTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
| join kind=fullouter
(
AzureActivity
| where ResourceProvider == "Microsoft.Compute" and ActivityStatus == "Succeeded" and OperationName == "Start Virtual Machine"
| project StartupResource=Resource ,StartDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd') ,StartTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
)
on $right.StartupResource == $left.DeallocateResource
| where StartDate == DeallocatedDate
| project Resource=coalesce(StartupResource, DeallocateResource) ,
Date=format_datetime(todatetime(coalesce(StartDate, DeallocatedDate)), 'dd/MM/yyyy' ) 
 , StartTime= StartTime ,StopTime=DeallocatedTime , 
Runtime_Hours = format_datetime(datetime_add('minute',datetime_diff('minute', todatetime(strcat(StartDate , " " , DeallocatedTime )) , todatetime(strcat(StartDate , " " , StartTime ))), make_datetime(2017,1,1)), 'hh:mm') 
| sort by Date asc , Resource asc

As you can see the runtime is not correct when a VM is started at 8:15 and stopped at 8:58 and have runtimes of 12:43 hours then there is something wrong. In the activity log of the VM, I see that some colleague did some strange thing with the VM. And started it a couple of times (a minute after he started it again, probably a glitch when you click twice on the start button at the same time).

Activity Logs

I did find a theoretical solution to my problem: My query needs to change so that the runtime and even the start and stop times need to get logged in the time table only when the VM starts and followed by a stop. But atm I got all the "Start Virtual Machines" and all the "Stop Virtual Machines" and just order them in a table which causes the mix up in my result table.

But I can't seem to find the way to adjust this in my query. To say Get the start virtual machine only when it's the first of the day (when the previous is not start virtual machine) or the previous log is "deallocate virtual machine" because this is not by order start-stop. the time of the day needs to be in the formula. Get deallocate virtual machine only when the previous is a start virtual machine. and calculate the runtime of each run not for each day.

As I am very new to SQL and kusto and I am not here for someone to hand me the solution or do the work for me. I was hoping if there is someone who can help me out or guide me in the right direction to find a solution to my problem.

Thanks in advance !!!

Answer 1

Please check if the following approach gets you closer to what you need.

datatable(Resource:string, Event:string, EventTime:datetime)
[
    'Machine1', 'Start', datetime(2019-04-12 00:00),
    'Machine1', 'Stop', datetime(2019-04-12 01:00),
    'Machine1', 'Start', datetime(2019-04-12 01:30),
    'Machine1', 'Start', datetime(2019-04-12 01:45),
    'Machine1', 'Stop', datetime(2019-04-12 11:45),
    // Machine2
    'Machine2', 'Start', datetime(2019-04-12 00:00),
    'Machine2', 'Stop', datetime(2019-04-12 01:00),
    'Machine2', 'Stop', datetime(2019-04-12 01:20),
    'Machine2', 'Start', datetime(2019-04-12 01:30),
    'Machine2', 'Stop', datetime(2019-04-12 11:45),
]
| order by Resource asc, EventTime asc
| extend IsSameResource = (prev(Resource) == Resource)
| extend PrevState = iif(IsSameResource, prev(Event), Event), CurrentState = Event
| extend RunTime = iif(PrevState == 'Start' and CurrentState == 'Stop', EventTime - prev(EventTime), time(null)), 
         StartTime = prev(EventTime)
| where isnotnull(RunTime)
| project Resource, StartTime, EndTime = EventTime, RunTime

[Edit]

Same approach - but using columns provided in the question:

let AzureActivity = datatable(ResourceProvider:string, Resource:string, ActivityStatus:string, OperationName:string, EventSubmissionTimestamp:datetime)
[
"Microsoft.Compute", 'Machine1', "Succeeded", 'Start Virtual Machine', datetime(2019-04-12 00:00),
"Microsoft.Compute", 'Machine1',  "Succeeded", 'Deallocate Virtual Machine', datetime(2019-04-12 01:00),
"Microsoft.Compute", 'Machine1',  "Succeeded", 'Start Virtual Machine', datetime(2019-04-12 01:30),
"Microsoft.Compute", 'Machine1',  "Succeeded", 'Start Virtual Machine', datetime(2019-04-12 01:45),
"Microsoft.Compute", 'Machine1',  "Succeeded", 'Deallocate Virtual Machine', datetime(2019-04-12 11:45),
    // Machine2
"Microsoft.Compute", 'Machine2',  "Succeeded", 'Start Virtual Machine', datetime(2019-04-12 00:00),
"Microsoft.Compute", 'Machine2',  "Succeeded", 'Deallocate Virtual Machine', datetime(2019-04-12 01:00),
"Microsoft.Compute", 'Machine2',  "Succeeded", 'Deallocate Virtual Machine', datetime(2019-04-12 01:20),
"Microsoft.Compute", 'Machine2',  "Succeeded", 'Start Virtual Machine', datetime(2019-04-12 01:30),
"Microsoft.Compute", 'Machine2',  "Succeeded", 'Deallocate Virtual Machine', datetime(2019-04-12 11:45),
];
AzureActivity
| where ResourceProvider == "Microsoft.Compute"
| where OperationName in ('Deallocate Virtual Machine','Start Virtual Machine')
| where ActivityStatus == 'Succeeded'
| order by Resource asc, EventSubmissionTimestamp asc
| extend IsSameResource = (prev(Resource) == Resource)
| extend PrevState = iif(IsSameResource, prev(OperationName), OperationName), CurrentState = OperationName
| extend RunTime = iif(PrevState == 'Start Virtual Machine' and CurrentState == 'Deallocate Virtual Machine', EventSubmissionTimestamp - prev(EventSubmissionTimestamp), time(null)), 
         StartTime = prev(EventSubmissionTimestamp)
| where isnotnull(RunTime)
| project Resource, StartTime, EndTime = EventSubmissionTimestamp, RunTime