topic Re: Add column with last logon user to the table in Splunk Search

Add column with last logon user to the table

raffal59 — Mon, 18 Oct 2021 12:07:51 GMT

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):

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.

Re: Add column with last logon user to the table

richgalloway — Mon, 18 Oct 2021 13:14:14 GMT

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

Re: Add column with last logon user to the table

raffal59 — Mon, 18 Oct 2021 15:02:22 GMT

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?

Re: Add column with last logon user to the table

richgalloway — Mon, 18 Oct 2021 15:58:34 GMT

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

Re: Add column with last logon user to the table

raffal59 — Tue, 19 Oct 2021 14:34:25 GMT

Hi Rich,

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

action

file_name

source_ip

Last Logon User

Re: Add column with last logon user to the table

richgalloway — Tue, 19 Oct 2021 19:40:14 GMT

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.