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.
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
Have you tried using the top command? This will give you a count and a percentage.
| top 10 field
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.
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
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!
If field="bar"
for every SessionID
, how does your eval'd distinct count differ from a distinct count without any filter?
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.