How to calculate availability of API on daily basis

[MousumiChowdhur]

Hi,

I have multiple APIs in my log whose availability duration needs to be determined on daily basis i.e., from 00 to 24 hours based on active and inactive status, which means, it will have to check the status of the API from the last event of previous day to the first event of current day to check the status of that particular API. But to make any calculation on availability it will have to start the calculation only since 00 hour.

Kindly help to build the query, this is how far I've managed to go.

`urlendpoint` 
| search endpoint=* 
| eval Brand="xyz" 
| eval status=case(like(elb_status_code,"2%") OR like(elb_status_code,"3%") OR like(elb_status_code, "505") OR like(elb_status_code, "510") OR like(elb_status_code, "511"), "active", like(elb_status_code,"4%") OR like(elb_status_code,"500"), "inactive")
| reverse 
| streamstats current=f last(_time) AS last_time last(status) AS last_status by endpoint 
| stats values(last_time) AS last_time values(last_status) AS last_status values(status) AS status by endpoint, _time Brand 
| eval active_time=case(last_status="active", _time-last_time) 
| eval inactive_time=case(last_status="inactive", _time-last_time) | eval day = strftime(_time, "%d") 
| eval month=strftime(_time, "%m") 
| eval Date = strftime(_time, "%d/%m/%y") | stats sum(active_time) AS active by day month Date Brand endpoint 
| eval active=active/(3600) 
| sort - month day 
| fields - month
| fillnull value=0

Thanks!

[elliotproebstel]

The way I'd approach this is to add some calculations in the middle of the search to find the timestamp of the first event per day, per endpoint and also the last event per day, per endpoint. When calculating the active_time and inactive_time, I'd check to see if the current event was the first event of the day. If it is, then the calculation of active/inactive time will be _time-relative_time(_time, "@d"), and if it's the last event of the day, then the calculation of active/inactive time will be relative_time(_time, "+1d@d")-_time.

So I think it would wind up like this:

`urlendpoint` 
| search endpoint=* 
| eval Brand="xyz" 
| eval status=case(like(elb_status_code,"2%") OR like(elb_status_code,"3%") OR like(elb_status_code, "505") OR like(elb_status_code, "510") OR like(elb_status_code, "511"), "active", like(elb_status_code,"4%") OR like(elb_status_code,"500"), "inactive")
| reverse 
| streamstats current=f last(_time) AS last_time last(status) AS last_status by endpoint 
| bin span=1d _time as day
| eventstats earliest(_time) AS first_of_day latest(_time) AS last_of_day BY day
| stats values(last_time) AS last_time values(last_status) AS last_status values(status) AS status by endpoint, _time Brand 
| eval active_time=case(last_status="active" AND _time=first_of_day, _time-relative_time(_time, "@d"), last_status="active" AND _time=last_of_day, _time-relative_time(_time, "@d"), last_status="active", _time-last_time) 
| eval inactive_time=case(last_status="inactive" AND _time=first_of_day, _time-relative_time(_time, "@d"), last_status="inactive" AND _time=last_of_day, _time-relative_time(_time, "@d"), last_status="inactive", _time-last_time) 
| eval day = strftime(_time, "%d") 
| eval month=strftime(_time, "%m") 
| eval Date = strftime(_time, "%d/%m/%y") 
| stats sum(active_time) AS active by day month Date Brand endpoint 
| eval active=active/(3600) 
| sort - month day 
| fields - month
| fillnull value=0

I don't have a good dataset to test this on, so I'm happy to iterate if this gets you part of the way but has issues. 🙂 Hopefully the description at the top is clear enough to communicate the intent.

[elliotproebstel]

Looking at this again - there's definitely a logic error that will arise at line 9, because the first_of_day and last_of_day won't pass through the stats command. But I'm not totally clear on the purpose of that line, so I can't quite figure out how to fix it.