Splunk Search

Splunk table with percentages

kevink1
Explorer

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.

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

ecambra_splunk
Splunk Employee
Splunk Employee

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

| top 10 field

kevink1
Explorer

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

kevink1
Explorer

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!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

kevink1
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...