Splunk Search

Lookup Update Table Unique Rows

Path Finder

Hi Experts,

I'm trying to build a lookup table that will update based on the latest time a user logged into a particular application. Ideally, it has a few tracking columns at the end to track last updated and first added. Something like the table that follows. Date added would be the date the user had first logged into any application. Not all users use all applications. Lastupdate should always equal the latest date in one of the application columns. The table below should hopefully self-explain the desired outcome.

UserID,app1,app2,app3,app4,DateAdded,LatestLogin
Jdoe,05/06/20,,,03/04/20,02/02/20,05/06/20
Ksmith,,04/20/20,,,01/15/20,04/20/20
Jfrank,,,03/03/20,,03/03/20,03/03/20

Each user would only appear once, and we would only update where they had an application login. We've been able to successfully append the lookup using a combination of input and outputlookup but unable to modify a specific row.

We created the following to build it (And added an "inlist" column that says "True" based on other examples) but are struggling to 'update' once built.

| multisearch
    [| search index=app1 status=success userid=*
    | rename _time as app1]

    [| search index=app2 status=success userid=*
    | rename _time as app2]

    [| search index=app3 status=success userid=*
    | rename _time as app3]

    [| search index=app4 status=success userid=*
    | rename _time as app4]

    | stats
    values(userid) as userid
    values(app1) as app1
    values(app2) as app2
    values(app3) as app3
    values(app4) as app4
    by userid
    | convert timeformat="%m/%d/%Y" ctime(app1) ctime(app2) ctime(app3) ctime(app4)
    | fillnull value=true inlist
    | table userid inlist app1 app2 app3 app4
    | outputlookup appaccess.csv

Thank you in advance, as always.

Finally, Happy Mothers Day!

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Do your searches, then read in the lookup file in append mode. Remove duplicates (using stats in this example). Write the results to the lookup file.

| multisearch 
...
| inputlookup append=true appaccess.csv
| stats latest(app1) as app1, latest(app2) as app2, latest(app3) as app3, latest(app4) as app4 by userid
| eval lastLogin = max(app1, app2, app3, app4)
| convert ...
| table userid, app1, app2, app3, app4, lastLogin
| outputlookup appaccess.csv
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Do your searches, then read in the lookup file in append mode. Remove duplicates (using stats in this example). Write the results to the lookup file.

| multisearch 
...
| inputlookup append=true appaccess.csv
| stats latest(app1) as app1, latest(app2) as app2, latest(app3) as app3, latest(app4) as app4 by userid
| eval lastLogin = max(app1, app2, app3, app4)
| convert ...
| table userid, app1, app2, app3, app4, lastLogin
| outputlookup appaccess.csv
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

each app may be multivalues?

0 Karma

Path Finder

no - that was heldover from a different query. I'm all set thanks to @richgalloway

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!