topic Re: How can I incorporate the transaction command along with startswith and endswith? in Splunk Search

How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Fri, 03 Nov 2017 19:02:07 GMT

So I saw someone did a query for Linux systems on failed sshd logins followed by a successful sshd login using the transaction command. This would potentially help discover passwords that have been compromised via brute force, guessing, etc. If I have the following search:

index=blah sourcetype=linux_secure process=sshd "tag::action"=success
|rename _time AS Time src AS Source, dest AS Destination, user AS User
|iplocation Source
|table Time, Source, Destination, User, City, Region, Country
|convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

Can anyone tell me how I would incorporate the transaction command along with startswith (failure I'm guessing) and endswith (success)? I've been testing with what I think is the correct syntax and haven't been successful. Thanks for any help.

Re: How can I incorporate the transaction command along with startswith and endswith?

koshyk — Fri, 03 Nov 2017 23:00:40 GMT

So in your main search, you should NOT put success filter, but rather it should be broad search. Please note, the field names I'm not sure for your data, so I've put as arbitary names

index=blah sourcetype=linux_secure process=sshd 
|rename _time AS Time src AS Source, dest AS Destination, user AS User
|iplocation Source
|table Time, Source, Destination, User, City, Region, Country
| transaction process maxspan=30s maxpause=5s startswith="failure" endswith="success"

Below is an example. Assume that two events

11-03-2017 22:49:01.642 +0000 INFO  Metrics - group=queue, name=typingqueue, max_size_kb=500, current_size_kb=0, current_size=0, largest_size=31, smallest_size=0
11-03-2017 22:49:01.642 +0000 INFO  Metrics - group=queue, name=tcpin_queue, max_size_kb=500, current_size_kb=0, current_size=0, largest_size=0, smallest_size=0

Say that we need to identify both of them and then stitch together if the group are the same and which starts with tcpin_queue and ends with typingqueue. The search would be something like.

index=_internal sourcetype=splunkd
| transaction group maxspan=30s maxpause=5s startswith="tcpin_queue" endswith="typingqueue"

You can put maxspan and maxpause to make it more accurate. Reference: https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Transaction

Re: How can I incorporate the transaction command along with startswith and endswith?

niketn — Sun, 05 Nov 2017 15:24:09 GMT

@SplunkLunk, please provide some sample data for both events you want to correlate. Do you have "tag::action"="failure" as well in your data?

Ideally formatting/enrichment of data should happen towards the end of your query. While transaction is ideal for startswith and endswith scenario, similar query with stats might actually perform better based on how far apart are the events within transaction and what is the total search duration

You would also need to define what is the key for creating a transaction, is it a combination or src, dest and user?

index=blah sourcetype=linux_secure process=sshd ("tag::action"="success" OR "tag::action"="success")
| stats count as eventcount min(_time) as _time max(_time) as EndTime list("tag::action") as action by src dest user
| search eventcount>1  action="success" and action="failure"
| eval firstAction=mvindex(action,eventcount-1)
| eval lastAction=mvindex(action,0)
| search firstAction="success" AND lastAction="failure"
| eval duration=EndTime-_time

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 19:20:00 GMT

Thanks. I did not have "tag::action"="failure" in there as I wasn't sure of the syntax. I originally just wanted to know about successful logins. Then I thought about failed followed by success to possibly identify password guessing. So I came here to ask the best way to do that. I'm assuming you mean to add "tag::action"="failure" and not have two "tag::action"="success" in your above search?

My key for creating a transaction I suppose would be user. I'm interested in a user whose account has login failures followed by a successful login. Also, is it possible to query for five+ unsuccessful logins followed by a success? I'm assuming I'd have to use a timespan or run the search every "X" minutes for the last "X" minutes. My span would depend on what I consider an acceptable window to looks at failures?

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 19:36:27 GMT

Adding the transaction command at the end of the search doesn't produce any results for me. It's only when I add it after index=blah sourcetype=linux_secure process=sshd so I see any results.

Re: How can I incorporate the transaction command along with startswith and endswith?

niketn — Mon, 06 Nov 2017 19:38:01 GMT

Thanks @SplunkLunk, I did a typo and meant filter in the base search for actions which are either "success" or "failure"
The reason for key was, if you do not also split by src and dest fields per user you may have multiple src and destination which you should capture based on either list() or values() (whichever is applicable).

 index=blah sourcetype=linux_secure process=sshd ("tag::action"="success" OR "tag::action"="failure")
 | stats count as eventcount min(_time) as _time max(_time) as EndTime list("tag::action") as action list(src) as Source list(dest) as destination user 
 | search eventcount>1  action="success" and action="failure"
 | eval firstAction=mvindex(action,eventcount-1)
 | eval lastAction=mvindex(action,0)
 | search firstAction="success" AND lastAction="failure"
 | eval duration=EndTime-_time

If your use case is five success followed by a failure then your scenario would be quite different.

Option 1: Ideally you would need to use streamstats which requires you to first ensure that your results are sorted ascending or descending order of time.

Option 2: With the approach provided with stats for transaction you can check first action is failure and last action is success and in total there are five or more failure/s and only one success.

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 20:35:28 GMT

Option 2 sounds like that's what I would want. What would that search look like?

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 20:46:26 GMT

Okay, so this search sort of gets at what I'm looking for I think:

index=blah sourcetype=linux_secure process=sshd ("tag::action"="success" OR "tag::action"="failure")
|transaction user, src maxspan=30s maxpause=5s startswith=action="failure" endswith=action="success"
|table _time,user, src, dest, action

However, that shows me one failure followed by a success during that timespan. How would I check for five failed logins followed by success within that timespan (or longer as I would likely push it out)?

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 21:15:01 GMT

Would this work?

index=blah sourcetype=linux_secure process=sshd ("tag::action"="success" OR "tag::action"="failure")
|transaction user, src, dest maxspan=60s maxpause=5s startswith=action="failure" endswith=action="success"
|where eventcount>=6
|table _time,user, src, dest, action

It may generate some false positives if there is a success mixed in the 60s span, but if I scope the span low enough (but still reasonable), there probably wouldn't be too many false positives if someone was trying to brute force. Thoughts?

Re: How can I incorporate the transaction command along with startswith and endswith?

SplunkLunk — Mon, 06 Nov 2017 21:25:00 GMT

When I run the above search, results appear briefly in that stats tab and then disappear. Any idea why?