I have two reports that I want to correlate and find their results:
Access control log - Contains FirstName, LastName, date, time, door names, etc
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.
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?
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
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.
_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).
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
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
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.
NB. when printing (
table etc) the
_time field, no conversion is needed, it's done automatically.
Hope this helps,
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