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

[jayakanthprasad]

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.

[DalJeanis]

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"

[jayakanthprasad]

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=my_index | rex "\"reportingId\":\"(?[0-9A-Za-z]+)\"" | rex "master-id:(?[0-9a-zA-Z]+)" | where MASTER_ID != "null" | dedup MASTER_ID | table MASTER_ID

The above query has all the unique MASTER_ID'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 - MASTER_ID

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 )

[jayakanthprasad]

Modified the query I used

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

[jayakanthprasad]

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 ?

[woodcock]

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