Splunk Search
Highlighted

How do I compare multiple fields between two CSV files?

Communicator

Hi. I'm attempting to compare an inventory master list (lookup file) to a weekly inventory list (lookup file) displayed by Splunk to see if an unauthorized item has been added OR removed in the weekly list.

Example:
masterlookup.csv

device              hostname                  ip                      domain
router              router1234               111.11.11.111          usr.hey.spk
workstation         workstation1234          222.22.22.222          usr.hey.spk
printer             printer1234              123.12.12.123          usr.hey.spk
switch              switch1234               444.44.44.444          usr.hey.spk

week2lookup.csv

device         hostname         ip                 domain
router         router4321       211.11.11.111      usr.hey.spk
workstation    workstation1234  222.22.22.222      usr.hey.spk
printer        printer5678      999.99.99.999      usr.hey.spk

The two queries should search three fields (hostname, ip, domain) and return the router and printer device because their hostname and ip values do not match what is listed in the masterlookup.csv. I intend to have a panel display a table of what is added (the new router and printer in week2lookup.csv) and another to display what was removed (the router, printer, and switch in masterlookup.csv).

0 Karma
Highlighted

Re: How do I compare multiple fields between two CSV files?

Communicator

Maybe this solution is not efficient but is the first one I thought

| inputlookup masterlookup.csv 
| eval time="old"
| append 
    [ | inputlookup week2lookup.csv 
    | eval time="new"]
| stats count values(time) as when by hostname, device, ip, domain
| search count=1 when=old

Changing the field's value in the last search you can have added or removed.

Highlighted

Re: How do I compare multiple fields between two CSV files?

Communicator

This works!

0 Karma
Highlighted

Re: How do I compare multiple fields between two CSV files?

Communicator

@elpred0 Post this again as a new comment so that I can accept it as the answer.

0 Karma
Highlighted

Re: How do I compare multiple fields between two CSV files?

Communicator

Done! Thank you @russell120 !!

0 Karma
Highlighted

Re: How do I compare multiple fields between two CSV files?

Communicator

Maybe this solution is not efficient but is the first one I thought

 | inputlookup masterlookup.csv 
 | eval time="old"
 | append 
     [ | inputlookup week2lookup.csv 
     | eval time="new"]
 | stats count values(time) as when by hostname, device, ip, domain
 | search count=1 when=old

Changing the field's value in the last search you can have added or removed.

View solution in original post

0 Karma