topic Re: Writing a join query to extract usernames from sessionID in Splunk Search

Writing a join query to extract usernames from sessionID

arsalanj — Wed, 30 Sep 2020 04:32:09 GMT

Hi there,

I need help writing a query that finds the username of whoever ran a command on A Linux server.
For example, if you look at the log below:
<86>Mar 5 18:41:44 server1 useradd[2569]: new user: name=test1, UID=1100, GID=5020, home=/home/test1, shell=/bin/bash

Someone with the session ID=2569 added a new user "test1".
If I run another query like this: "pid=2569 eventtype=ssh_open", I can see to whom that session belongs.
<86>Jan 24 18:34:03 test1 sshd[2569]: pam_unix(sshd:session): session opened for user admin by (uid=0)

I was trying to write a query like this, but I keep hitting the wall :

|multisearch
[search index="linux_secure"
eventtype=useradd
| stats values(pid) AS pid1]

[search index="linux_secure" eventtype=ssh_open
| stats values(pid) AS pid2]

| where pid1=pid2

The query above is not correct, and it returns errors like subsearch 1 contains a non-streaming command.

I want to write something that checks for the identical pid and extracts the username from search2 and the action from search 1.

Any help would be appreciated.

Thanks,
Arsalan

Re: Writing a join query to extract usernames from sessionID

to4kawa — Tue, 10 Mar 2020 01:08:38 GMT

UPDATE2:

index="linux_secure" (eventtype=useradd OR eventtype=ssh_open)
| rex "new user: name=(?<adduser>[^,]+)"
| stats dc(eventtype) as flag values(adduser) as adduser values(admin) as admin by pid
| where flag >1 and isnotnull(adduser)

I see, this query wii display user name who use useradd command and admin.
I'm not sure your field extraction, I keep rex.

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 03:15:18 GMT

Thank you @to4kawa,

But this query will return all the users who also login but did not run the useradd command.

I'm only looking to find users who ran that command.

Some users have logged in to the systems a long time ago, and they are running commands, the only way for me to map them is by using their pid.

Re: Writing a join query to extract usernames from sessionID

woodcock — Tue, 10 Mar 2020 03:48:58 GMT

This assumes that the one type uses user and the other uses name; if not, you will have to fix that part; otherwise this:

index="linux_secure" AND eventtype="useradd"
| eval earliest = _time - (10 * 60)
| map search="search index=\"linux_secure\" AND eventtype=\"ssh_open\" earliest=$earliest$ latest=$_time$ pid=$pid$ | eval name=$name$, addtime=$_time$ | table _time addtime pid name user"

Or:

index="linux_secure" (eventtype="useradd" OR eventtype="ssh_open")
| stats dc(eventtype) AS eventtype_count values(name) AS name values(user) AS user BY pid
| where eventtype_count >1

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 18:28:13 GMT

Thanks Again.
I did a field extraction before, so we don't need this part: | rex "new user: name=(?[^,]+)"
So, with this query now I'm getting the pid and the user that has been added. But I don't get the username of the user who logged in in ssh_open event and ran that command.

This is what I'm getting:
pid adduser flag
2569 test1 2

I want to extract the admin field from ssh_open, then we can now to whom that pid belongs.

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 18:49:05 GMT

Thank you @woodcock for the above queries.
Query number two, return pid, eventtype_count, name ( which is the user that has been added), and user (which is also has the same value as name or it's null).

I really like query number 1, but for some reason, I can't get it to work.
first, it complained about the latest time, I added the latest time but then it did not return anything.

Re: Writing a join query to extract usernames from sessionID

woodcock — Tue, 10 Mar 2020 19:03:55 GMT

I updated my answer; does it work now?

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 21:09:34 GMT

Unfortunately no. The search runs without any errors, but it's not returning anything.

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 22:40:18 GMT

@woodcock I wanted to thank you again for helping me with this.
I was reviewing the logs and realized that linux_secure logs are not the best option to do this.
I will consider doing this by using Linux Audit logs.
Thanks.

Re: Writing a join query to extract usernames from sessionID

arsalanj — Tue, 10 Mar 2020 22:42:35 GMT

Thanks again! I decided to do this in a different way by using Linux Audit logs.
I'll either update this page or create a new question if I run into problems.

Thanks,