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.
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)
@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?
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
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.
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 !