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.
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 😉
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
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)
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).
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:
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:
| eval _raw="Day,User
| multikv noheader=f
| fields Day User
| transaction User maxevents=2
| table Day User
It results with:
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.
Thank you so much. Really helped me!