topic Re: count two status by resource name in Splunk Search

count two status by resource name

dreeck — Thu, 24 Jan 2013 20:32:40 GMT

hey All,

I'd like to produce a table or chart similar to the following:

---------- count(status=202) count(Status=404)
Resource 1 25 10
Resource 2 50 5
...

I'm getting close to what I want using this query;
sourcetype=access Status=404 OR Status=200 | stats c(Status) by Resource

but this gives me a chart that counts up all the caught status (202 and 404) in a single column.

Any ideas how I can seperate the 200s into seperate columns?

Extra credit: can I create a search that would isolate a time frame in which both 200s and 404s occur? For example, when the Splunk Natural Language release comes along, I'd want to say:
"Splunk, show me the most recent hour in which experienced both 200s and 404s"

Re: count two status by resource name

dreeck — Thu, 24 Jan 2013 20:33:44 GMT

Side note: if I use this
sourcetype=access Status=404 OR Status=200 | stats c(Status=200), c(Status=404) by Resource

I get the chart format I want, but the counts are always zero.

Re: count two status by resource name

yannK — Thu, 24 Jan 2013 20:36:51 GMT

use the chart command :
http://docs.splunk.com/Documentation/Splunk/5.0.1/SearchReference/Chart

sourcetype=access Status=404 OR Status=200 | chart count over Resource by Status

Re: count two status by resource name

dreeck — Thu, 24 Jan 2013 20:41:23 GMT

Awesome, I didn't know about Over.

How can I restrict the chart to resources with counts greater than 0?

Re: count two status by resource name

jonuwz — Thu, 24 Jan 2013 21:01:50 GMT

This'll do it :

sourcetype=access Status=404 OR Status=200 | chart count over Resource by Status

example :

index=_internal sourcetype="splunkd_access" earliest=-1d | chart count over uri by status

To split this out into hourly data (using the example)

index=_internal sourcetype="splunkd_access" earliest=-1d
| bin _time span=1h
| eval uri=_time.";".uri
| chart count over uri by status
| rex field=uri "(?<_time>\d+);(?<uri>.*)"

To look for when 2 fields have data add (for example)

| where $404$>0 AND $200$> 0

(note I have to wrap the fields in $ signs so splunk knows these are field names and not raw numbers)

Now you can

| sort - _time | head 1

To get the latest.

Re: count two status by resource name

dreeck — Thu, 24 Jan 2013 21:28:25 GMT

Awesome, thanks!

Re: count two status by resource name

yannK — Thu, 24 Jan 2013 22:08:12 GMT

Difficult to do after the chart, because the fields names are replaced by the fied values after the chart.

So you have to use a stats before, filter, then add a chart after for presentation.
sourcetype=access Status=404 OR Status=200 | stats count by Resource Status | where count >0 | chart values(count) over Resource by Status