I have a lookup file( with one column combinedrules{}) which would be dynamic and i want to run a scheduled search to print ID's that are not present in the index search(has same column combinedrules{}).
Lookup File
combinedrules{}
324252
543246
search
Combinedrules{} | Search(index) | Inputlookup file |
324252 | No | Yes |
432324 | No | Yes |
Hi @RahulMisra,
you could try something like this:
index=your_index
| eval Combinedrules{}=lower(Combinedrules{})
| stats count BY Combinedrules{}
| append [ | inputlookup your_lookup.csv | eval Combinedrules{}=lower(Combinedrules{}), count=0, lookup_check="yes" | fields Combinedrules{} count lookup_check ]
| fillnull value="no" lookup_check
| stats
sum(count) AS total
values(lookup_check) AS lookup_check
dc(lookup_check) AS lookup_check_count
BY Combinedrules{}
| eval
search_index=if(total=0,"no","yes),
lookup_check=if(lookup_check_count=1,lookup_check,"yes")
| table Combinedrules{} search_index lookup_check
Ciao.
Giuseppe
Just one last thing, i am getting desired results on stats ( as per my lookup file)
but , events are not aliighning to stats
You have a couple different ways to do this. if you have say 10000 items in the lookup table.. but 10Million in the index many of which are NOT in the lookup then you probably want to use it as part of the search.
index=<indexname> [ | inputlookup <filename> | fields combinedrules{} | format]
| append [ | inputlookup <filename> | eval sourcetype=lookupfile ]
|stats dc(sourcetype) AS sources by combinedrules{}
| eval Search_Index=if(sources > 1,"Yes","No")
| eval Inputlookup_File = "Yes"
If you want to know if a value is IN the index but not the lookup file.. then:
index=<indexname>
| append [ | inputlookup <filename> | eval sourcetype=lookupfile ]
|stats dc(sourcetype) AS sources values(sourcetype) AS sourcetype by combinedrules{}
| eval Search_Index=if(sources > 1 OR NOT match(sourcetype,"(lookupfile)"),"Yes","No")
| eval Inputlookup_File =if(match(sourcetype,"(lookupfile)"),"Yes","No")
|fields combinedrules{},Search_Index,Inputlookup_File
<index search>
| dedup combinedrules
| eval found="Index"
| append
[| inputlookup lookupfile
| eval found="Lookup"]
| chart count by combinedrules found
| where isnull(Index) OR (Index==0 AND Lookup==1)
No data coming in.
Are you using the correct field names? (Fieldnames are case-sensitive)
I got some data after removing dedup
combinedrules{}
3000041 |
959073 |
981248 |
I want to see what events are there is search which has these Id's.
This is the combined rules field containing on raw events : "combinedrules": ["3900006", "3900007", "3900013", "3900020", "3900021", "3900036", "970901"
Appreciate your help.
Hi @RahulMisra,
you could try something like this:
index=your_index
| eval Combinedrules{}=lower(Combinedrules{})
| stats count BY Combinedrules{}
| append [ | inputlookup your_lookup.csv | eval Combinedrules{}=lower(Combinedrules{}), count=0, lookup_check="yes" | fields Combinedrules{} count lookup_check ]
| fillnull value="no" lookup_check
| stats
sum(count) AS total
values(lookup_check) AS lookup_check
dc(lookup_check) AS lookup_check_count
BY Combinedrules{}
| eval
search_index=if(total=0,"no","yes),
lookup_check=if(lookup_check_count=1,lookup_check,"yes")
| table Combinedrules{} search_index lookup_check
Ciao.
Giuseppe
getting error:
Error in 'EvalCommand': The expression is malformed. Expected ).
@gcusello I am getting the desired results. Accepting the solution.
Hi @RahulMisra ,
good for you, see next time!
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated by all the contributors 😉