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?
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
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
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
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!
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.