topic Re: how to add more than one matching criteria while getting the count in Splunk Search

how to add more than one matching criteria while getting the count

surekhasplunk — Wed, 22 Nov 2017 13:42:46 GMT

Hi @somesoni2

Can you help me with a simple search i have following requirement

from a single input lookup file. Now am using below query to get the field2 count by field3 for a particular pattern math.

|inputlookup file1.csv|search field1=abc|stats count(eval(match(field2,xyz) as output by field3

How can i add another matching criteria to the same query something like this.
|inputlookup file1.csv|search field1=abc|stats count(eval(match(field2,xyz AND filed4,222) as output by field3

Re: how to add more than one matching criteria while getting the count

niketn — Wed, 22 Nov 2017 14:46:17 GMT

@surekhasplunk, since this question is for @somesoni2, I will wait for him to answer 🙂 Seems like you are looking for this...

| inputlookup file1.csv 
| search field1="abc"
| stats count(eval(match(field2,"xyz") AND match(field4,"222"))) as myCount by field3

However performance wise you can try the following instead:

| inputlookup file1.csv where field1="abc" field2="*xyz*" field4="*222*"
| stats count as myCount by field3

PS: Since you have used match() in your question I have used asterisk ( * ) for wildcard search using where in the inputlookup command.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Inputlookup

Re: how to add more than one matching criteria while getting the count

somesoni2 — Wed, 22 Nov 2017 14:59:42 GMT

These would be my answers too, with a typo correction of as field3 with by field3.

Re: how to add more than one matching criteria while getting the count

niketn — Wed, 22 Nov 2017 15:04:36 GMT

Oh it was intentional alias for count(eval(...)). But I missed in the question whether it was originally as field3 or by field3 let me correct. Now I know why the question was directed specifically towards you 🙂 Thanks!

Re: how to add more than one matching criteria while getting the count

surekhasplunk — Wed, 22 Nov 2017 15:09:06 GMT

Hi @niketnilay,

Thanks for your response but stats count(eval(match(field2,"xyz") AND match(field4,"222"))) doesn't seem to be working i am getting syntax error Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field

And i can use the performance approach since i have got more than 1 field to evaluate

Re: how to add more than one matching criteria while getting the count

niketn — Wed, 22 Nov 2017 15:23:43 GMT

You need to give stats an alias... add as myCount as the alias just after stats

Re: how to add more than one matching criteria while getting the count

surekhasplunk — Wed, 22 Nov 2017 15:33:21 GMT

|inputlookup file1.csv|search category="Time" Region=UK "Location - City"="London"| stats count(eval("Onsite/Offsite" like "%Onsite%")) as ON , count(eval(match('Onsite/Offsite',"Offsite"))) as OFF by "CTO sub department" |eval Total=ON+OFF

Now i have another query
|inputlookup file1.csv|search category="Outcome" Region=UK "Location - City"="London"| stats count(eval("Onsite/Offsite" like "%Onsite%")) as ON , count(eval(match('Onsite/Offsite',"Offsite"))) as OFF by department |eval Total=ON+OFF

It just differs on the category value and i want output like below:
Time Outcome
ON|OFF|total|ON|OFF|total
department 10|10|20|22|33|55

hope its clear now. what i am trying to achieve.

Re: how to add more than one matching criteria while getting the count

niketn — Wed, 22 Nov 2017 16:29:13 GMT

Please try the following

| inputlookup file1.csv where ( category="Time" OR category="Outcome" )
| chart count(eval( category="Time" AND match('Onsite/Offshore',"Onsite"))) as "Time:ON" count(eval( category="Time" AND match('Onsite/Offshore',"Offsite")))  as "Time:OFF" count(eval( category="London" AND match('Onsite/Offshore',"Onsite")))  as "London:ON" count(eval( category="London" AND match('Onsite/Offshore',"Onsite"))) as "London:OFF" by department
| eval "Time:Total"='Time:ON' + 'Time:OFF'
| eval "London:Total"='London:ON' + 'London:OFF'
| table department Time* London*

Re: how to add more than one matching criteria while getting the count

somesoni2 — Wed, 22 Nov 2017 17:33:07 GMT

Give this a try

|inputlookup file1.csv
|search (category="*Time*" OR category="*Outcome*") Region=UK "Location - City"="*London*"
("Onsite/Offsite"="*Onsite*" OR "Onsite/Offsite"="*Offsite*")
| eval colField=if(match(category,"Time"),"Time","Outcome").":".if(match('Onsite/Offsite',"Onsite"),"ON","OFF")
| eval department=if(match(category,"Time"),'CTO sub department',department)
| chart count over department by colField
| eval "Time:Total"='Time:ON'+'Time:OFF' 
| eval "Outcome:Total"='Outcome:ON'+'Outcome:OFF' 
| table department "Time:ON" "Time:OFF" "Time:Total" "Outcome:ON" "Outcome:OFF" "Outcome:Total"

Re: how to add more than one matching criteria while getting the count

surekhasplunk — Thu, 23 Nov 2017 11:26:06 GMT

Thanks a lot it worked well for me and i got the required result.

But if i want to do a pattern match there i am getting error saying you have to dynamically allocate the values.

| inputlookup file1.csv where ( category="Time" OR category="Outcome" )
| chart count(eval( category="Time" AND match('Onsite/Offshore',"Onsite"))) as "Time:ON" count(eval( category="Time" AND match('Onsite/Offshore',"Offsite"))) as "Time:OFF" count(eval( category="London" AND match('Onsite/Offshore',"Onsite"))) as "London:ON" count(eval( category="London" AND match('Onsite/Offshore',"Onsite"))) as "London:OFF" by department
| eval "Time:Total"='Time:ON' + 'Time:OFF'
| eval "London:Total"='London:ON' + 'London:OFF'
| table department Time* London*

Can you please help me with that pattern match against the category values.

Re: how to add more than one matching criteria while getting the count

niketn — Thu, 23 Nov 2017 14:45:56 GMT

@surekhasplunk, can you please post above code with code button (with 101010) so that special characters do not escape?

Are you trying to do Pattern match in first inputlookup command? If yes then you will have to remove from where to next pipe with search, the way you had in your original query.

| inputlookup file1.csv 
| search ( category="Time*" OR category="Outcome" )

If wildcard is somewhere else please re-post your search with code button and mention the error you are receiving.

Re: how to add more than one matching criteria while getting the count

niketn — Thu, 23 Nov 2017 14:47:17 GMT

@surekhasplunk, have to tried the search provided by @somesoni2? It uses wildcard in filter from inputlookup.