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
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"
@surekhasplunk, have to tried the search provided by @somesoni2? It uses wildcard in filter from inputlookup.
|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.
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*
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.
@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.
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
You need to give stats an alias... add as myCount as the alias just after stats
@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
These would be my answers too, with a typo correction of as field3
with by field3
.
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!