Hi,
I have some events, and a User lookup. The Lookup holds the UserID, User Name, a WorkGroup, and dates when the User was in that particular WorkGroup. Users can move from one WorkGroup to another, and hence Users may have more than one record in the User lookup.
What I'd like to produce is a report showing which WorkGroup the user was in at the time the event took place.
My desired output would be along the lines of..
user1 User One 2011-03-15 14:02:15 WorkGroup2
because at the time of the event(2011-03-15 14:02:15), the User was in WorkGroup2.
User Lookup
UserID,Name,ValidStart,ValidEnd,WorkGroup
user1,User One,2010-11-16 00:00:00,2011-01-20 00:00:00,WorkGroup1
user1,User One,2011-01-21 00:00:00,2011-05-23 00:00:00,WorkGroup2
user1,User One,2011-05-24 00:00:00,2011-10-31 00:00:00,WorkGroup1
user2,User Two,2010-01-16 00:00:00,2011-08-23 00:00:00,WorkGroupX
Here's my attempt, but I always get no records returned from the search.
source="mysource"
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS mytime
| lookup UserLookup UserID AS UserID
| where mytime>=ValidStart AND mytime<=ValidEnd
| fields UserID, Name, mytime, WorkGroup
I have verified that the event times do come within the range of Valid Start and End Dates. If I replace the where with a search – no difference.
If I remove the where altogether, and add in ValidStart and ValidEnd, I get all records as evpected.
Any ideas ?
Thanks.
you should look at temporal lookups that do mostly what you want. see the description on how to configure it in the transforms.conf.spec file: http://docs.splunk.com/Documentation/Splunk/latest/Admin/Transformsconf
see the time_field
, time_format
, max_offset_secs
, and min_offset_secs
parameters.
The limitation here is that you can only specify one single time, not a start and stop range. however, if we can assume only the start time, and assume that if there is an entry with a later start time, that one wins, then you can use that as the time field and leave the max and min offsets as default.
you should look at temporal lookups that do mostly what you want. see the description on how to configure it in the transforms.conf.spec file: http://docs.splunk.com/Documentation/Splunk/latest/Admin/Transformsconf
see the time_field
, time_format
, max_offset_secs
, and min_offset_secs
parameters.
The limitation here is that you can only specify one single time, not a start and stop range. however, if we can assume only the start time, and assume that if there is an entry with a later start time, that one wins, then you can use that as the time field and leave the max and min offsets as default.
Shouldn't have had the quotes around the time_format
Thanks for the pointer. I have tried, but always get the first (earliest) record returned. In my example above, that would be WorkGroup1 for 2010-11-16 00:00:00, even though the event time is in April 2011.
transforms.conf
[UserLookup]
default_match = Unknown
filename = UserLookup.csv
min_matches = 1
time_field = ValidStart
time_format = "%Y-%m-%d %H:%M:%S"