topic Re: Help counting, the field, pageName, when I'm trying to count two or more specific values. in Splunk Search

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

rick4039 — Wed, 30 Sep 2020 03:10:54 GMT

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!

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

adonio — Tue, 03 Dec 2019 18:50:25 GMT

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

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

oscar84x — Tue, 03 Dec 2019 20:12:40 GMT

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]

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

woodcock — Wed, 04 Dec 2019 07:26:11 GMT

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

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

rick4039 — Wed, 04 Dec 2019 14:09:06 GMT

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.

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

rick4039 — Wed, 04 Dec 2019 14:14:26 GMT

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!

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

rick4039 — Wed, 04 Dec 2019 14:17:18 GMT

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

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

oscar84x — Wed, 04 Dec 2019 14:35:49 GMT

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

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

woodcock — Wed, 04 Dec 2019 18:23:51 GMT

I had a typo but I fixed it.