All Apps and Add-ons

How to update lookup based on search results?

Thulasinathan_M
Communicator

Hi,

I'm trying to maintain a users very first login using lookup and the scheduler runs twice in a day. The userLogin field is a combination of username, userId and uniqueId associated to each Loginuser. I just want the username and userId from userLogin field to maintain single record of each user but to know the exact logged in user records I've to display the userLogin field as well and have to maintain the user's earliest login dateTime.

Currently i'm using csv lookup and has records of past three months in the lookup file but in future if I expand for past 6 months I've to update the earliest login dateTime for the existing user from lookup and append new user details with their login dateTime. I'm bit worried about the performance if my records goes higher in the lookup file.

Here's the query i've managed to write, but I'm struggling to track the earliest dateTime. Any suggestions would be highly welcomed. Thanks in advance.

 

index=user_login_details
rex field=userLogin "(?<userName>\s+\d{5}).*"
| dedup userName
| eval Time=strftime(_time,"%Y-%m-%dT%H:%M:%S")
| table userName, userLogin, Time
| inputlookup user_details.csv append=true
| dedup userName
| outputlookup user_details.csv append=true

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Thulasinathan_M,

please try something like this:

index=user_login_details
| rex field=userLogin "(?<userName>\s+\d{5}).*"
| append [| inputlookup user_details.csv | eval _time=strptime(Time,"%Y-%m-%dT%H:%M:%S")| fields userName userLogin _time ]
| stats values(userLogin) AS userLogin earliest(_time) AS Time BY userName 
| outputlookup user_details.csv

in this way you take all the values from the index and the lookup and for each userName you take the first Time, at the end you override your lookup with all the values.

Ciao.

Giuseppe

View solution in original post

Thulasinathan_M
Communicator

Yes, used the same which you've advised but ended up in such situation. I know it's a rare case scenario but I would be very  happy if it works for such scenario as well.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Thulasinathan_M,

please try something like this:

index=user_login_details
| rex field=userLogin "(?<userName>\s+\d{5}).*"
| append [| inputlookup user_details.csv | eval _time=strptime(Time,"%Y-%m-%dT%H:%M:%S")| fields userName userLogin _time ]
| stats values(userLogin) AS userLogin earliest(_time) AS Time BY userName 
| outputlookup user_details.csv

in this way you take all the values from the index and the lookup and for each userName you take the first Time, at the end you override your lookup with all the values.

Ciao.

Giuseppe

Thulasinathan_M
Communicator

Hi, 

Thank you very much for the quick assistance, that done the trick but as we're not appending the results at worst case if the job doesn't complete this will lead to removing the existing results from the lookup file.

0 Karma

gcusello
SplunkTrust
SplunkTrust

HI @Thulasinathan_M,

even if te main search has no results, it takes the values from the lookup so you should, at least have the lookup values.

Ciao.

Giuseppe

0 Karma

Thulasinathan_M
Communicator

Thanks, but I faced a situation where my splunk stuck during searching and overwritten the csv file from 100+ records to 3.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Thulasinathan_M,

how it's possible that the search has less results of the lookup? you appended to the main search the content of the lookup.

Which is the search you're using?

Ciao.

Giuseppe

0 Karma

Thulasinathan_M
Communicator

Hi @gcusello ,

You've used eval _time to convert the time to machine readable format, but it's missing to update in a human readable format before adding it to outputlook is it fine. Sorry, if the question sounds silly.

 

0 Karma

Thulasinathan_M
Communicator

I've used the same you've suggested but tried to find userLogin for 3 months and it took long time to complete the search, after a long wait I've stopped the job and ended up in such situation where it overwritten my results in lookup.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...