Splunk Search
Highlighted

Need to compare fields in a csv

I want to create a search that will use a csv to ignore results if the result of the search has fields equal to a row in the csv i.e. suppose I have a csv that has two columns e.g. Col1 and Col2 with values of x and y etc. I want the search to equate field A in the search with x in the csv and also equate field B with y in the csv. If both are equal I do not use this in a report, dashboard etc. I've tried various combinations of where and inputlookup but it is eluding me. The reason for a csv is because I need to allow users to update this file as and when new info turns up. My apologies if I have not been clear, any help would be greatly appreciated

0 Karma
Highlighted

Re: Need to compare fields in a csv

Legend

Based on the details in the question I created a lookup file ignorelist.csv with the following row:

Col1    Col2    ignoreFlag
x      y       Y

PS: Lookup definition ignorelistwas created for updated lookup file ignorelist.csv to be used in following run anywhere example:

The makeresults command along with append is used to mock some dummy events you can replace command before | lookup ... with your base search instead.

The lookup command matches the two fields x and y in the lookup table for Col1 an Col2 respectively against event data for fieldA and fieldB respectively and pulls the ignoreFlag from the lookup file. Finally where filter is applied to fetch only events which do not match fields in the lookup file

|  makeresults
|  eval fieldA="x",fieldB="y"
|  append 
    [|  makeresults
|  eval fieldA="x",fieldB="x"
] 
|  append 
    [|  makeresults
|  eval fieldA="y",fieldB="y"
] 
|  append 
    [|  makeresults
|  eval fieldA="x",fieldB="y"
] 
|  lookup ignorelist Col1 as fieldA Col2 as fieldB output ignoreFlag
|  where isnull(ignoreFlag)

While above query is more of a run anywhere search with the attached lookup file, you can try the following with your actual search:

<YourBaseSearch>
| lookup <yourLookupDefinitionName> Col1 as fieldA Col2 as fieldB output ignoreFlag
|  where isnull(ignoreFlag)



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Need to compare fields in a csv

I probably didn't make myself clear enough. Here is an example. I have a windows event from the security log (for example). In the event is (for example) the user field, the hostname field and the eventtype field i.e Tom S1234 and 4625 (for example). Now if there is a row anywhere in my csv that reads Tom,S1234,4625 then I want to ignore this event. Does that make it clearer?

0 Karma
Highlighted

Re: Need to compare fields in a csv

P.S So that I will end up with a list of events that do not where User, Hostname and Eventtype do not correspond to any rows in the csv.

0 Karma
Highlighted

Re: Need to compare fields in a csv

Legend

Thanks for explaining. You can try @woodcock's approach which he has posted.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Need to compare fields in a csv

Esteemed Legend

Like this:

Your Base Search Here NOT [|inputcsv YourIgnoreFileHere.csv | rename Col1 AS A Col2 AS B | table A B | format]