Splunk Search

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

surekhasplunk
Communicator

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

Tags (2)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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"

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

surekhasplunk
Communicator

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

0 Karma

niketn
Legend

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*
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

surekhasplunk
Communicator

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

surekhasplunk
Communicator

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

somesoni2
SplunkTrust
SplunkTrust

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

niketn
Legend

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!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...