Splunk Dev

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...