Splunk Search

User login

RanjithaN99
Explorer

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

 

Labels (2)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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 😉

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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)

...

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

RanjithaN99
Explorer

Thank you so much. Really helped me! 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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