I have to plot a graph from 0 to 1 for different clients but didn't finding any exact queries to do so.
Each of the clients are maintaining two status started or ended.For some client ended can't appear in logs if some exceptions or errors occurs.I want to plot the graph as
1) 0 in case the client is not started
2) 1 in case it is started but not ended.
3) 0 in case it is ended.
4) 1 in case it is started but not ended i.e running.
I have tried some queries but none have give the exact data.
earliest="-5d" tag="aa" sourcetype="bb" "Client started." OR "Client ended."|rex "Notification Client - (?[\S]+)]"|rex "Client (?[\S]+)."|eval status=0|eval status=case(ClientStatus=="started",1,ClientStatus=="ended",0)|sort _time|streamstats last(status) by ClientId,_time global=t current=t|timechart last(status) by ClientId
I am getting some null values that I want to fill up with appropriate values.
Any idea to do so.
One way to do this is is to use transactions to join started and ended events. I assume you only get one pair per clientid.
earliest="-5d" tag="aa" sourcetype="bb" "Client started." OR "Client ended."
|rex "Notification Client - (?<clientid>[S]+)]"
|rex "Client (?<clientstatus>[S]+)."
| transcation clientid
| eval status=case(clientstatus=="started" AND clientstatus=="ended",0, clientstatus=="started",1, clientstatus=="ended",0)
| timechart last(status) by clientid
If you get more than one start/end pair per clientid, you need to add startswith, endswith and keeporphans. eg.
| transcation clientid startswith="ended" endswith="started" keeporphans
Note I have swaped the start and end terms rather than sort the data by time.