How to Print Id's which are not present in Index search?

[RahulMisra]

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

[RahulMisra]

Just one last thing, i am getting desired results on stats ( as per my lookup file)

but , events are not aliighning to stats

[aromanauskas]

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

[ITWhisperer]

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

[RahulMisra]

No data coming in.

[ITWhisperer]

Are you using the correct field names? (Fieldnames are case-sensitive)

[RahulMisra]

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.

[gcusello · Accepted solution]

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

[RahulMisra]

getting error:

 Error in 'EvalCommand': The expression is malformed. Expected ).

[RahulMisra]

@gcusello  I am getting the desired results.  Accepting the solution. 

[gcusello]

Hi @RahulMisra ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉