Splunk Search

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

warrick2
New Member

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
Tags (2)
0 Karma

masonmorales
Influencer

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.

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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)


0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

warrick2
New Member

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

0 Karma

warrick2
New Member

my query for the above lookups was (literally):

earliest=10/01/2013:0:0:0 latest=2/2/2015:0:0:0 class=ARGUS_PASSAGE

[ | inputlookup area.csv | fields area_number ]
[ | inputlookup visitor.csv | fields Lab_ID ]

| stats count by area_number, Lab_ID_id

Returned 0

0 Karma

masonmorales
Influencer

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

0 Karma

warrick2
New Member

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.

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...