Is there a way to get 'values' working with the top command? Or get the same effect?

[wrangler2x]

We use this search to give me a ranked view of active clients of a certain type:

index="exchange_index" cs_user_agent="Mac+OS*" | top c_ip,cs_username,cs_user_agent,sc_status limit="30"

exchange_index has iis logs from an Exchange server. We see cases, where a given IP number has two different matches: one with a username and status 200, and one with no username and status 401, like this:

128.200.235.121 AD\mcuser Mac+OS+X/10.8.3+(12D78);+ExchangeWebServices/3.0+(157);+Mail/6.3+(1503) 200
128.200.235.121 -         Mac+OS+X/10.8.3+(12D78);+ExchangeWebServices/3.0+(157);+Mail/6.3+(1503) 401

We'd like to be able to treat the sc_status as values to the other fields which are the same, and produce an output like this (or at least similar to it):

128.200.235.121 AD\mcuser Mac+OS+X/10.8.3+(12D78);+ExchangeWebServices/3.0+(157);+Mail/6.3+(1503) 200
                -                                                                                 401

I am able to do this with the stats command, as follows:

index="exchange_index" cs_user_agent="Mac+OS*" | stats values(cs_username), values(sc_status) by c_ip cs_user_agent

with this output:

  c_ip            cs_usr_agent                    values(cs_username)     values(sc_status)
1 101.172.170.148 Mac+OS+X/10.8.3+(12D78)+CalendarAgent/55        -       200
                                                                  AD\usrx 401

2 107.16.155.76   Mac+OS+X/10.8.3+(12D78)+CalendarAgent/55        -       200
                                                                  AD\usry 401

But of course, stats does not give me the same results as top does, and top does not seem to work with values, even though the syntax is so similar to stats.

Any ideas?

[wrangler2x]

With total on the count column:

index="exchange_index" cs_user_agent="Mac+OS*"
| stats values(cs_username) as usernames, values(sc_status) as status count by c_ip cs_user_agent | sort 30 -count c_ip cs_user_agent
| eventstats sum(count) as total
| eval percent=100*count/total| strcat percent "%" percent
| table c_ip, usernames, cs_user_agent, status, count, percent| addtotals col=t count row=f

[BobM · Accepted solution]

There are multiple ways of doing this including using transactions, subsearch and join etc. but probably the easiest for you would be to just sort your results. I wouldn't sort by a multi value field so your search should look somthing like :-

index="exchange_index" cs_user_agent="Mac+OS*" | stats values(cs_username) as usernames, values(sc_status) as status by c_ip cs_user_agent | sort 30 c_ip cs_user_agent

if you want the results to be in the same order as previously, add the table command.

... | table c_ip, usernames, cs_user_agent, status

Bob

[wrangler2x]

I've been meaning to get back to this for days, but had fires to put out. Your suggestion is very close to the mark, but to get the same output as top and the same results I had to make some minor modifications (top seems to do by-descending count):

index="exchange_index" cs_user_agent="Mac+OS*" | stats values(cs_username) as usernames, values(sc_status) as status count by c_ip cs_user_agent | sort 30 -count c_ip cs_user_agent | eventstats sum(count) as total | eval percent=100*count/total | strcat percent "%" percent | table c_ip, usernames, cs_user_agent, status, count, percent

Thank you!

[sideview]

what exactly is top doing that stats isn't doing? do you like the limit=30 thing that top does? Or maybe the calculation of percentages? Both can be done in other ways.