Splunk Search

How to use two fields from my lookup table as search parameters?

New Member

Can someone help me with a query? I have an index which contains user login data having the date format yyyy-mm-dd hh:mm:ss.

Ex: index table has entries like uid, time (yyyy-mm-dd hh:mm:ss+00:00 indicating GMT), location

jdoe1 2016-12-01 18:01:02+00:00 loc1
jdoe1 2016-12-01 19:01:02+00:00 loc2
jdoe1 2016-12-02 18:01:02+00:00 loc3
jdoe2 2016-12-03 18:02:02+00:00 loc4
jdoe3 2016-12-02 17:01:02+00:00 loc5

And my lookup.csv has 2 columns uid & time. Ex as follows

jdoe1 2016-12-01
jdoe3 2016-12-02

I want to know the user's login activity on a specified date (not concerned about the time of the day for search). Any help would be greatly appreciated.

0 Karma

SplunkTrust
SplunkTrust

Give this a try
Updated

your current search giving fields uid, time, location 
| eval date=substr(time,10) 
| lookup lookup.csv uid date OUTPUT date as checkfield
| where isnotnull(checkfield)

Path Finder

@somesoni2 here we use OUTPUT to add a new field to the search, but how we can remove a row from lookuptable based on our search?

0 Karma

New Member

Many thanks somesoni2. I get an error "Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table."

Please note the columns in the index table are uid, time, location & the columns in lookup csv are uid, date

This is the query I wrote

index=login eval date=substr(time,10) | lookup lookup.csv uid time as date OUTPUT time as checkfield
| where isnotnull(checkfield) | table uid, time, location

0 Karma

SplunkTrust
SplunkTrust

In your question you mentioned that the lookup has field uid and time, so the query was made accordingly. If the lookup fields are uid and date, try the updated answer.

0 Karma

New Member

Someone2, many thanks! Now the query runs, but fetches 0 events ... though I see there are many matching the lookup

Here is the query I am running

index=login eval date=substr(time,10)
| lookup lookup.csv uid date OUTPUT date as checkfield
| where isnotnull(checkfield)
| table uid, time, location

Please note the columns in the index table are uid, time, location & the columns in lookup csv are uid, date.

Many thanks in advance !

0 Karma