Ratio of total number Informational events and events with certain keywords into it

[macadminrohit]

My query is like this, here eventstats is not doing what it is supposed to do.

index=servers sourcetype=xs_json | rename hdr.level as LEVEL

| stats count(eval(searchmatch("not available"))) as ERROR_COUNT | streamstats count(eval(LEVEL="Information")) as INFO | eval RATIO=(INFO/ERROR_COUNT)

I am getting the values for ERROR_COUNT by not for the field INFO. But when i run stats on count(eval(LEVEL="Information")) as INFO i can see the values but not with eventstats.

Basically i want a ratio as seen in the above query. Ratio of TOTAL number of messages divided by the number of events in which "not available" was present.

[somesoni2 · Accepted solution]

If the Total Event count is all events, including error events (to calculate ratio of all vs errors), try like this (showing hourly timechart, adjust span per your need)

index=servers sourcetype=xs_json | rename hdr.level as LEVEL 
| eval isError=if(searchmatch("not available"),1,0)
| timechart span=1h sum(isError) as ERROR_COUNT count as TOTAL_COUNT
| eval RATIO=round(TOTAL_COUNT/ERROR_COUNT,2) | table _time RATIO

If the Total Event count is all INFO events, NOT including error events (to calculate ratio of info vs errors), try like this (showing hourly timechart, adjust span per your need)

index=servers sourcetype=xs_json | rename hdr.level as LEVEL 
| eval isError=if(searchmatch("not available"),1,0)
| eval isInfo=if(LEVEL="Information",1,0)
| timechart span=1h sum(isError) as ERROR_COUNT sum(isInfo) as INFO_COUNT
| eval RATIO=round(INFO_COUNT/ERROR_COUNT,2) | table _time RATIO

[macadminrohit]

the query is still running as i am getting 30 days data, any idea why eventstats didnt work?

[macadminrohit]

sorry for the type, in the main query the command should be eventstats not streamstats, which is also not working

[macadminrohit]

I even tried appendcols but it doesnt work. Always getting the value for INFO as 0.

[macadminrohit]

I need a timechart of the ratio.