topic Re: How can I get the count of two different field values in the same search? in Splunk Search

How can I get the count of two different field values in the same search?

zacksoft — Fri, 10 Nov 2017 13:01:28 GMT

My splunk query is ,

host=x OR host=y OR host=z nfs1
| stats count as nfs1_count

In the above case nfs1 field is searched from the three hosts and if found the event count is displayed as nfs1_count.

My concern is, I have another field called 'nfs2' ,that too is needed to be searched from the same three hosts(x,y,z) and the event count needs to be collected. Later the event counts(the numeric values) for fields nfs1 and nfs2 are to be put in a table or a pie chart.
Is it possible to achieve this in one search query ?

Re: How can I get the count of two different field values in the same search?

gcusello — Fri, 10 Nov 2017 14:07:38 GMT

Hi zacksoft,
try something like this:

host=x OR host=y OR host=z
| eval nfs=case(nfs1=*,"nfs1_count"," ",nfs2=*,"nfs2_count")
| stats count BY nfs

Bye.
Giuseppe

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Fri, 10 Nov 2017 14:18:36 GMT

HI @zacksoft,

Can you please try this one?

(host=x OR host=y OR host=z) (nfs1=* OR nfs2=*) | stats count(eval(isnotnull(nfs1))) as nfs1_countcount(eval(isnotnull(nfs2))) as nfs2_count

Thanks

Re: How can I get the count of two different field values in the same search?

zacksoft — Fri, 10 Nov 2017 14:44:24 GMT

Thanks Giuseppe for the response.
The eval statement throws error
Error in 'eval' command: The expression is malformed. An unexpected character is reached at ',"nfs1_count"," " ,nfs2=,"nfs2_count")'.

Re: How can I get the count of two different field values in the same search?

zacksoft — Tue, 29 Sep 2020 16:41:34 GMT

hi Kamlesh,
Thanks for the response.
The query runs without any error , but the count for nfs1_count and nfs2_count shows as zero.
But I am pretty sure there are many events in the search that contains the word nfs1 and nfs2.

Re: How can I get the count of two different field values in the same search?

zacksoft — Fri, 10 Nov 2017 15:28:40 GMT

@kamlesh_vaghela: I'm not sure , but I think the phrasse " (nfs1=* OR nfs2=*) " isn't able to match any events with the keyword nfs1 or nfs2. Could we have any alternative command to this.

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Fri, 10 Nov 2017 15:32:34 GMT

HI @zacksoft,

nfs1 and nfs2 are just words in events OR fields name?
If it is words then try below search.

(host=x OR host=y OR host=z) ("nfs1" OR "nfs2") | stats count(eval(like(_raw,"%nfs1%"))) as nfs1_count count(eval(like(_raw,"%nfs2%"))) as nfs2_count

if it is field then try below search.

(host=x OR host=y OR host=z) (nfs1=* OR nfs2=*) | stats count(eval(isnotnull(nfs1))) as nfs1_countcount(eval(isnotnull(nfs2))) as nfs2_count

🙂
Happy Splunking

Re: How can I get the count of two different field values in the same search?

zacksoft — Tue, 29 Sep 2020 16:41:42 GMT

@kamlesh_vaghela

nfs1 and nfs2 are two words not fields. and the search you provided works brilliantly.
Thank you.
If I may ask, what do I have to change in the search, if instead of nfs1 word I want both nfs1 and error (error is just another word not a field).

What I mean is , "nfs1_count" should give the number of events containing the word 'nfs1' AND the word 'error'. Similarly nfs2_count should give me all the event count containing the word 'nfs2' as well as the word 'error2'.

Re: How can I get the count of two different field values in the same search?

gcusello — Fri, 10 Nov 2017 15:52:28 GMT

Sorry, there's an error, try:
host=x OR host=y OR host=z
| eval nfs=case(nfs1=,"nfs1_count",nfs2=,"nfs2_count")
| stats count BY nfs
Bye.
Giuseppe

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Fri, 10 Nov 2017 16:14:01 GMT

HI @zacksoft,

Can you please try this??

(host=x OR host=y OR host=z) ("nfs1" OR "nfs2")  | stats count(eval(like(_raw,"%nfs1%") AND like(_raw,"%error%"))) as nfs1_count count(eval(like(_raw,"%nfs2%") AND like(_raw,"%error1%"))) as nfs2_count

Thanks

Re: How can I get the count of two different field values in the same search?

zacksoft — Tue, 29 Sep 2020 16:41:51 GMT

@kamlesh_vaghela
I tested it, It only searches the word nfs1 from the events and gives the nfs1_count.
It is not searching 'nfs1' AND 'error'.
Same with nfs2_count. It only showed the count for 'nfs2' , instead of 'nfs2' and 'error1'.
I opened the corresponding events in verbose mode to verify this.

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Fri, 10 Nov 2017 16:51:46 GMT

HI
It should work,
Well, I did few changes in search. Can you please try this?

(host=x OR host=y OR host=z) ("nfs1" OR "nfs2") 
| eval nfs1 = if(like(_raw,"%nfs1%") AND like(_raw,"%error%"),1,0)
| eval nfs2 = if(like(_raw,"%nfs2%") AND like(_raw,"%error2%"),1,0)
| stats sum(nfs1) as nfs1_count sum(nfs2) as nfs2_count

Here I have managed flags in seperate fields.
Thanks

Re: How can I get the count of two different field values in the same search?

niketn — Sat, 11 Nov 2017 05:32:45 GMT

@zacksoft, it will be easy for community to assist if you can add some sample events from various hosts. Is it possible that nfs1 and nfs2 etc are applicable to different hosts since they are on different filesystem? Please add the events per host in that case to clarify.

Re: How can I get the count of two different field values in the same search?

zacksoft — Mon, 13 Nov 2017 06:42:00 GMT

@kamlesh_vaghela
I tried it. The query is not identifying "error" keyword and the result that shows me only comprises of keyword nfs1 or nfs2 . 😞

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Mon, 13 Nov 2017 06:49:08 GMT

Hi
Can you share you sample search ??

Re: How can I get the count of two different field values in the same search?

zacksoft — Tue, 29 Sep 2020 16:42:39 GMT

@Anonymous
Hi Giuseppe -
I get [Error in 'eval' command: The expression is malformed.]
Just so as you know. nfs1 and nfs2 are not splunk fields. They are just some keywords found in error logs. What I'm exactly seeking help for is,
nfs1_count should give me the count of the 'nfs1' AND 'error1'.
nfs2_count should give me the count of 'nfs2' AND 'error2'
(error1 and error2 are again just words in log, thy are not splunk fields.

Re: How can I get the count of two different field values in the same search?

zacksoft — Mon, 13 Nov 2017 06:52:07 GMT

@kamlesh_vaghela
Do you mean the 'search query' or 'search results' ?

Re: How can I get the count of two different field values in the same search?

niketn — Mon, 13 Nov 2017 06:54:43 GMT

@zacksoft, as requested please mock/anonymize some sample events from various host containing nfs1, nfs2 error and error1? The query that you need seems simple however, without understanding the underlying data we can not give you exact query.

Re: How can I get the count of two different field values in the same search?

kamlesh_vaghela — Mon, 13 Nov 2017 06:56:31 GMT

Search query.

Re: How can I get the count of two different field values in the same search?

zacksoft — Tue, 29 Sep 2020 16:42:41 GMT

@kamlesh_vaghela

host="something1.domain.com" OR "something2.domain.com" OR "something3.domain.com" OR "something4.domain.com" OR "something5.domian.com"
("struc" OR "xpo")
| eval struc = if(like(_raw,"%struc%") AND like(_raw,"%Error%"),1,0)
| eval xpo = if(like(_raw,"%xpo%") AND like(_raw,"%Error%"),1,0)
| stats sum(struc) as nfs1_count sum(xpo) as nfs2_count