Show only those users who exceed percentage of a certain value

[plucas_splunk]

I have a preliminary search of a web-server-like log that looks like:

index=whatever Method=GET
| where Response in (200,404)
| replace 200 with "Hit", 404 with "Miss" in Response

There is also a User field. I want to:

• Calculate the percentage of misses from the total, e.g., p = misses / (hits + misses), by User.
• Show the Hits & Misses for only those users where the percentage of misses exceeds some percentage, say 50%.

How can I add to the search to get what I want? Thanks.

[ITWhisperer]

| makeresults count=10
| streamstats count as row 
| eval user="User_".mvindex(split("ABC",""),row%3)
| eval Response=200+(204*(row%2))
| replace 200 with "Hit", 404 with "Miss" in Response
| stats count(eval(if(Response="Hit",true(),null))) as hits count(eval(if(Response="Miss",true(),null))) as misses by user
| eval percent=100*misses/(hits+misses)
| where percent>=50

[plucas_splunk]

Could you explain what these lines:

| streamstats count as row 
| eval user="User_".mvindex(split("ABC",""),row%3)
| eval Response=200+(204*(row%2))

do and why they are needed given that I don't care about a 204 value and I already have a User field?

[ITWhisperer]

They generate dummy data - 200+204=404 so every other event is either 200 or 404

They are not needed for your solution, they are just there as a runanywhere example to show you the effect of the other lines.

[plucas_splunk]

OK, fine.  But when I append your solution to my real search, I just get event rows. I want to see the results like:

User	Hits	Misses	Percent
bob	5	3	38
alice	7	9	56

ordered by decreasing percentage.

[ITWhisperer]

Try adding

| table user hits misses percent
| sort - percent