topic Re: How to use a lookup table field to use to query another lookup table in Splunk Search

How to use a lookup table field to use to query another lookup table

warrick2 — Tue, 03 Feb 2015 20:49:43 GMT

I'm a Splunk beginner, bear with me.... I am querying a system log file of access events. I have two lookup tables defined: Visitors and Areas. Visitors table has a 6 alphanumeric userid and the Areas table has a 3 digit area number. There are 129 userids in the visitors lookup table and 50+ area_numbers in the areas table

Basically I am trying to query an access log on our system to produce a report that tells me if any of the Visitors in the lookup table accessed any of the areas in the areas table, and if so, how many times they accessed any particular area.
I have started with:

index=events sourcetype=PASSAGES 38D999 
[ | inputlookup area.csv | fields area_number ] 
| stats count by area_number

This requires me to run the query with a manually entered visitor ID (38D999). But I can't come up with how to have the query use my Visitor lookup table to get each individual userid and run the query to get the number of accesses each user made in the areas table. Is there a query that can go sequentially through the visitor table, get the first userid, run the query against the areas table and repeat for all 129 visitors?

Sample event:

30-Jan-2015 15:16:43, ARGUS_PASSAGE, NORMAL_PASSAGE, "NORMAL_PASSAGE, Station: 268, Unit Name: DOOR G, Portal: 2999, user 003835: Richard David Warrick, Badge: 2675(JO5), From Area 276, To Area 277, Biometrics: Not Configured", user 003835, badge 169739505, station 999, portal 2999, area 277, area 999, AFP 99, building 9999, badge 168234234

Visitor.csv

Lab ID,Name,Column1,Column12
000360,Smith,Joe E.,Smith,Joe E.

Area.csv

area_number
2
5
89

Re: How to use a lookup table field to use to query another lookup table

aljohnson_splun — Tue, 03 Feb 2015 20:56:47 GMT

Could you do this ?

index=events sourcetype=PASSAGES 
 [ | inputlookup area.csv | fields area_number ] 
 [ | inputlookup visitor.csv | fields visitor_id ] 
 | stats count by area_number, visitor_id

Re: How to use a lookup table field to use to query another lookup table

warrick2 — Tue, 03 Feb 2015 21:22:14 GMT

Ran without any errors... but also ran without any results, which I know there are some of for my visitor population. Thanks for the quick reply! Any other ideas? I did check both of my lookup tables for correct privileges, field names, etc. All looked ok.

Re: How to use a lookup table field to use to query another lookup table

masonmorales — Tue, 03 Feb 2015 21:43:02 GMT

Could you post a sample event, as well as a sample row from each lookup?

Re: How to use a lookup table field to use to query another lookup table

warrick2 — Mon, 28 Sep 2020 18:49:56 GMT

Sure...

Sample event:
30-Jan-2015 15:16:43, ARGUS_PASSAGE, NORMAL_PASSAGE, "NORMAL_PASSAGE, Station: 268, Unit Name: DOOR G, Portal: 2999, user 003835: Richard David Warrick, Badge: 2675(JO5), From Area 276, To Area 277, Biometrics: Not Configured", user 003835, badge 169739505, station 999, portal 2999, area 277, area 999, AFP 99, building 9999, badge 168234234

Visitor.csv
Lab ID,Name,Column1,Column12
000360,Smith,Joe E.,Smith,Joe E.

Area.csv
area_number
2
5
89

Re: How to use a lookup table field to use to query another lookup table

warrick2 — Mon, 28 Sep 2020 18:49:51 GMT

my query for the above lookups was (literally):

Returned 0

Re: How to use a lookup table field to use to query another lookup table

somesoni2 — Tue, 03 Feb 2015 22:14:38 GMT

Do you have your visitor ID field extracted already.

If yes try this @aljohnson_splunk answer should work fine.

If not, then try this

 index=events sourcetype=PASSAGES
[ | inputlookup area.csv | fields area_number ]
[ | inputlookup visitor.csv | fields visitor_id | rename visitor_id as search  | format ]
| stats count by area_number, visitor_id

Re: How to use a lookup table field to use to query another lookup table

aljohnson_splun — Tue, 03 Feb 2015 22:19:39 GMT

You could use the lookup command. Note that visitor.csv needs to be the name of the lookup table file name (it may be different from the name of the file). user_id is whatever the input field for the lookup is.

 index=events sourcetype=PASSAGES 
 [ | inputlookup area.csv | fields area_number ] 
| lookup visitor.csv Lab_ID as user_id
| search Name=*
| stats count by area_number, user_id

by adding Name=*, you'll be filtering to events which the lookup added the name value for (only the people in the visitors.csv)

Re: How to use a lookup table field to use to query another lookup table

masonmorales — Wed, 04 Feb 2015 13:43:55 GMT

I am assuming that the user ID in your sample event (e.g. 003835) is the same as the Lab_ID in Area.csv, and that the "To area" in your event is what you want to match against in Area.csv.

If that's the case, try this:

index=events sourcetype=PASSAGES | rex "user\s(?<userid>\d+)," | rex "To\s\Area\s(?<toarea>\d+)," | inputlookup Visitor.csv append=t | search userid=Lab_ID | inputlookup Area.csv append=t | search toarea=area_number | stats count by area_number, Name, userid

After extracting the userid field, it appends the Visitor.csv file to your search results, then filters it to only events where the "userid" matches the "Lab ID" in your Visitors.csv table. Then, it extracts the "To Area" from your events, appends the Area.csv table, and searches for events where the "To Area" match the "area_number" in Area.csv. Then, it counts how many times each user accessed each area.