How to calculate the SLA percentage from start event query and end event query?

[Sekhar]

I have two events one is  calculate the SLA percentage from below querys

Start event query 

Index=x source type= xx "saved msg" extacted fields s like manid,actionid,batch I'd

End event query

Index=y source type=y " recived msg" extacted fields like manid ,actionid

[woodcock]

(index="X" AND sourcetype="xx" AND "saved msg")
OR (Index="y" AND sourcetype="yy" AND "recived msg")
| streamstats count(eval(match(_raw, "recived msg"))) AS sessionID BY manid actionid
| stats count(eval(match(_raw, "saved msg"))) AS savedCount range(_time) AS duration min(_time) AS _time BY sessionID
| stats count AS total count(eval((savedCount > 0 AND duration > 30) OR (savedCount==0 AND (now() - _time) > 30))) AS SLA_blown
| eval SLA_percentage = 100 * (total - SLA_blown) / total

[ITWhisperer]

What exactly do you mean by "SLA percentage"?

SLA usually means Service Level Agreement, so SLA percentage, is that the percentage of people that have signed up to the agreement, out of everyone in your organisation, or at least the key stakeholders in whatever service is being provided?

[Sekhar]

Calculate different between start and end events grouped by manid and count number of mandate execceding different above 30 sec 

[ITWhisperer · Accepted solution]

Try something like this

(index=x source type=xx "saved msg") OR (index=y source type=y " recived msg")
| stats values(_time) as time values(actionid) as actionid values(batchid) as batchid by manid
| eval duration = max(time) - min(time)
| stats count count(eval(duration > 30)) as exceeded
| eval slapercentagefailure = 100*exceeded/count

[Sekhar]

Getting result like below 

Count       exceeded.   slapercentagefailure

66                1              1.5152

Same way can add the | stats count by date_hour

For SLA trendy by time of the day??

[ITWhisperer]

Which date_hour, when the start event happens or the end event?

[Sekhar]

Calculate different between start and end events grouped by manid and count number of mandate execceding different above 30 sec.  SLA trendy by time of the day.

Can I add any field for day_hour above query?

[ITWhisperer]

Sure - which time do you want to use, the min(time) or the max(time)?

[Sekhar]

Min(time)

[ITWhisperer]

(index=x source type=xx "saved msg") OR (index=y source type=y " recived msg")
| stats values(_time) as time values(actionid) as actionid values(batchid) as batchid min(_time) as _time by manid
| eval duration = max(time) - min(time)
| bin _time span=1h
| stats count count(eval(duration > 30)) as exceeded by _time
| eval slapercentagefailure = 100*exceeded/count

[Sekhar]

Min(time)

[Sekhar]

Getting result like below 

Count       exceeded.   slapercentagefailure

66               1.                     1.5152