Building for the Splunk Platform

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

macadminrohit
Contributor

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.

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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
0 Karma

macadminrohit
Contributor

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

0 Karma

macadminrohit
Contributor

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

0 Karma

macadminrohit
Contributor

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

0 Karma

macadminrohit
Contributor

I need a timechart of the ratio.

0 Karma
Get Updates on the Splunk Community!

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...