Add column with last logon user to the table

[raffal59]

Hi,

I have following table: 

ts	action	file_name	source_ip
2021-10-12T09:34:08.910998Z	File Open	test	10.0.0.14

I would like to add to this table column with the last username who logged from this ip address, using as a latest filter timestamp from this event and as a earliest timestamp - 24h.

Search 1 on the dasboard: 

index=files sourcetype=test_files | search src_ip="$src_ip$" action="$action$" file_name="$name_substr$" | table ts, action,  file_name, src_ip

Search 2, that currently based on two tokens from the first table (ts and src_ip):

index="windows" EventCode=4624 src_ip="$ip$" | eval time="$ts$" | eval ts_u=strptime(time, "%Y-%m-%dT%H:%M:%S.%6NZ") | eval start=relative_time(ts_u,"-24h")  | where _time>$$start$$ AND _time<$$ts_u$$ | stats latest(_time) AS Latest, latest(TargetUserName) AS LastUser | eval LastEvent=strftime(Latest,"%+") | table LastEvent,LastUser

I would like to merge this two searches to one table, but if I use join or append command I can't use the ts/_time and src_ip field values from the first search (result is empty).

Do you have any idea how I can merge events from two independent sources?

Thank you in advance.

[richgalloway]

It would help to know how to tried using the join and append commands.

Did you try something like this?

index=files sourcetype=test_files 
| search src_ip="$src_ip$" action="$action$" file_name="$name_substr$" 
| table ts, action,  file_name, src_ip
| append [ search index="windows" EventCode=4624 src_ip="$ip$" 
  | eval time="$ts$" 
  | eval ts_u=strptime(time, "%Y-%m-%dT%H:%M:%S.%6NZ") 
  | eval start=relative_time(ts_u,"-24h")  
  | where _time>$$start$$ AND _time<$$ts_u$$ 
  | stats latest(_time) AS Latest, latest(TargetUserName) AS LastUser by src_ip
  | eval LastEvent=strftime(Latest,"%+") 
  | table src_ip,LastEvent,LastUser ]
| stats values(*) as * by src_ip
| table ts, action, file_name, src_ip, LastEvent, LastUser

[raffal59]

I tried do this with join command, but there is a problem with using field values (ts) in subsearch with event code.

index=files sourcetype=test_files
| search src_ip="$src_ip$" action="$action$" file_name="$name_substr$"
| join type=inner src_ip 
[ search index="windows" EventCode=4624 src_ip="$src_ip$"
| eval time="$ts$" 
| eval ts_u=strptime(time, "%Y-%m-%dT%H:%M:%S.%6NZ") 
| eval start=relative_time(ts_u,"-24h")
| where _time>$$start$$ AND _time<$$ts_u$$ 
| stats latest(_time) AS Latest, latest(TargetUserName) AS LastUser by src_ip 
| eval LastEvent=strftime(Latest,"%+") 
| table src_ip,LastEvent,LastUser ] 
| table ts, action, file_name, src_ip, LastEvent, LastUser, name 

When I specify example of time in subsearch it works correctly.

index=files sourcetype=test_files
| search src_ip="$src_ip$" action="$action$" file_name="$name_substr$"
| join type=inner src_ip 
[ search index="windows" EventCode=4624 src_ip="$src_ip$"
| eval time="2021-08-12T01:48:10.327248Z" 
| eval ts_u=strptime(time, "%Y-%m-%dT%H:%M:%S.%6NZ") 
| eval start=relative_time(ts_u,"-24h")
| where _time>$$start$$ AND _time<$$ts_u$$ 
| stats latest(_time) AS Latest, latest(TargetUserName) AS LastUser by src_ip 
| eval LastEvent=strftime(Latest,"%+") 
| table src_ip,LastEvent,LastUser ] 
| table ts, action, file_name, src_ip, LastEvent, LastUser, name

Is there any workaround for this issue? 

[richgalloway]

The $ts$ token must exist and must be in a format that matches the values in the time field.

[raffal59]

Hi Rich,

Thank you for your help. Do you mean that there is no possibility to do table like this?

ts

action

file_name

source_ip

Last Logon User

[richgalloway]

Not saying that at all.  It may very well be possible, depending on the data.  The trick will be to get the token value to match what's in the index.  That probably will require an eval command, but I don't know enough about the data to offer specifics.