topic Re: User login in Splunk Search

User login

RanjithaN99 — Sat, 28 Aug 2021 10:33:20 GMT

Hi ,

A newbie to Splunk here. I have found the query for login info for users on a host:

index=os source=var/log/secure host=myhost process=sshd

I want to trigger an alert if a user who has logged in before, logs in to the host after more than 90 days. Could someone please help me how to write a query .

So the user should not have logged in for more than 90 days on the host.

Thank you

Re: User login

gcusello — Sat, 28 Aug 2021 10:42:27 GMT

Hi @RanjithaN99,

you could run a search like this:

index=os source=var/log/secure host=myhost process=sshd earliest=-180d@d latest=now | transaction user maxevents=2 | eval duration=duration/86400 | where duration>90 OR eventcount=1

Ciao.

Giuseppe

Re: User login

RanjithaN99 — Sat, 28 Aug 2021 11:13:19 GMT

Thank you so much. Really helped me!

Re: User login

PickleRick — Sat, 28 Aug 2021 12:21:00 GMT

Correct me if I'm wrong but this way you'll miss sequences of events like this:

- login at day 2 (transaction starts here)

- login at day 14 (transaction ends here)

- login at day 123 (new transaction ends here)

...

Re: User login

PickleRick — Sat, 28 Aug 2021 20:18:16 GMT

I thought about this one for a while and the elegant solution eluded me (I had some horrible ideas with table/transpose and the running foreach... yuck).

But then I had an epiphany 🤣

I was focusing too much on events in their natural order whereas the key to do it properly is sorting.

Firstly, you do

| sort user + _time

So you have batches of events concerning the same user sorted by time.

Now we need to find a way to get the time of the previous login. Luckily, Splunk has a nice feature called autoregress which copies a value of a field from previous event(s).

So we copy the previous login time (and user to filter out moments in which we switch from one user to another).

| autoregress _time as oldtime p=1 | autoregress user as olduser

Now we have all the data needed to find our culprits

| where user=olduser AND _time-oldtime>90*86400

And voila, you have your logins after long period of inactivity. As a bonus you also have the time of previous login (although it could use some formating 😉

Re: User login

gcusello — Sun, 29 Aug 2021 16:24:46 GMT

Hi @PickleRick,

you spoke of loging with an interval greater than 90 days, you didn't spoke ok start and end transaction.

How is it possible to identify start and end transaction?

Using those strings you can use the same method adding startswith and endswith options on transaction command.

so if in the starting transaction there's the string "start" and in the ending transaction there's the string "end", you could try something like this:

index=os source=var/log/secure host=myhost process=sshd earliest=-180d@d latest=now | transaction user maxevents=2 startswith="start" endswith="end" | eval duration=duration/86400 | where duration>90 OR eventcount=1

for more infos see the command transaction (https://docs.splunk.com/Documentation/Splunk/8.2.2/SearchReference/Transaction).

Ciao.

Giuseppe

Re: User login

PickleRick — Mon, 30 Aug 2021 07:25:55 GMT

I don't know why you assume that there needs to be a transaction.

And you keep misunderstanding the question.

There are no separate types of events to detect.

As I wrote earlier, let's assume you have a sequence of login events:

Day ,User
1,user1
2,user1
13,user2
14,user3
27,user1
40,user1
51,user2
54,user3
72,user2
82,user3
101,user2
110,user3
140,user1

The original poster's question was how to detect events like user1's login at day 140 (since previous login was over 90 ays earlier). Your transaction-based solution won't do:

| makeresults
| eval _raw="Day,User
1,user1
2,user1
13,user2
14,user3
27,user1
40,user1
51,user2
54,user3
72,user2
82,user3
101,user2
110,user3
140,user1" 
| multikv noheader=f 
| fields Day User
| transaction User maxevents=2 
| table Day User

It results with:

Day,User
"1 2",user1
"13 51",user2
"14 54",user3
"27 40",user1
140,user1
"110 82",user3
"101 72",user2

As you can see, we miss the user1's login at day 140 completely.

Transaction won't do because it just measures separate non-overlapping periods whereas we need a sliding window.