Calculating downtime using Window functions and Time Series functions in Azure Data Explorer

Published 04-28-2020 02:29 PM 1,570 Views
Microsoft

Customers interacting with Time Series, IoT Analytics, and Infra/App Logs often have a challenge identifying the total downtime of a device or an application. In this blog we will look at some of the common scenarios and see how we can use the power of Kusto Query Language to solve this challenge. Please note that these sample queries do not cover all the edge cases, please use them as a reference and adjust the logic for your scenario.

 

Scenario 1: Compute downtime based on health status

In this scenario, the devices are reporting health status as ‘Up’ or ‘Down’ or a binary value 1 or 0  or anything else. Our task is to identify how long the device was in a specific state. In this case we are interested in how long the device was down. Check the below sample query to identify the change in status and compute the start and end time of a given state.

 

Execute: [Web] [Desktop] https://help.kusto.windows.net/Samples

let DeviceHealth = datatable (TimeStamp:datetime, DeviceId:string, State:int)
[
'2020-04-24 10:40:00', '12987679', 0,
'2020-04-24 10:40:00', '21998045', 0,
'2020-04-24 11:00:00', '12987679', 1,
'2020-04-24 11:05:00', '12987679', 1,
'2020-04-24 11:49:00', '12987679', 0,
'2020-04-24 11:30:00', '21998045', 1,
'2020-04-24 12:30:00', '21998045', 0,
'2020-04-24 14:30:00', '21998045', 1,
'2020-04-24 12:45:00', '12987679', 0,
'2020-04-24 12:48:00', '12987679', 0,
'2020-04-24 13:00:00', '12987679', 1,
'2020-04-24 16:14:00', '21998045', 0,
'2020-04-24 16:30:00', '21998045', 1,
'2020-04-24 16:31:00', '21998045', 0
];
DeviceHealth
| where TimeStamp between (datetime('2020-04-23') .. datetime(2020-04-25))
| summarize TimeStampList = make_list(TimeStamp), StateList=make_list(State) by DeviceId
| mv-apply TimeStampList to typeof(datetime), StateList to typeof(string)
on
(
    order by TimeStampList asc
    | extend PrvTimeStamp=prev(TimeStampList)
    | extend PrvState=prev(StateList)
    | extend OutageStartTime= iff((PrvState == 1 or isempty(PrvState)) and StateList==0, TimeStampList, todatetime('')) 
    | extend OutageEndTime= iff(PrvState == 0 and StateList==1, TimeStampList,  todatetime('')) 
    | where isempty(OutageStartTime) <> isempty(OutageEndTime)
    | extend OutageStartTime = iif(isempty(OutageStartTime), prev(OutageStartTime),OutageStartTime)
    | extend OutageEndTime = iif(isempty(OutageEndTime ), next(OutageEndTime),OutageEndTime )
    | summarize by DeviceId, OutageStartTime, OutageEndTime, TotalDurationInMin = datetime_diff('minute',OutageEndTime, OutageStartTime)
)

Sample output:

Scenerio1Output.JPG

 

Explanation of the above sample query:

  1. Create an in-memory DeviceHealth table to get some sample data.
  2. Apply the time filter
  3. Create lists for TimeStamp and State column by DeviceID.
  4. Use mv-apply operator to expand each List in into a sub-table, apply a sub-query to each sub-table, and returns the union of the results of all sub-queries.
  5. In the sub queries, order the records by TimeStamp – this also materializes the records so that we can apply the window function prev() to get the previous row data to detect a state change.
  6. Finally, we eliminate the unwanted rows and return the summary of downtime start and end time by DeviceId.

 

Scenario 2: Compute downtime based on missing signals

In some cases, the devices don’t report specific health status, instead just stop sending signals. The below sample query identifies missing signals and compute the start and end time when the signal was missing.

 

Execute: [Web] [Desktop] https://help.kusto.windows.net/Samples

let DeviceTelemetry = datatable (TimeStamp:datetime, DeviceId:string, BatteryLevel:int, Temp:real, Humidity:real) 
[ 
'2020-04-23T23:11:51.903Z', '637086755205674255', 24, 64.8, 68, 
'2020-04-23T23:12:00.143Z', '637085868243706792', 34, 70.3, 61.8, 
'2020-04-23T23:13:07.308Z', '637086755205674255', 83, 61.3, 68.8, 
'2020-04-23T23:13:15.584Z', '637085868243706792', 69, 53.8, 64.6, 
'2020-04-23T23:14:22.714Z', '637086755205674255', 19, 67.9, 77.6, 
'2020-04-23T23:14:30.989Z', '637085868243706792', 6, 72.9, 67.7, 
'2020-04-23T23:15:37.516Z', '637086755205674255', 45, 53.5, 56.8, 
'2020-04-23T23:15:45.766Z', '637085868243706792', 66, 49.1, 72.3, 
'2020-04-23T23:16:52.951Z', '637086755205674255', 48, 70, 66.8, 
'2020-04-23T23:17:01.226Z', '637085868243706792', 50, 71.1, 70.3, 
'2020-04-23T23:59:52.951Z', '637086755205674255', 48, 70, 66.8, 
'2020-04-23T23:59:01.226Z', '637085868243706792', 50, 71.1, 70.3 
];
let StartDateTime = datetime('2020-04-23 20:00');
let EndDateTime = datetime('2020-04-24 05:00');
DeviceTelemetry
| make-series count() default=0 on TimeStamp from StartDateTime to EndDateTime step 1m by DeviceId
| mv-apply TimeStamp to typeof(datetime), count_ to typeof(int)
on 
(     
    order by TimeStamp asc
    | extend PrvTimeStamp=iif(isempty(prev(TimeStamp)), StartDateTime,prev(TimeStamp)) 
    | extend PrvCount=iif(isempty(prev(count_)), -1,prev(count_)) 
    | extend MissingDataStartTime= iff(PrvCount > 0 and count_==0, TimeStamp, iif(PrvCount==-1, TimeStamp,todatetime('')))
    | extend MissingDataEndTime= iff(PrvCount == 0 and count_>0, TimeStamp,  iif(isempty(next(TimeStamp)), TimeStamp,todatetime('')))
    | where isnotempty(MissingDataStartTime) or isnotempty(MissingDataEndTime)
    | extend MissingDataEndTime = iif(isempty(MissingDataEndTime), next(MissingDataEndTime),todatetime(''))
    | where PrvCount != 0
    | project DeviceId, MissingDataStartTime, MissingDataEndTime, TotalDurationInMin = datetime_diff('minute',MissingDataEndTime, MissingDataStartTime)
)

Sample output:

Scenerio2Output.JPG

 

Explanation of the above sample query:

  1. Create an in-memory DeviceHealth table to get some sample data.
  2. Apply the time filter
  3. Using make-series, create timeseries data set that returns a lists for TimeStamp and count of records aggregated at 1 min window by DeviceID.
  4. Use mv-apply operator to expand each List in into a sub-table, apply a sub-query to each sub-table, and returns the union of the results of all sub-queries.
  5. In the sub queries, order the records by TimeStamp – this also materializes the records so that we can apply the window function prev() and next() to get the previous/next row data to detect a state change.
  6. Finally, we eliminate the unwanted rows and columns and return the final dataset.

 

Scenario 3: Compute downtime based on threshold

Building on the scenario #2, we want to detect all the time slots where the device did not report any status in a 30 min window.

 

Execute: [Web] [Desktop] https://help.kusto.windows.net/Samples

let DeviceHealth = datatable (TimeStamp:datetime, DeviceId:string)
[
'2020-04-24 10:40:00', '12987679',
'2020-04-24 10:40:00', '21998045',
'2020-04-24 11:00:00', '12987679',
'2020-04-24 11:05:00', '12987679',
'2020-04-24 11:49:00', '12987679',
'2020-04-24 11:30:00', '21998045',
'2020-04-24 12:30:00', '21998045',
'2020-04-24 14:30:00', '21998045',
'2020-04-24 12:45:00', '12987679',
'2020-04-24 12:48:00', '12987679',
'2020-04-24 13:00:00', '12987679',
'2020-04-24 12:44:00', '21998045',
'2020-04-24 16:14:00', '21998045',
'2020-04-24 16:30:00', '21998045',
];
let outage_threshold=30m;
DeviceHealth
| order by DeviceId asc, TimeStamp asc
| extend dt=iff(DeviceId != prev(DeviceId), 0s, TimeStamp-prev(TimeStamp))
| extend State=iff(DeviceId != prev(DeviceId), 1, iff(dt >= outage_threshold, 0, 1))
| extend PrvTimeStamp=prev(TimeStamp)
| extend PrvState=prev(State)
| extend OutageStartTime= iff(State == 0, PrvTimeStamp, todatetime(''))
| extend OutageEndTime= iff(State==0, TimeStamp,  todatetime('')) 
| where isnotempty(OutageEndTime) and isnotempty(OutageEndTime)
| project DeviceId, OutageStartTime, OutageEndTime, TotalDurationInMin = datetime_diff('minute',OutageEndTime, OutageStartTime)

Sample output:

Scenerio3Output.JPG

 

Explanation of the above sample query:

  1. Create an in-memory DeviceHealth table to get some sample data.
  2. Apply the time filter.
  3. Order the records by Device ID and TimeStamp – this also materializes the records so that we can apply the window function prev() and next() to get the previous/next row data to detect a state change.
  4. Using the Window functions, we are identifying the records with a stage change i.e. records having gap of over 30 min (Outage Threshold).
  5. Identify the start time and end time of the outage.
  6. Finally, we eliminate the unwanted rows and columns and return the final dataset.

 

Scenario 4: Calculate uptime for each device for a given period

Finally, let’s build on Scenario #1 and calculate the total availability of the device. 

 

Execute: [Web] [Desktop] https://help.kusto.windows.net/Samples

let StartTime = datetime('2020-04-23');
let EndTime = datetime('2020-04-25');
let DeviceHealth = datatable (TimeStamp:datetime, DeviceId:string, State:int)
[
'2020-04-24 10:40:00', '12987679', 0,
'2020-04-24 10:40:00', '21998045', 0,
'2020-04-24 11:00:00', '12987679', 1,
'2020-04-24 11:05:00', '12987679', 1,
'2020-04-24 11:49:00', '12987679', 0,
'2020-04-24 11:30:00', '21998045', 1,
'2020-04-24 12:30:00', '21998045', 0,
'2020-04-24 14:30:00', '21998045', 1,
'2020-04-24 12:45:00', '12987679', 0,
'2020-04-24 12:48:00', '12987679', 0,
'2020-04-24 13:00:00', '12987679', 1,
'2020-04-24 16:14:00', '21998045', 0,
'2020-04-24 16:30:00', '21998045', 1,
];
DeviceHealth
| where TimeStamp between (StartTime .. EndTime)
| summarize TimeStampList = make_list(TimeStamp), StateList=make_list(State) by DeviceId
| mv-apply TimeStampList to typeof(datetime), StateList to typeof(string)
on
(
    order by TimeStampList asc
    | extend PrvTimeStamp=prev(TimeStampList)
    | extend PrvState=prev(StateList)
    | extend OutageStartTime= iff((PrvState == 1 or isempty(PrvState)) and StateList==0, TimeStampList, todatetime('')) 
    | extend OutageEndTime= iff(PrvState == 0 and StateList==1, TimeStampList,  todatetime('')) 
    | where isempty(OutageStartTime) <> isempty(OutageEndTime)
    | extend OutageStartTime = iif(isempty(OutageStartTime), prev(OutageStartTime),OutageStartTime)
    | where isnotempty(OutageEndTime)
    | project DeviceId, OutageStartTime, OutageEndTime, TotalDurationInMin = datetime_diff('minute',OutageEndTime, OutageStartTime)
)
| extend TotalMin = toreal(datetime_diff('minute', EndTime, StartTime))
| summarize Availability=((max(TotalMin)-sum(TotalDurationInMin))/max(TotalMin))*100  by DeviceId

Sample output:

Scenerio4Output.JPG  

Explanation of the above sample query:

  1. All steps same as scenario #1, except add two additional lines of code at the bottom to do the availability calculation.

 

Next steps

  1. Refer Azure Data Explorer documentation for additional reference.
  2. These queries can be further optimized for large data sets by using query hint called shufflekey, this will share the load on all cluster nodes where each node will process one partition of the data. You can also look into defining a data partitioning policy on your table. Queries in which the shufflekey is also the table's hash partition key are expected to perform better, as the amount of data required to move across cluster nodes is significantly reduced.

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-1345430%22%20slang%3D%22en-US%22%3ECalculating%20downtime%20using%20Window%20functions%20and%20Time%20Series%20functions%20in%20Azure%20Data%20Explorer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1345430%22%20slang%3D%22en-US%22%3E%3CP%3ECustomers%20interacting%20with%20Time%20Series%2C%20IoT%20Analytics%2C%20and%20Infra%2FApp%20Logs%20often%20have%20a%20challenge%20identifying%20the%20total%20downtime%20of%20a%20device%20or%20an%20application.%20In%20this%20blog%20we%20will%20look%20at%20some%20of%20the%20common%20scenarios%20and%20see%20how%20we%20can%20use%20the%20power%20of%20Kusto%20Query%20Language%20to%20solve%20this%20challenge.%20Please%20note%20that%20these%20sample%20queries%20do%20not%20cover%20all%20the%20edge%20cases%2C%20please%20use%20them%20as%20a%20reference%20and%20adjust%20the%20logic%20for%20your%20scenario.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3EScenario%201%3A%20Compute%20downtime%20based%20on%20health%20status%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EIn%20this%20scenario%2C%20the%20devices%20are%20reporting%20health%20status%20as%20%E2%80%98Up%E2%80%99%20or%20%E2%80%98Down%E2%80%99%20or%20a%20binary%20value%201%20or%200%26nbsp%3B%20or%20anything%20else.%20Our%20task%20is%20to%20identify%20how%20long%20the%20device%20was%20in%20a%20specific%20state.%20In%20this%20case%20we%20are%20interested%20in%20how%20long%20the%20device%20was%20down.%20Check%20the%20below%20sample%20query%20to%20identify%20the%20change%20in%20status%20and%20compute%20the%20start%20and%20end%20time%20of%20a%20given%20state.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecute%3A%20%5B%3CA%20href%3D%22https%3A%2F%2Fdataexplorer.azure.com%2Fclusters%2Fhelp.kusto.windows.net%2Fdatabases%2FSamples%3Fquery%3DH4sIAAAAAAAEAI1VXW%252bbQBB8R%252bI%252f7BsgnaMDY8fQ0qdUaqRWrdS%252bVVV0DuvkVDgQnJ26yo%252fvYsyHATfYfsC3szOzyyAS1HCHB%252fmIn1Ak%252bhkiiIWm7zZBsH%252fIFL9rkeYhHaKmf%252byMvo%252fDUhdSPTEggMZQKu2Yxk%252fTsDzu8QX3F54PLg99HnJuMbBcL9jcrm8DuubsOsxzg2DD%252fdUUzCXMmM2dgK1mwfxghjc3XE54G7F5k7ARmz%252bXzZ8YYcRGsM0M2HLe3tYh2Xt7hPW8EQjmTrCZxq93ptGPnGm8wsszFght3GCL%252bgVRgd3kzu64l5YDNzfQVtrCynEqrnKfpqKQf3t8n2WpKdmp%252bI0PCV13wXbO%252ba0QUVc%252fnTmwPbZxr5jTw0LkeXIcEOsM9DHHbNe67bP2yvUTQyYzZRq2aQB9siLGohK65BTlY11%252fBfyjUcXwrTi0kCgv8GBfdDhj%252bMlCDW3dDGFf91o8VSyFrugikLudbTfNEEXgkkWQJaa5PrYFxwFB3d3qIs4uJ2A0d3fzLGqYVP6o4k63L8uHAu5I4KpCnabG82BEB95%252fGNTOJt7YDQVIyp19jZbBadOj4%252bHB%252f9YwKdHUSEFR08AzG4Aa%252bu4p6IWYDWdil%252fq04kyL5G5fCC0zda%252b%252bSFW%252fEU5bfoiru2SlUu01WmzQOh6Ufv8AbpDQ0WAGAAA%253d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWeb%3C%2FA%3E%5D%20%5B%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3Fquery%3DH4sIAAAAAAAEAI1VXW%252bbQBB8R%252bI%252f7BsgnaMDY8fQ0qdUaqRWrdS%252bVVV0DuvkVDgQnJ26yo%252fvYsyHATfYfsC3szOzyyAS1HCHB%252fmIn1Ak%252bhkiiIWm7zZBsH%252fIFL9rkeYhHaKmf%252byMvo%252fDUhdSPTEggMZQKu2Yxk%252fTsDzu8QX3F54PLg99HnJuMbBcL9jcrm8DuubsOsxzg2DD%252fdUUzCXMmM2dgK1mwfxghjc3XE54G7F5k7ARmz%252bXzZ8YYcRGsM0M2HLe3tYh2Xt7hPW8EQjmTrCZxq93ptGPnGm8wsszFght3GCL%252bgVRgd3kzu64l5YDNzfQVtrCynEqrnKfpqKQf3t8n2WpKdmp%252bI0PCV13wXbO%252ba0QUVc%252fnTmwPbZxr5jTw0LkeXIcEOsM9DHHbNe67bP2yvUTQyYzZRq2aQB9siLGohK65BTlY11%252fBfyjUcXwrTi0kCgv8GBfdDhj%252bMlCDW3dDGFf91o8VSyFrugikLudbTfNEEXgkkWQJaa5PrYFxwFB3d3qIs4uJ2A0d3fzLGqYVP6o4k63L8uHAu5I4KpCnabG82BEB95%252fGNTOJt7YDQVIyp19jZbBadOj4%252bHB%252f9YwKdHUSEFR08AzG4Aa%252bu4p6IWYDWdil%252fq04kyL5G5fCC0zda%252b%252bSFW%252fEU5bfoiru2SlUu01WmzQOh6Ufv8AbpDQ0WAGAAA%253d%26amp%3Bweb%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDesktop%3C%2FA%3E%5D%20%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatatable%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%3Adatetime%2C%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%3Astring%2C%20%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E%3Aint)%0A%5B%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A05%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A49%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2014%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A45%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A48%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2013%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A14%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A31%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%0A%5D%3B%0A%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Ebetween%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E('2020-04-23')%20..%20%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E(2020-04-25))%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Emake_list%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E)%2C%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Emake_list%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Emv-apply%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(datetime)%2C%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(string)%0A%3CSPAN%20class%3D%22csl-command%22%3Eon%3C%2FSPAN%3E%0A(%0A%20%20%20%20%3CSPAN%20class%3D%22csl-operator%22%3Eorder%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Easc%3C%2FSPAN%3E%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvTimeStamp%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E((%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%20%3D%3D%201%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eor%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E))%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3D0%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%20%3D%3D%200%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3D1%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%2C%20%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%20%26lt%3B%26gt%3B%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%2C%20%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%20)%2C%20%3CSPAN%20class%3D%22csl-function%22%3Enext%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%20)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETotalDurationInMin%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Edatetime_diff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E'minute'%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%0A)%3C%2FPRE%3E%0A%3CP%3ESample%20output%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Scenerio1Output.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187337i181691F06217B8F5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Scenerio1Output.JPG%22%20alt%3D%22Scenerio1Output.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExplanation%20of%20the%20above%20sample%20query%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20an%20in-memory%20DeviceHealth%20table%20to%20get%20some%20sample%20data.%3C%2FLI%3E%0A%3CLI%3EApply%20the%20time%20filter%3C%2FLI%3E%0A%3CLI%3ECreate%20lists%20for%20TimeStamp%20and%20State%20column%20by%20DeviceID.%3C%2FLI%3E%0A%3CLI%3EUse%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmv-applyoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Emv-apply%3C%2FA%3E%20operator%20to%20expand%20each%20List%20in%20into%20a%20sub-table%2C%20apply%20a%20sub-query%20to%20each%20sub-table%2C%20and%20returns%20the%20union%20of%20the%20results%20of%20all%20sub-queries.%3C%2FLI%3E%0A%3CLI%3EIn%20the%20sub%20queries%2C%20order%20the%20records%20by%20TimeStamp%20%E2%80%93%20this%20also%20materializes%20the%20records%20so%20that%20we%20can%20apply%20the%20window%20function%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fprevfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eprev()%3C%2FA%3E%20to%20get%20the%20previous%20row%20data%20to%20detect%20a%20state%20change.%3C%2FLI%3E%0A%3CLI%3EFinally%2C%20we%20eliminate%20the%20unwanted%20rows%20and%20return%20the%20summary%20of%20downtime%20start%20and%20end%20time%20by%20DeviceId.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3EScenario%202%3A%20Compute%20downtime%20based%20on%20missing%20signals%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EIn%20some%20cases%2C%20the%20devices%20don%E2%80%99t%20report%20specific%20health%20status%2C%20instead%20just%20stop%20sending%20signals.%20The%20below%20sample%20query%20identifies%20missing%20signals%20and%20compute%20the%20start%20and%20end%20time%20when%20the%20signal%20was%20missing.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecute%3A%20%5B%3CA%20href%3D%22https%3A%2F%2Fdataexplorer.azure.com%2Fclusters%2Fhelp.kusto.windows.net%2Fdatabases%2FSamples%3Fquery%3DH4sIAAAAAAAEAKWUXWvbMBSG7wP5D2dXiUERkmxZjod7sXWwwQqD5WpjFLdWNm3%252bCLaSLdAfv2O7SZ1UThlLYhHr%252bLzvoyMf5drCtd6Ze73SuS60rfeQQJZa%252fN7lGuYrU%252bjPNi02MU5qi3fkMeFDFje2NuV3Am9Sa3W9%252f6h3Oo9NaQmsNGbUOs0JvN8WJjN23916MJ18xWsmmGALFiyEvxJ%252bzHksOV0y%252f8uMwCz0FYtCJaVgMlSBkBJnRUAgDGiEI17PFUTMGOXBQEFGYSQC%252fBeqpcBZHxUUoz4qcOrU8GOmqM%252bicYqoz27HaEyDSyqjYJwjXBKQfreSgIYujSAWgioejHNw1AgVxVGpMQ2f0WW0vMCByYL2QsolIWNfUcnDcYxAdktpx9BdDhkHkqowvICBHMGS8o7Gd2mEsUROyS9wRO3WtlpuChUzToW4QCExW%252fGOgjkp5PJ%252fKVDh3yhQ4tvr6STHHsUWrO01dmDbj32Hds04H3iAYNgDM%252b8x5V2ZXU4IgMlDwtkZMJ08QJH%252b0otG10Y3cF9tSzv3INPrdJvbhEFVwvFogHVdFWeItjoBaKzeAC%252fgbn88PTqP3SLdbPL9QAsT7X6jq%252fX8gOyR3v92EMNDxptOEGI6mUP7mU7asaozXbcmT3ppc9%252fHHkD%252fsbrM4FO9O4YTY1CswdPK7uebWu%252beDjwPfU%252fWRM7j8Fz4bQv6XLTnbxUXnJzMnGvcmKbBMxU90868tUvArNfzgzpcAYMUH%252b0lkgTfmSMUgdb7CJKg3SBoq6fXYOZ53rg37p3DOUlOrK9OnWG47BI1T2v5MsbvH7rWYJqysr2IqxgebvLIM4%252fQLy8L%252b2HI6pAg0C3AFTmjP4U%252fluoVluoQ2tTVT31vj28%252bce4ycYBi1Sqb5tfbOrWmKj%252bUN6YcNPNt1m7PrDDl1uoZceU7Szid4O8vSQ1AS%252fsHAAA%253d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWeb%3C%2FA%3E%5D%20%5B%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3Fquery%3DH4sIAAAAAAAEAKWUXWvbMBSG7wP5D2dXiUERkmxZjod7sXWwwQqD5WpjFLdWNm3%252bCLaSLdAfv2O7SZ1UThlLYhHr%252bLzvoyMf5drCtd6Ze73SuS60rfeQQJZa%252fN7lGuYrU%252bjPNi02MU5qi3fkMeFDFje2NuV3Am9Sa3W9%252f6h3Oo9NaQmsNGbUOs0JvN8WJjN23916MJ18xWsmmGALFiyEvxJ%252bzHksOV0y%252f8uMwCz0FYtCJaVgMlSBkBJnRUAgDGiEI17PFUTMGOXBQEFGYSQC%252fBeqpcBZHxUUoz4qcOrU8GOmqM%252bicYqoz27HaEyDSyqjYJwjXBKQfreSgIYujSAWgioejHNw1AgVxVGpMQ2f0WW0vMCByYL2QsolIWNfUcnDcYxAdktpx9BdDhkHkqowvICBHMGS8o7Gd2mEsUROyS9wRO3WtlpuChUzToW4QCExW%252fGOgjkp5PJ%252fKVDh3yhQ4tvr6STHHsUWrO01dmDbj32Hds04H3iAYNgDM%252b8x5V2ZXU4IgMlDwtkZMJ08QJH%252b0otG10Y3cF9tSzv3INPrdJvbhEFVwvFogHVdFWeItjoBaKzeAC%252fgbn88PTqP3SLdbPL9QAsT7X6jq%252fX8gOyR3v92EMNDxptOEGI6mUP7mU7asaozXbcmT3ppc9%252fHHkD%252fsbrM4FO9O4YTY1CswdPK7uebWu%252beDjwPfU%252fWRM7j8Fz4bQv6XLTnbxUXnJzMnGvcmKbBMxU90868tUvArNfzgzpcAYMUH%252b0lkgTfmSMUgdb7CJKg3SBoq6fXYOZ53rg37p3DOUlOrK9OnWG47BI1T2v5MsbvH7rWYJqysr2IqxgebvLIM4%252fQLy8L%252b2HI6pAg0C3AFTmjP4U%252fluoVluoQ2tTVT31vj28%252bce4ycYBi1Sqb5tfbOrWmKj%252bUN6YcNPNt1m7PrDDl1uoZceU7Szid4O8vSQ1AS%252fsHAAA%253d%26amp%3Bweb%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDesktop%3C%2FA%3E%5D%20%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceTelemetry%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatatable%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%3Adatetime%2C%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%3Astring%2C%20%3CSPAN%20class%3D%22csl-column%22%3EBatteryLevel%3C%2FSPAN%3E%3Aint%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETemp%3C%2FSPAN%3E%3Areal%2C%20%3CSPAN%20class%3D%22csl-column%22%3EHumidity%3C%2FSPAN%3E%3Areal)%20%0A%5B%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A11%3A51.903Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2024%2C%2064.8%2C%2068%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A12%3A00.143Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%2034%2C%2070.3%2C%2061.8%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A13%3A07.308Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2083%2C%2061.3%2C%2068.8%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A13%3A15.584Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%2069%2C%2053.8%2C%2064.6%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A14%3A22.714Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2019%2C%2067.9%2C%2077.6%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A14%3A30.989Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%206%2C%2072.9%2C%2067.7%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A15%3A37.516Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2045%2C%2053.5%2C%2056.8%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A15%3A45.766Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%2066%2C%2049.1%2C%2072.3%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A16%3A52.951Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2048%2C%2070%2C%2066.8%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A17%3A01.226Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%2050%2C%2071.1%2C%2070.3%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A59%3A52.951Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637086755205674255'%3C%2FSPAN%3E%2C%2048%2C%2070%2C%2066.8%2C%20%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-23T23%3A59%3A01.226Z'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'637085868243706792'%3C%2FSPAN%3E%2C%2050%2C%2071.1%2C%2070.3%20%0A%5D%3B%0A%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EStartDateTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E('2020-04-23%2020%3A00')%3B%0A%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EEndDateTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E('2020-04-24%2005%3A00')%3B%0A%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceTelemetry%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Emake-series%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Ecount%3C%2FSPAN%3E()%20%3CSPAN%20class%3D%22csl-command%22%3Edefault%3C%2FSPAN%3E%3D0%20%3CSPAN%20class%3D%22csl-command%22%3Eon%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Efrom%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EStartDateTime%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EEndDateTime%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Estep%3C%2FSPAN%3E%201m%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Emv-apply%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(datetime)%2C%20%3CSPAN%20class%3D%22csl-column%22%3Ecount_%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(int)%0A%3CSPAN%20class%3D%22csl-command%22%3Eon%3C%2FSPAN%3E%20%0A(%20%20%20%20%20%0A%20%20%20%20%3CSPAN%20class%3D%22csl-operator%22%3Eorder%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Easc%3C%2FSPAN%3E%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvTimeStamp%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E))%2C%20%3CSPAN%20class%3D%22csl-let-variable%22%3EStartDateTime%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvCount%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3Ecount_%3C%2FSPAN%3E))%2C%20-1%2C%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3Ecount_%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataStartTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvCount%3C%2FSPAN%3E%20%26gt%3B%200%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3Ecount_%3C%2FSPAN%3E%3D%3D0%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvCount%3C%2FSPAN%3E%3D%3D-1%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E)))%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvCount%3C%2FSPAN%3E%20%3D%3D%200%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3Ecount_%3C%2FSPAN%3E%26gt%3B0%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%2C%20%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Enext%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E))%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E)))%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisnotempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EMissingDataStartTime%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eor%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisnotempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E)%2C%20%3CSPAN%20class%3D%22csl-function%22%3Enext%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E)%2C%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvCount%3C%2FSPAN%3E%20!%3D%200%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eproject%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataStartTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETotalDurationInMin%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Edatetime_diff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E'minute'%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-column%22%3EMissingDataEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EMissingDataStartTime%3C%2FSPAN%3E)%0A)%3C%2FPRE%3E%0A%3CP%3ESample%20output%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Scenerio2Output.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187338i94BCE550912C14CD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Scenerio2Output.JPG%22%20alt%3D%22Scenerio2Output.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExplanation%20of%20the%20above%20sample%20query%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20an%20in-memory%20DeviceHealth%20table%20to%20get%20some%20sample%20data.%3C%2FLI%3E%0A%3CLI%3EApply%20the%20time%20filter%3C%2FLI%3E%0A%3CLI%3EUsing%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmake-seriesoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Emake-series%3C%2FA%3E%2C%20create%20timeseries%20data%20set%20that%20returns%20a%20lists%20for%20TimeStamp%20and%20count%20of%20records%20aggregated%20at%201%20min%20window%20by%20DeviceID.%3C%2FLI%3E%0A%3CLI%3EUse%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmv-applyoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Emv-apply%3C%2FA%3E%20operator%20to%20expand%20each%20List%20in%20into%20a%20sub-table%2C%20apply%20a%20sub-query%20to%20each%20sub-table%2C%20and%20returns%20the%20union%20of%20the%20results%20of%20all%20sub-queries.%3C%2FLI%3E%0A%3CLI%3EIn%20the%20sub%20queries%2C%20order%20the%20records%20by%20TimeStamp%20%E2%80%93%20this%20also%20materializes%20the%20records%20so%20that%20we%20can%20apply%20the%20window%20function%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fprevfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eprev()%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fnextfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Enext()%3C%2FA%3E%20to%20get%20the%20previous%2Fnext%20row%20data%20to%20detect%20a%20state%20change.%3C%2FLI%3E%0A%3CLI%3EFinally%2C%20we%20eliminate%20the%20unwanted%20rows%20and%20columns%20and%20return%20the%20final%20dataset.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3EScenario%203%3A%20Compute%20downtime%20based%20on%20threshold%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EBuilding%20on%20the%20scenario%20%232%2C%20we%20want%20to%20detect%20all%20the%20time%20slots%20where%20the%20device%20did%20not%20report%20any%20status%20in%20a%2030%20min%20window.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecute%3A%20%5B%3CA%20href%3D%22https%3A%2F%2Fdataexplorer.azure.com%2Fclusters%2Fhelp.kusto.windows.net%2Fdatabases%2FSamples%3Fquery%3DH4sIAAAAAAAEAI2UTWuDQBCG74H8h%252blJhQ2sxnxo2Z5SaA6lhfZWSjDZSWLxI6yTtIH%252b%252bO6aVoxpULy4M8%252b%252b77s6miDBDA%252fxCh8wSmgLAmRE%252blomCPZrnOILReku1EUkvWK%252f9FyGBak42zj93lu%252fZ3nc4wPuDzwfXB76POTcYmC5XjCdjCeBxa4znhsEU%252b6PLhhXA206mhm1M37Qzgxb83gdGL%252bTjt%252baWTPTNmbY4floHb8tzzh0OzD%252fnuv9tt9L9Ajle4o2uKCtwmKbJ1IMeapb9cnq974hVxIVLI%252fVEEFUrBhUY2aWhsMvwkyCJBGv13YF3wjYKTxUBYcBL2rbB2W3WjpOTUtXCFvlXAYGkQR34uJQ2k0TZ6rP6lDZiYb7OXbyL5Hytt5%252bKo10WZHZLsoMJQVCGNO6DQPK%252f75G27KcS6H7TDZkhOC1x8SgKQFG43OLCiEuspww3dHRPlNzINIGV7tGYKfyD1xR9XJZ82DsPKCOlFOUzPYqojjP5tljnJ3%252bP2WyhTT5rTTO9oQWa2xtSDs%252fTfliBskEAAA%253d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWeb%3C%2FA%3E%5D%20%5B%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3Fquery%3DH4sIAAAAAAAEAI2UTWuDQBCG74H8h%252blJhQ2sxnxo2Z5SaA6lhfZWSjDZSWLxI6yTtIH%252b%252bO6aVoxpULy4M8%252b%252b77s6miDBDA%252fxCh8wSmgLAmRE%252blomCPZrnOILReku1EUkvWK%252f9FyGBak42zj93lu%252fZ3nc4wPuDzwfXB76POTcYmC5XjCdjCeBxa4znhsEU%252b6PLhhXA206mhm1M37Qzgxb83gdGL%252bTjt%252baWTPTNmbY4floHb8tzzh0OzD%252fnuv9tt9L9Ajle4o2uKCtwmKbJ1IMeapb9cnq974hVxIVLI%252fVEEFUrBhUY2aWhsMvwkyCJBGv13YF3wjYKTxUBYcBL2rbB2W3WjpOTUtXCFvlXAYGkQR34uJQ2k0TZ6rP6lDZiYb7OXbyL5Hytt5%252bKo10WZHZLsoMJQVCGNO6DQPK%252f75G27KcS6H7TDZkhOC1x8SgKQFG43OLCiEuspww3dHRPlNzINIGV7tGYKfyD1xR9XJZ82DsPKCOlFOUzPYqojjP5tljnJ3%252bP2WyhTT5rTTO9oQWa2xtSDs%252fTfliBskEAAA%253d%26amp%3Bweb%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDesktop%3C%2FA%3E%5D%20%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatatable%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%3Adatetime%2C%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%3Astring)%0A%5B%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A05%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A49%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2014%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A45%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A48%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2013%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A44%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A14%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%0A%5D%3B%0A%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3Eoutage_threshold%3C%2FSPAN%3E%3D30m%3B%0A%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eorder%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Easc%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Easc%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3Edt%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%20!%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E)%2C%200s%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E-%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E))%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%20!%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E)%2C%201%2C%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3Edt%3C%2FSPAN%3E%20%26gt%3B%3D%20%3CSPAN%20class%3D%22csl-let-variable%22%3Eoutage_threshold%3C%2FSPAN%3E%2C%200%2C%201))%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvTimeStamp%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E)%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E)%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E%20%3D%3D%200%2C%20%3CSPAN%20class%3D%22csl-column%22%3EPrvTimeStamp%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E%3D%3D0%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%2C%20%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%20%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisnotempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisnotempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eproject%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETotalDurationInMin%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Edatetime_diff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E'minute'%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%3C%2FPRE%3E%0A%3CP%3ESample%20output%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Scenerio3Output.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187346i748CCECEBB65072B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Scenerio3Output.JPG%22%20alt%3D%22Scenerio3Output.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExplanation%20of%20the%20above%20sample%20query%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20an%20in-memory%20DeviceHealth%20table%20to%20get%20some%20sample%20data.%3C%2FLI%3E%0A%3CLI%3EApply%20the%20time%20filter.%3C%2FLI%3E%0A%3CLI%3EOrder%20the%20records%20by%20Device%20ID%20and%20TimeStamp%20%E2%80%93%20this%20also%20materializes%20the%20records%20so%20that%20we%20can%20apply%20the%20window%20function%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fprevfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eprev()%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fnextfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Enext()%3C%2FA%3E%20to%20get%20the%20previous%2Fnext%20row%20data%20to%20detect%20a%20state%20change.%3C%2FLI%3E%0A%3CLI%3EUsing%20the%20Window%20functions%2C%20we%20are%20identifying%20the%20records%20with%20a%20stage%20change%20i.e.%20records%20having%20gap%20of%20over%2030%20min%20(Outage%20Threshold).%3C%2FLI%3E%0A%3CLI%3EIdentify%20the%20start%20time%20and%20end%20time%20of%20the%20outage.%3C%2FLI%3E%0A%3CLI%3EFinally%2C%20we%20eliminate%20the%20unwanted%20rows%20and%20columns%20and%20return%20the%20final%20dataset.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CH1%20id%3D%22toc-hId-441591159%22%20id%3D%22toc-hId-441590253%22%3E%26nbsp%3B%3C%2FH1%3E%0A%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%225%22%3EScenario%204%3A%20Calculate%20uptime%20for%20each%20device%20for%20a%20given%20period%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EFinally%2C%20let%E2%80%99s%20build%20on%20Scenario%20%231%20and%20calculate%20the%20total%20availability%20of%20the%20device.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecute%3A%20%5B%3CA%20href%3D%22https%3A%2F%2Fdataexplorer.azure.com%2Fclusters%2Fhelp.kusto.windows.net%2Fdatabases%2FSamples%3Fquery%3DH4sIAAAAAAAEAI1U22rbQBB9F%252bgf5k27RU5XsuzYalUopNBASwvtWylhbY2TbXVDHjtxyMd3ZVl3ObX0suycOXNmdEYREvwgmdNPFSMEEEpC0kdmucIVE%252bFN3KnF35lGpIGfkvA8bFbDbnCv1vgZZUQPJVa%252fqwiBFdm6Wpz5FYF9Qt%252bG%252fpZyldzbhRxCXyXETeOXaTQVPHCE7wlfCMsGy3GXi%252bv59VKfhX0e5jrL5UJ4szGYozFDNmcENrsI5i0v0Ob40xFtAzZ3FDZg8y5l80ZaGLBp2OIC2PSyuc19Le%252f%252fLczPtvBbG6ptJtN4gccHzBFqI8EK6RExAda4%252bOqqciovMra7OJa5em5lfVFb0s6M5V%252b8i%252fS5MSY%252f%252ba9ABE38eMdhdajtWjDH%252b4nMsujQI6YU6JBhumGVy9usrXDpeC0yTUyDmQboJ81DzItCXU65XZfxF8AnwiSE7%252fm%252bhgRZjnvWyeBD%252bFFCCa3V9GHfdiTvsZ5lAGqzYaxKhiAAR0sEtcU4o0Md4Bykzm5GFwi724Gt%252b27%252bGpZOGK18%252bnBl3XZZ0S%252fgDAqcrVB6ptLca5HD%252bw%252b9WOOe12ajDaTUhp2jteE46cF1%252f6IvMknpNS1Znv7BNdU%252btPuy7O4k9ZRSktHNLpek0uQ2%252baqS1g%252f8LiwGbcUq2RFadi91qPW4Uad5HIlLOkpzvaHsDCvUhA1VbzU%252f7qWK5EpFig4BY7F8YhU9n2gYG3bB%252bdsOjL9xhIDujv4DbwXFZt8GAAA%253d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWeb%3C%2FA%3E%5D%20%5B%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3Fquery%3DH4sIAAAAAAAEAI1U22rbQBB9F%252bgf5k27RU5XsuzYalUopNBASwvtWylhbY2TbXVDHjtxyMd3ZVl3ObX0suycOXNmdEYREvwgmdNPFSMEEEpC0kdmucIVE%252bFN3KnF35lGpIGfkvA8bFbDbnCv1vgZZUQPJVa%252fqwiBFdm6Wpz5FYF9Qt%252bG%252fpZyldzbhRxCXyXETeOXaTQVPHCE7wlfCMsGy3GXi%252bv59VKfhX0e5jrL5UJ4szGYozFDNmcENrsI5i0v0Ob40xFtAzZ3FDZg8y5l80ZaGLBp2OIC2PSyuc19Le%252f%252fLczPtvBbG6ptJtN4gccHzBFqI8EK6RExAda4%252bOqqciovMra7OJa5em5lfVFb0s6M5V%252b8i%252fS5MSY%252f%252ba9ABE38eMdhdajtWjDH%252b4nMsujQI6YU6JBhumGVy9usrXDpeC0yTUyDmQboJ81DzItCXU65XZfxF8AnwiSE7%252fm%252bhgRZjnvWyeBD%252bFFCCa3V9GHfdiTvsZ5lAGqzYaxKhiAAR0sEtcU4o0Md4Bykzm5GFwi724Gt%252b27%252bGpZOGK18%252bnBl3XZZ0S%252fgDAqcrVB6ptLca5HD%252bw%252b9WOOe12ajDaTUhp2jteE46cF1%252f6IvMknpNS1Znv7BNdU%252btPuy7O4k9ZRSktHNLpek0uQ2%252baqS1g%252f8LiwGbcUq2RFadi91qPW4Uad5HIlLOkpzvaHsDCvUhA1VbzU%252f7qWK5EpFig4BY7F8YhU9n2gYG3bB%252bdsOjL9xhIDujv4DbwXFZt8GAAA%253d%26amp%3Bweb%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDesktop%3C%2FA%3E%5D%20%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%2FSamples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fhelp.kusto.windows.net%2FSamples%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EStartTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E('2020-04-23')%3B%0A%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EEndTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatetime%3C%2FSPAN%3E('2020-04-25')%3B%0A%3CSPAN%20class%3D%22csl-command%22%3Elet%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edatatable%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%3Adatetime%2C%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%3Astring%2C%20%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E%3Aint)%0A%5B%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2010%3A40%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A05%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A49%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2011%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2014%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A45%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2012%3A48%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2013%3A00%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'12987679'%3C%2FSPAN%3E%2C%201%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A14%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%200%2C%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'2020-04-24%2016%3A30%3A00'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-string-literal%22%3E'21998045'%3C%2FSPAN%3E%2C%201%2C%0A%5D%3B%0A%3CSPAN%20class%3D%22csl-let-variable%22%3EDeviceHealth%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Ebetween%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-let-variable%22%3EStartTime%3C%2FSPAN%3E%20..%20%3CSPAN%20class%3D%22csl-let-variable%22%3EEndTime%3C%2FSPAN%3E)%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Emake_list%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStamp%3C%2FSPAN%3E)%2C%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Emake_list%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EState%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Emv-apply%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(datetime)%2C%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etypeof%3C%2FSPAN%3E(string)%0A%3CSPAN%20class%3D%22csl-command%22%3Eon%3C%2FSPAN%3E%0A(%0A%20%20%20%20%3CSPAN%20class%3D%22csl-operator%22%3Eorder%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Easc%3C%2FSPAN%3E%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvTimeStamp%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E((%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%20%3D%3D%201%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eor%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E))%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3D0%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EPrvState%3C%2FSPAN%3E%20%3D%3D%200%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EStateList%3C%2FSPAN%3E%3D%3D1%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETimeStampList%3C%2FSPAN%3E%2C%20%20%3CSPAN%20class%3D%22csl-function%22%3Etodatetime%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E''%3C%2FSPAN%3E))%20%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%20%26lt%3B%26gt%3B%20%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Eiif%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Eisempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%2C%20%3CSPAN%20class%3D%22csl-function%22%3Eprev%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-function%22%3Eisnotempty%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E)%0A%20%20%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eproject%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3ETotalDurationInMin%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Edatetime_diff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E'minute'%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22csl-column%22%3EOutageEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EOutageStartTime%3C%2FSPAN%3E)%0A)%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3ETotalMin%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Etoreal%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-function%22%3Edatetime_diff%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-string-literal%22%3E'minute'%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-let-variable%22%3EEndTime%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-let-variable%22%3EStartTime%3C%2FSPAN%3E))%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EAvailability%3C%2FSPAN%3E%3D((%3CSPAN%20class%3D%22csl-function%22%3Emax%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETotalMin%3C%2FSPAN%3E)-%3CSPAN%20class%3D%22csl-function%22%3Esum%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETotalDurationInMin%3C%2FSPAN%3E))%2F%3CSPAN%20class%3D%22csl-function%22%3Emax%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3ETotalMin%3C%2FSPAN%3E))*100%20%20%3CSPAN%20class%3D%22csl-command%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EDeviceId%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ESample%20output%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Scenerio4Output.JPG%22%20style%3D%22width%3A%20353px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187345i855F9A55D504C4AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Scenerio4Output.JPG%22%20alt%3D%22Scenerio4Output.JPG%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExplanation%20of%20the%20above%20sample%20query%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EAll%20steps%20same%20as%20scenario%20%231%2C%20except%20add%20two%20additional%20lines%20of%20code%20at%20the%20bottom%20to%20do%20the%20availability%20calculation.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CFONT%20size%3D%225%22%3ENext%20steps%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ERefer%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Data%20Explorer%20documentation%3C%2FA%3E%20for%20additional%20reference.%3C%2FLI%3E%0A%3CLI%3EThese%20queries%20can%20be%20further%20optimized%20for%20large%20data%20sets%20by%20using%20query%20hint%20called%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fshufflequery%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eshufflekey%3C%2FA%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%20this%20will%20share%20the%20load%20on%20all%20cluster%20nodes%20where%20each%20node%20will%20process%20one%20partition%20of%20the%20data.%20You%20can%20also%20look%20into%20defining%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fmanagement%2Fpartitioningpolicy%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Edata%20partitioning%20policy%3C%2FA%3E%20on%20your%20table.%20Queries%20in%20which%20the%20shufflekey%20is%20also%20the%20table's%20hash%20partition%20key%20are%20expected%20to%20perform%20better%2C%20as%20the%20amount%20of%20data%20required%20to%20move%20across%20cluster%20nodes%20is%20significantly%20reduced.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1345430%22%20slang%3D%22en-US%22%3E%3CP%3ECustomers%20interacting%20with%20Time%20Series%2C%20IoT%20Analytics%2C%20and%20Infra%2FApp%20Logs%20often%20have%20a%20challenge%20identifying%20the%20total%20downtime%20of%20a%20device%20or%20an%20application.%20In%20this%20blog%20we%20will%20look%20at%20some%20of%20the%20common%20scenarios%20and%20see%20how%20we%20can%20use%20the%20power%20of%20Kusto%20Query%20Language%20to%20solve%20this%20challenge.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Apr 28 2020 02:44 PM
Updated by: