Lookup Update Table Unique Rows

[antb]

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!

[richgalloway · Accepted solution]

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

[to4kawa]

each app may be multivalues?

[antb]

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