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

[zacksoft]

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 ?

[niketn]

@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.

[niketn]

@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.

[zacksoft]

@niketnilay
Thanks for the response. The word nfs1,nfs2,error are just some keywords not splunk fields.
And the hosts are exactly identical type webserver, only with different names. When I query seraching for nfs1 and error 1, I get the proper result. When I query again searching nfs2 and error2 , I get all the relevant results too.
But what I wanted is to write "one" query for this.

[niketn]

Yes we know these are text in raw data and not fields. The reason why I am asking for sample events is that the queries provided by community members should have worked for you as per the information provided. Since they have not worked, it would be helpful if we look at raw event data and start with clean slate.

You have pasted one sample event below in one of the threads. It belongs to category ** "struc" AND "ERROR" **

2017-11-13 02:08:37,145 PHUTAN-EventThread-6652 ERROR AB12345 126x1221146x1 e68i5w 30.142.116.35,30.128.209.1 /rest/struc/2.0/poll [c.o.scriptrunner.runner.AbstractScriptListener] Script function failed on event: com.bsa.phutan.event.issue.IssueEvent, file:
java.lang.NullPointerException: Cannot invoke method getRelated() on null object
at org.ofbiz.core.entity.GenericValue$getRelated.call(Unknown Source)
at Script3.run(Script3.groovy:89)
host = VA1056575.trwaiviskll.com source = /apps/bsa/phutan/logs/catalina.out sourcetype = bsa:phutan:catalina username = AB12345

Can you paste sample event for other category "xpo" AND "ERROR 1"? Also by any chance do you have other combination like "struc" AND "ERROR 1" or "xpo" AND "ERROR". As per your question they should not exist or are not supposed to be used for stats.

Having said these please try out the following ( In the lack of sample event for xpo I am assuming they have text ERROR 1:

(host="hg67plvbsa788.vadnski.com" OR "hg67plvbsa781.vadnski.com" OR "hg67plvbsa783.vadnski.com" OR "hg67plvbsa784.vadnski.com" OR "hg67hgtrre388.vadnski.com")
("struc" AND "ERROR") OR ("xpo" AND "ERROR 1")
| stats count(eval(searchmatch("struc"))) as nfs1 count(eval(searchmatch("xpo"))) as nfs2

Since I have filtered only those events containing struc which also have ERROR and only those from xpo which have ERROR 1, the count with searchmatch() for struc and xpo. Should suffice the need. You do not need separate eval before the stats command. PS: This should have better performance as required events are filtered in base search, searchmatch() is not case sensitive and separate eval is not required.

Please try out and confirm. If it does not work you will have to provide one sample event from xpo, so that we can assist you with actual query.

If it is possible for you to convert a working query, we can also provided you with a run anywhere search based on Splunk's _internal index on similar pattern as your query and then you can change search filter and field names as per what you have got.

[zacksoft]

kamlesh_vaghela has provided a solution which works as expected.

[niketn]

Glad you were able to find a working solution 🙂

[kamlesh_vaghela · Accepted solution]

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

[zacksoft]

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.

[zacksoft]

@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.

[kamlesh_vaghela]

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

[zacksoft]

@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'.

[kamlesh_vaghela]

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

[zacksoft]

@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.

[kamlesh_vaghela]

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

[zacksoft]

@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 . 😞

[kamlesh_vaghela]

Hi
Can you share you sample search ??

[zacksoft]

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

[kamlesh_vaghela]

Search query.

[zacksoft]

2017-11-13 02:08:37,145 PHUTAN-EventThread-6652 ERROR AB12345 126x1221146x1 e68i5w 30.142.116.35,30.128.209.1 /rest/struc/2.0/poll [c.o.scriptrunner.runner.AbstractScriptListener] Script function failed on event: com.bsa.phutan.event.issue.IssueEvent, file:
java.lang.NullPointerException: Cannot invoke method getRelated() on null object
at org.ofbiz.core.entity.GenericValue$getRelated.call(Unknown Source)
at Script3.run(Script3.groovy:89)
host = VA1056575.trwaiviskll.com source = /apps/bsa/phutan/logs/catalina.out sourcetype = bsa:phutan:catalina username = AB12345

[kamlesh_vaghela]

Hi,

Can you please try this search?

(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

Just made capital "Error" - "ERROR"

Thanks