Help counting, the field, pageName, when I'm trying to count two or more specific values.

[rick4039]

Using Splunk Cloud - 7.0.11
My goal is to create a search and generate a table that I can use with MLTK. I'm searching for a relationship between a particular API, it's responseTime, two specific web pages and corresponding status codes.

When the API, serviceName="/api/more/auth" appears, I want to capture the count for the field, pageName but only when its value is: pageName="Error : API Failure" OR pageName="Device : Device Wall"
Along with this, I'm after the count for a handful of status' (i.e. responseCode is 422, 500, 503 and 504 as well as the responseTime.
Here is my sample search:

sourcetype=json_auto_timestamp index="webprod" serviceName="/api/more/auth" AND (responseCode=422 OR responseCode=500 OR responseCode=503 OR responseCode=504) AND responseTime="*" OR pageName="Error : API Failure" OR pageName="Device : Device Wall"
| bin _time span=5m
| stats
count(pageName="Error : API Failure") as PageUnav
count(pageName="Device : Device Wall") as DevWall
avg(responseTime) as RspTimeAvg
count(eval(responseCode==422)) as 422
count(eval(responseCode==500)) as 500
count(eval(responseCode==503)) as 503
count(eval(responseCode==504)) as 504
by _time

Everything works great except pageName. I'm not sure how to handle a field when I'm looking for two specific values. If I search for just one page, I can do: | count(pageName) and it works great.

Appreciate the input!

[woodcock]

Like this:

index="webprod" AND sourcetype="json_auto_timestamp" AND serviceName="/api/more/auth" AND responseCode IN("422","500","503","504") AND (responseTime="*" OR pageName="Error : API Failure" OR pageName="Device : Device Wall")
| timechart limit=0 span=5m avg(responseTime) AS RspTimeAvg count(eval(responseCode==422)) AS 422 count(eval(responseCode==500)) AS 500 count(eval(responseCode==503)) AS 503 count(eval(responseCode==504)) AS 504 BY pageName

[rick4039]

I didn't try this format. I'll give it a try this morning when I'm back in Splunk. Thanks for the help!

[woodcock]

I had a typo but I fixed it.

[oscar84x · Accepted solution]

Hi. I tried to solve this with some of the data I have in my lab. You could try using sub-searches and appending the results to the outer search.
First, sub-search does a timechart with a span of 5 minutes for the pageName field. The second sub-search does the average for responseTime by _time (like your original search). And lastly the outer search takes care of the responseCode field with the codes you specified, also a timechart with span=5m.
The results for the two sub-searches get appended to the results for the outer search.

    index="webprod" sourcetype=json_auto_timestamp  serviceName="/api/more/auth" AND (responseCode=422 OR responseCode=500 OR responseCode=503 OR responseCode=504)
| fields responseCode
| timechart count(responseCode) span=5m by responseCode
| appendcols 
    [search index="webprod" sourcetype=json_auto_timestamp  serviceName="/api/more/auth" AND (pageName="Error : API Failure" OR pageName="Device : Device Wall")
    | fields pageName
    | timechart span=5m count(pageName) by pageName]
| appendcols
 [search index="webprod" sourcetype=json_auto_timestamp  serviceName="/api/more/auth"
 | fields responseTime
    | bin _time span=5m 
    | stats avg(responseTime) as RspTimeAvg
    by _time]

[rick4039]

This worked great! I got the data I needed and I can see some interesting possiblities on using this approach for other types of data we want to analyze. The only odd thing I discovered was when I selected job settings in search, "send to backlog" was grayed out. But will need to keep the range of searches to a smaller time range and append the data to the csv. Thanks again, this was great!

[oscar84x]

Excellent! I'm glad it worked and glad to help. Thank you.

[adonio]

maybe i am missing something, but have you tried the eval with OR ?

.... all your search ... | stats count(eval(pageName="Error : API Failure" OR pageName="Device : Device Wall")) as page_count .... more stuff

[rick4039]

Thanks! This is similar to what I was trying. It would return 0 for the value of pageName. If I did only one page per search, it would work but didn't seem to be able to handle two or more.