How to find the totals of status codes per uri per day?

[Arjang]

I am using the following search:

( sourcetype=iis ) sc_status=500 |stats count by  uri_path sc_status date

but that only gives me the failures, I want the successes for them as well i.e. sc_status=200 or other sc_status

If I try :

( sourcetype=iis ) |stats count by  uri_path sc_status date

I get too many results that had never had 400, 500, i.e. the ur_path s that always were successful,
I just want the

( sourcetype=iis ) |stats count by  uri_path sc_status date

results sets that contain at least one sc_status >400

I tried using join, inner join (1)

 ( sourcetype=iis ) sc_status=500 |stats count by  uri_path sc_status date

with (2)

 ( sourcetype=iis ) |stats count by  uri_path sc_status date

I got this :

( sourcetype=iis ) sc_status=500 |fields  uri_path | join uri_path [search sourcetype=iis | fields uri_path,sc_status,date ] | stats count by uri_path , sc_status , date| sort -count

but the result does not contain any sc_status = 500

The result should be (2) where each one of the uri_path was in (1).
That means sc_status = 500 should also be included in the final result.
Maybe there is an alternative way of finding the totals of status codes per uri per day. I would be happy with just a result like so

uri_path,sc_"statusLessThan400","sc_statusGreaterThanOrEqualTo400",date

[niketn · Accepted solution]

@Arjang, please try the following:

 sourcetype=iis ) sc_status=*
| stats count(eval(sc_status=200)) as Success count(eval(sc_status!=200)) as Failures by  uri_path sc_status date

[Arjang]

Thank you!

I ended up using :

(sourcetype=iis ) sc_status=* CurrentWork | stats count(eval(sc_status=200)) as Success count(eval(sc_status!=200)) as Failures by  uri_path date | search Failures > 0 | fields uri_path, date, Success,Failures

[niketn]

Great... I am sorry I think I missed the second part of your question. Glad you figured it out 🙂

[Arjang]

you did the hardest part, once there are results filtering was easy.