topic Re: Help with transaction ! in Splunk Search

Help with transaction !

dmenon84 — Wed, 05 Apr 2017 19:44:08 GMT

Hi ,

I have following query written but it is not giving me correct output. So my logs would look like this

subject action score x s
Hello continue 40 234 585
Hello discard 80 234 585

My query
index=myindex (action=discard OR action=continue ) | transaction x s keepevicted=true startswith=eval(action="continue") endswith=eval(action="discard") | search subject=* | stats values(action) AS action,dc(action) AS actioncount by subject | where actioncount=2

It gives me info but its usually the once that are discarded first and continued later. I am trying to get info for other way round.

So anything that scores above 80 have action=discard so I want to get alerted on all subjects that had score below 80 and had action=continue but later score went above 80 and now action=discard. The logs are split in several line hence a transaction of 'x' and 's' is required to combine the logs.

Thanks in advance for any help !

Re: Help with transaction !

cmerriman — Wed, 05 Apr 2017 19:59:40 GMT

try editing the startswith/endswith and add an mvlist to keep the events in stats in the right order.

index=myindex (action=discard OR action=continue ) | transaction x s keepevicted=true startswith=eval(match(action,"continue")) endswith=eval(match(action,"discard")) mvlist=t | search subject=* | stats list(action) AS action, dc(action) AS actioncount by subject | where actioncount=2

or try this:

index=myindex (action=discard OR action=continue )|sort 0 x s + _time |streamstats window=1 current=f values(action) as previousAction by x s| stats values(action) as action values(previousAction) as previousAction values(x) as x values(s) as s by subject |search action="discard" previousAction="continue"

Re: Help with transaction !

niketn — Wed, 05 Apr 2017 20:12:20 GMT

@dmenon84... few things to take care

1) Stats is more suitable for your use case compared to transaction
2) Filter results upfront so add subject=*, action and score criteria for discard and continue in your base search
3) Subject should also be part of your aggregation clause

index=myindex  subject=* (action=discard AND scores>80) OR (action=continue AND score<80)
| stats count as eventcount list(action) as action list(score) as score dc(action) as actioncount by x, s,subject

Try this and let us know how it goes.

Re: Help with transaction !

dmenon84 — Wed, 10 May 2017 14:58:19 GMT

Thanks for helping . Sorry for delay in response, I got caught up in other projects. So this query works some what but I am not getting the scores. This is actually due to the info I provided. Here is how logs look. The action and score are not in same log event so will it still work without transaction.

May 10 08:39:40 s=200 m=1 x=200-1 mod=mail cmd=env_from from=emailaddress to=emailaddressto host=hostname ip=externalsenderip
May 10 08:39:41 s=200 m=1 x=200-1 mod=spam cmd=run score=74
May 10 08:39:41 s=200 m=1 x=200-1 mod=mail cmd=msg rule=pass action=continue attachments=0 rcpts=1 hops-ip=ipaddress subject="My email subject"

Re: Help with transaction !

DalJeanis — Wed, 10 May 2017 15:33:14 GMT

Yes, aggregate clauses in splunk (like stats values(x)) do not require every event to have field x, it just counts, aggregates, averages, or lists the values that are there, for all records that have the same values in the fields in the by clause.

Since subject is only in one record, you'll want to move it out of the by clause in with the aggregate clauses.

| stats count as eventcount, list(action) as action, list(subject) as subject, list(score) as score dc(action) as actioncount by x, s

Re: Help with transaction !

dmenon84 — Wed, 10 May 2017 19:18:38 GMT

Thanks that worked well. I just added a where clause at end to see only the ones that were continued

 | stats count as eventcount, list(action) as action, list(subject) as subject, list(score) as score dc(action) as actioncount by x, s | where action="continue"

Now I am trying to group by subject , can you please guide on that as well. I want to group by subject and want to see only columns
subject action score

Re: Help with transaction !

niketn — Thu, 11 May 2017 10:25:38 GMT

Once correlated you should have only one value for action, subject and score for each combination of x and s (which are composite key in this case)

   | stats count as eventcount, values(action) as action, values(subject) as subject, values(score) as score dc(action) as actioncount by x, s 
   | search action="continue"
   | table subject action score

Re: Help with transaction !

dmenon84 — Thu, 11 May 2017 14:36:53 GMT

I have some cases where subject is same but x,s and score are different. It would be nice to be able to group by subject , sender etc . I have done this using transaction but with stats I am not having much luck.

2a9a93527r 2a9a93527r-1 4 continue RE: more space 43 1
2a9a9352m4 2a9a9352m4-1 4 continue RE: more space 45 1