Splunk Search

Writing a join query to extract usernames from sessionID

arsalanj
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

arsalanj
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

I updated my answer; does it work now?

0 Karma

arsalanj
Path Finder

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

0 Karma

arsalanj
Path Finder

@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.

0 Karma

to4kawa
Ultra Champion

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.

0 Karma

arsalanj
Path Finder

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.

0 Karma

arsalanj
Path Finder

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.

0 Karma

arsalanj
Path Finder

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,

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...