Splunk Search

Lookup Update Table Unique Rows

antb
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

richgalloway
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, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

to4kawa
Ultra Champion

each app may be multivalues?

0 Karma

antb
Path Finder

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

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