Splunk Search

Filtering lookups based on dates

JovanMilosevic
Path Finder

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.

Tags (1)
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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.

JovanMilosevic
Path Finder

Shouldn't have had the quotes around the time_format

0 Karma

JovanMilosevic
Path Finder

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"

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...