count two status by resource name

[dreeck]

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"

[jonuwz]

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.

[dreeck]

Awesome, thanks!

[yannK · Accepted solution]

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

[yannK]

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

[dreeck]

Awesome, I didn't know about Over.

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

[dreeck]

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.