Splunk Search

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

rick4039
Explorer

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!

Tags (1)
0 Karma
1 Solution

oscar84x
Contributor

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]

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

rick4039
Explorer

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

0 Karma

woodcock
Esteemed Legend

I had a typo but I fixed it.

0 Karma

oscar84x
Contributor

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]
0 Karma

rick4039
Explorer

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
Contributor

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

0 Karma

adonio
Ultra Champion

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

0 Karma

rick4039
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...