Correlating events between two dates and multiple sources

Path Finder


I have two reports that I want to correlate and find their results:

Report 1:

Access control log - Contains FirstName, LastName, date, time, door names, etc

Report 2:

HR Vacation sheet - Contains FirstName, LastName, start_vacation_date, end_vacation_date

The goal is that if someone is registered as on vacation, that an alert will display if they are detected on the Access Control log.

So for instance, John Doe is on vacation from 26th of Sept 2012 until 29th of Sept 2012, but he is detected swiping in to the main entrance on the 27th of Sept 2012 at 02:00 AM. I want an alert to show that John Doe came in to the office on his day off, indicating his proximity card may have been stolen.

Eventually I want to also correlate this data with VPN event data, so for instance, if John Doe has swiped into the building, and 10 minutes later logs on to the VPN, that we will get an alert from that too. This would help us to determine if someone has compromised John Doe's VPN account.

I can do searches between different times, but it is when those times are defined within a field is what is confusing me.



Tags (3)

Path Finder

Thanks very much Kristian,

I've modified that a bit to match the data and also had to eval some fields (just noticed that in one sheet first and last name are in the same field). So I've done this:

index="main" source="accesscontrol.csv"| join User [search index="businessintelligence" source=vacations.csv | eval User=First_Name." ".Surname | fields + Start_Date, End_Date, User] | eval status = if((Date_time > Start_Date) AND (Date_time < End_Date), "alert", "ok") | table Date_time, User, Door

Not sure how to fit in the strptime function here. The only difference with the dates is that on the access control log there is a timestamp included with the date, and in the vacation log file there is only a start and end date. The formats are the same.

This shows no results when I run it. I am assuming it should return results with a status of "OK" if the access log is not within the range of the vacation start or end dates? Or does it just display nothing if there are no violations?

I've also changed the _time parameter to Date_time - The files are imported manually and then this query is run within the time limits of the last import. The Date_time field is the field name for the timestamp in the access control file. I am assuming _time was the time that Splunk received the log. Is this correct?



0 Karma

Ultra Champion

I suppose that this is where a temporal lookup could be put to use;

where you would use the vacations.csv as the lookup table. Haven't really done any of that myself, but I imagine you could make it work.

An alternate solution might be to index the vacations.csv file, and compare timestamps from the accesscontrol.csv, something along the lines of;

sourcetype=access_control| join userid [search sourcetype=vacations | dedup userid | fields + start_time, end_time, userid] | eval status = if((_time > start_time) AND (_time < end_time), "alert", "ok") | table _time, userid, doorname, status

Please note that you will most likely have to convert the start and end timestamps to epoch with eval's strftime/strptime functions. This will not work out of the box.


Three things (though I cant promise that it will work, since I don't have your data);

a) a 'User' in access_control must have the same format as in vacations. If not, you'll have to eval that as well.

b) _time is what you want (from the access_control), since that is where splunk stores the time in the event in epoch format (number of seconds since midnight on Jan 1 1970 UTC - handy for comparisons).

c) strptime is an eval function that you can use to parse string representations of time in an event, e.g. if expressed as 2012-09-26 13:34:45 in a field called startdate

eval ZZZ = strptime(startdate, "%Y-%m-%d %H:%M:%S")

ZZZ will be given the epoch representation of the string in startdate, which will look something like 139492045. This can then easily be compared to _time, which is in epoch itself.

When printing, you might want to convert it back - e.g. extract the year of an epoch (QQQ)

eval XXX = strftime(QQQ, "%Y")

XXX will contain '2011', '2012' or such.

Have a look at the eval functions or strftime/strptime documentation

NB. when printing (stats, table etc) the _time field, no conversion is needed, it's done automatically.

Hope this helps,


Ultra Champion

see update above /k

0 Karma

Path Finder

I got it - Sleepy moment - my assumption is correct - it shows "OK" if the event does not match. The Access Control log also showed Lastname, Firstname 🙂

Ended up doing this:

index="main" source="accesscontrol.csv"| join User [search index="businessintelligence" source=vacations.csv | eval User=Surname.", ".First_Name | fields + Start_Date, End_Date, User] | eval status = if((Date_time > Start_Date) AND (Date_time < End_Date), "alert", "ok") | table Date_time, User, Door




0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...