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
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.
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)
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
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
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
Could you post a sample event, as well as a sample row from each lookup?
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.
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