How to update lookup based on search results?

[Thulasinathan_M]

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

[Thulasinathan_M]

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.

[gcusello · Accepted solution]

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]

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.

[gcusello]

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

[Thulasinathan_M]

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

[gcusello]

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

[Thulasinathan_M]

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.

[Thulasinathan_M]

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.