topic Re: Filtering lookups based on dates in Splunk Search

Filtering lookups based on dates

JovanMilosevic — Mon, 17 Oct 2011 16:57:53 GMT

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.

Re: Filtering lookups based on dates

gkanapathy — Mon, 17 Oct 2011 20:16:40 GMT

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.

Re: Filtering lookups based on dates

JovanMilosevic — Tue, 18 Oct 2011 11:36:50 GMT

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"

Re: Filtering lookups based on dates

JovanMilosevic — Tue, 18 Oct 2011 18:45:18 GMT

Shouldn't have had the quotes around the time_format