Splunk Search
Highlighted

How to search a lookup csv file for list of matched events and count ?

New Member

Hi,

I have few queries related to lookup in Splunk.

My lookup file - list-of-master-ids.csv

content of csv file

MASTER_ID (Column)
AA0012A (Row1)
BB1113B (Row2)
CC22232B (Row3)

My Splunk logs has the event related to AA0012A (Row1), Now I need to print the rows count of csv file, Count of unique matched events (Only machine-id), Missing MASTER-ID's from the index, means the id's exist in the csv file and no events related to those id's in the logs.

Please help.

0 Karma
Highlighted

Re: How to search a lookup csv file for list of matched events and count ?

SplunkTrust
SplunkTrust

Try this. change the Master field names to whatever they should be on each path.

your search that gets the splunk logs 
| rename Master_ID_Field as MasterID
| stats by MasterID
| eval typerec="splunk"
| append [| inputcsv list-of-masfter-ids.csv | stats by Master_ID | rename Master_ID as MasterID | eval typerec="lookup"]
| stats values(typerec) as typerec by MasterID
| where mvcount(typerec)=1 AND typerec="lookup"
0 Karma
Highlighted

Re: How to search a lookup csv file for list of matched events and count ?

New Member

Thank you , I will give a try. I was not able to find the use of typerec in splunk documentation. Could you please let me know the use ?

0 Karma
Highlighted

Re: How to search a lookup csv file for list of matched events and count ?

Esteemed Legend

You need a way to distinguish the 2 datasets from oneanother so that you can use the consequential logic to create the proper kind of join (i.e. right-join vs. left-join).

0 Karma
Highlighted

Re: How to search a lookup csv file for list of matched events and count ?

New Member

Hi DalJeanis,

Thanks alot for your response, I tried the query as you suggested but it is not working as expected. Is there any way to do SQL IN or SQL NOT IN against the list of id's we got in splunk logs VS master list without using lookups ?

index=myindex | rex "\"reportingId\":\"(?[0-9A-Za-z]+)\"" | rex "master-id:(?[0-9a-zA-Z]+)" | where MASTERID != "null" | dedup MASTERID | table MASTERID

The above query has all the unique MASTERID's in splunk logs. Now I need to compare these id's against the csv file I have. My csv file has only one column and 2500 rows. Column name in csv file - MASTERID

  1. I need to print the count of total MASTER_ID's in csv file
  2. I need to print the count of unique MASTER_ID's in splunk logs
  3. I need to print the count of missing MASTER_ID's and their ID's too. (At any time, csv file has all the id's where as splunk logs doesn't have all id's , so I need to report which id's from csv list are not having any events in splunk logs )
0 Karma
Highlighted

Re: How to search a lookup csv file for list of matched events and count ?

New Member

Modified the query I used

index=myindex | rex "\"reportingId\":\"(?[0-9A-Za-z]+)\"" | rex "master-id:(?[0-9a-zA-Z]+)" | where MASTERID != "null" | dedup MASTERID | table MASTERID

0 Karma