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!
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
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
each app may be multivalues?
no - that was heldover from a different query. I'm all set thanks to @richgalloway