topic Re: Splunk table with percentages in Splunk Search

Splunk table with percentages

kevink1 — Thu, 08 May 2014 19:21:26 GMT

I want is a table that looks like this, but it seems like there is no simple way:

Field        Count of sessions with the field   Percent of sessions with the field
field_1      count_1                            percent_1
field_2      count_2                            percent_2
field_3      count_3                            percent_3

This is the best way I have found to do it:

search (_field1_ OR _field2_ OR _field3_ OR _field_______that_______is_______in_______all_______sessions_) | stats dc(eval(if(field=_field1_, SessionID, NULL))) AS count1, dc(eval(if(field=_field2_, SessionID, NULL))) AS count2, dc(eval(if(field=_field1_, SessionID, NULL))) AS count3, dc(eval(if(field=_field_______that_______is_______in_______all_______sessions_, SessionID, NULL))) AS numberOfSessions | eval row=mvrange(1,4) | mvexpand row | eval Field=case(row=1,"field_______1", row=2,"field_______2", row=3,"field_______3") | eval Count=case(row=1,count1, row=2,count2, row=3,count3) | eval Percent=100*Count/numberOfSessions | table Field Count Percent

This works, but I feel like it is a pretty messy workaround and that there should be a better way. Also, I think using mvexpand makes it take longer. I could probably also do it with a transaction, but that also slows things down. I would like to just do dc(SessionID) by field, but then I can't get the total number of sessions into its own column to calculate the percentages.

Re: Splunk table with percentages

martin_mueller — Thu, 08 May 2014 19:32:40 GMT

So... your data looks like this?

SessionID=id1 field=foo
SessionID=id2 field=bar
SessionID=id3 field=baz
SessionID=id4 field=foo

And you'd like a result like this:

field   count  percentage
foo         2         50%
bar         1         25%
baz         1         25%

If so, you can do this:

your search | eventstats dc(SessionID) as total | stats dc(SessionID) as count avg(total) as total by field | eval percentage = round(count/total*100, 2)."%" | fields - total

Re: Splunk table with percentages

ecambra_splunk — Thu, 08 May 2014 19:36:02 GMT

Have you tried using the top command? This will give you a count and a percentage.

| top 10 field

Re: Splunk table with percentages

kevink1 — Thu, 08 May 2014 20:52:46 GMT

Thanks for the response! That is almost it. In every session, there is a log line with field=foo. The log lines I am interested in have field=bar, field=baz1, and field=baz2. I am looking for a distinct count of field=bar as the total.

I think eventstats is the command I was looking for, though. If I just do "| eventstats dc(eval(if(field="bar", SessionID, NULL))) as total" instead of "| eventstats dc(SessionID) as total", I should get the desired result, albeit with an extra row for the total, which is fine.

EDIT: Nvm, this is exactly what I was looking for.

Re: Splunk table with percentages

kevink1 — Thu, 08 May 2014 20:56:13 GMT

The top command will give me a percentage, but not the percentage I am looking for, as my total is only the count for one value of field. The rest of the values are the things that I am looking to count and get the percentages of. I think martin_mueller's solution is more along the lines of what I need.

Re: Splunk table with percentages

martin_mueller — Thu, 08 May 2014 21:01:25 GMT

If field="bar" for every SessionID, how does your eval'd distinct count differ from a distinct count without any filter?

Re: Splunk table with percentages

kevink1 — Thu, 08 May 2014 21:10:27 GMT

That's a good point. Looks like I'm just over complicating things. Anyways, eventstats is the key. Didn't know about it before. Thanks for the help!