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.
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.
[| 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]
values(userid) as userid
values(app1) as app1
values(app2) as app2
values(app3) as app3
values(app4) as app4
| 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