Forum Discussion
How to analyse the missing timeframe logs
Hi ,
Subject might look different from what my question is.
I'm using requests table to find the availability of application. This table will have record for every minute . there won't be records if application is not reachable for some reason (VM Shutdown / planned maintenance/ etc)
Below query will fetch requests that are success in last 1 hour
requests
| where timestamp > ago(1h)
| where name in ( "/health")
| where success == "True"
|order by timestamp
//| summarize Success = sumif(itemCount, success == true)
| summarize Success = count() by name, bin(timestamp, 1m)
sample output ...
| 01/01/2022, 4:00:00.000 AM | /health | 2 | ||
| 01/01/2022, 4:07:00.000 AM | /health | 2 | ||
| 01/01/2022, 4:08:00.000 AM | /health | 2 | ||
| 01/01/2022, 4:09:00.000 AM | /health | 2 | ||
| 01/01/2022, 4:10:00.000 AM | /health | 2 |
There is a record for 4:00 AM and next record is at 4:07 AM - this is because VM has been shutdown and the JVM was not running for 5 min .
How can i write a query to display that system was not available for those 7 min in last one hour because requests table doesn't have record.
Thanks in advance!
4 Replies
- Hi,
Does anyone knows how to get those result?
Thanks !- Interestingly i came across, make-series operator which has an option to provide default value if no record is found. below queries gives 0 for the minutes where there is no record from 04:00 to 04:07
requests
|where name in ( "/health")
|make-series num=count() default=0 on timestamp from datetime(2022-01-01 02:00) to datetime(2022-01-01 02:15) step 1m by name
eg:
num
[0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
Is there equivalent operator like 'default' for summarize statement so there will be rows with missing timeframe with value as '0'
Thanks!
- Maybe something like
| summarize _count = count() by bin(TimeGenerated, 1m)
| extend _alive=iff(_count > 1, true, false)- Thanks @clive ,
this query has given the same result .
