topic Re: grouping count by multiple field substrings in Splunk Search

grouping count by multiple field substrings

OrionCulver — Thu, 09 Nov 2023 13:39:04 GMT

Hi,

We currently have events where identifying the app that makes the event depends multiple fields, as well as substrings in within those fields. For example,

app 1 is identified by SourceName=Foo "bar("
app 2 is identified by SourceName=Foo "quill("
app 3 is identified by SourceName=Foo
app 4 is identified by source=abcde
app 5 is identified by sourcetype=windows eventcode=11111

I would like to count the number of errors per app, but not having luck yet. I've tried regexes & an eval case match pattern, & I can't seem to google the correct words to find a similar scenario in others' posts.

Please help.

Thanks,

Orion

Re: grouping count by multiple field substrings

richgalloway — Thu, 09 Nov 2023 13:56:05 GMT

Have you tried setting a new field that defines the app and then grouping on that field?

Re: grouping count by multiple field substrings

OrionCulver — Thu, 09 Nov 2023 14:36:39 GMT

@richgalloway
thanks! that might be working... how do I include count of zero when there are no matches? like,

app_name error_count
app1 0
app2 0
app3 5
app4 0
app5 233

Re: grouping count by multiple field substrings

richgalloway — Thu, 09 Nov 2023 14:44:57 GMT

Finding something that is not there is not Splunk's strong suit. See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

If the set of apps is small enough, you may be able to append a static set of zero counts and then add them to what Splunk produces.

<<current query>> | append [ makeresults format=csv data="app_name,error_count app1,0 app2,0 app3,0 app4,0 app5,0"] | stats sum(error_count) as error_count by app_name

Re: grouping count by multiple field substrings

OrionCulver — Thu, 09 Nov 2023 15:12:05 GMT

@richgalloway
so combining your responses, something like this?

| eval appType = case(SourceName="Foo \"bar(\"", "app1", SourceName="Foo \"quill(\"", "app2", SourceName="Foo", "app3", source=abcde, "app4", sourcetype=windows AND eventcode=11111, "app5", 1==1, "other") | stats count by appType | append [ makeresults format=csv data="app_name,error_count app1,0 app2,0 app3,0 app4,0 app5,0"] | stats sum(error_count) as error_count by app_name

Re: grouping count by multiple field substrings

richgalloway — Thu, 09 Nov 2023 15:07:55 GMT

Almost. The append must use the same field names as the main search (I used field names from your example output).

| eval appType = case(SourceName="Foo \"bar(\"", "app1", SourceName="Foo \"quill(\"", "app2", SourceName="Foo", "app3", source=abcde, "app4", sourcetype=windows AND eventcode=11111, "app5", 1==1, "other") | stats count by appType | append [ makeresults format=csv data="appType,count app1,0 app2,0 app3,0 app4,0 app5,0"] | stats sum(count) as count by appType