topic Re: How to compare values of a field in a transaction? in Splunk Search

How to compare values of a field in a transaction?

ZacEsa — Tue, 16 Aug 2016 11:15:29 GMT

Is it possible to compare values in a transaction?

I have a transaction with maxspan of 5 minutes, which group events which happened within 5 minutes of each other.

I want to compare the values of a field inside the transaction, and if the fields are similar, it will create a new value in a new field.

EDIT: I also want to check if the transactions happen between a certain time range, e.g. 8pm to 5am, and if it falls in the time range, create a new value in a new field too.

Re: How to compare values of a field in a transaction?

lguinn2 — Tue, 16 Aug 2016 20:23:39 GMT

Yes, it is possible. However, you haven't given enough information for me to provide detailed information on how to do it. So here is some general information that may help

 yoursearchhere
| transaction whatever here maxspan=5m
| eval transaction_hour=strftime(_time,"%H")
| eval nighttime=if(transaction_hour>=20 OR transaction_hour<=5,"yes","no")
| eval diff=mvindex(yourField,0) - mvindex(yourField,-1)
| eval newField=if(diff<10,"newValue",null())
| eval newField=if(isnull(mvindex(yourField,0)) OR isnull(mvindex(yourField,-1)),null(),newField)

with a line by line explanation:
line 3 - _time represents the start time of the transaction. From _time, extract the hour.
line 4 - if the hour is between 8 pm and 5 am, set the new field "nighttime" to yes, otherwise set it to no
line 5 - for a field named "yourField," calculate the difference between the first event in the transaction and the last event in the transaction
line 6 - if the difference is less than 10, create a new field and set it to "newValue" otherwise, set the field to null
line 7 - also set the newField to null if yourField was null in either the first event or the last event of the transaction

HTH

Re: How to compare values of a field in a transaction?

ZacEsa — Wed, 17 Aug 2016 00:30:37 GMT

Thanks. I believe part of your answer will work but, some parts may not. For example, the field, yourField will not be a numerical value. It will be a string(IP address) I would like to compare the different yourFields in the transaction and if there are more than one, newField will become "Multiple Sources"

EDIT: Is it possible to do the transaction_hour down to half an hour too? Is it like this?

| eval transaction_hour=strftime(_time,"%H%M")
| eval nighttime=if(transaction_hour>=1800 OR transaction_hour<=0830,"yes","no")

Re: How to compare values of a field in a transaction?

sundareshr — Wed, 17 Aug 2016 00:54:08 GMT

If it only 1 field you want to compare, you can do the normal dedup. Like this

yoursearchhere
 | transaction whatever here maxspan=5m
 | eval transaction_hour=strftime(_time,"%H")
 | eval nighttime=if(transaction_hour>=20 OR transaction_hour<=5,"yes","no")
 | streamstats count
 | mvexpand yourField
 | dedup count yourField
 | mvcombine yourField
 | eval newField=if(mvcount(yourField)>1, "Multi Source", "Single Source")

Re: How to compare values of a field in a transaction?

ZacEsa — Wed, 17 Aug 2016 01:21:10 GMT

Unfortunately, it's not only one field.

Re: How to compare values of a field in a transaction?

sundareshr — Wed, 17 Aug 2016 01:46:23 GMT

Try this approach

yoursearchhere
| bin span=5m _time
| eval whatever=_time."#".whatever
| stats span=5m values(yourField1) as yourField1 values(yourField2) as yourField2  by whatever
| eval transaction_hour=strftime(_time,"%H")
| eval nighttime=if(transaction_hour>=20 OR transaction_hour<=5,"yes","no")
| eval newField=if(mvcount(yourField1)>1, "Multi Source", "Single Source")
| rex field="whatever" "(?<_time>[^#]+)#(?<whatever>.*)"
| fields _time whatever yourField1 yourField2 etc

Re: How to compare values of a field in a transaction?

ZacEsa — Wed, 17 Aug 2016 02:45:32 GMT

Thanks! I just needed the

| eval newField=if(mvcount(yourField1)>1, "Multi Source", "Single Source")

Re: How to compare values of a field in a transaction?

sundareshr — Wed, 17 Aug 2016 02:50:27 GMT

Yeah, I missed that. Please accept this answer to close it out.

Re: How to compare values of a field in a transaction?

ZacEsa — Wed, 17 Aug 2016 02:54:43 GMT

But I got part of the answer from yours and part of the answer from the other answer. 😕

Re: How to compare values of a field in a transaction?

sundareshr — Wed, 17 Aug 2016 10:52:53 GMT

@ZacEsa, pick the answer closest to the one that worked and post the final query you used. This will help other users with similar situation find the right answer faster. That's what this community is about.

The two answers here have one basic difference, one uses the transaction (@lguinn) to group events, the other uses bin + stats to group events.

Re: How to compare values of a field in a transaction?

ZacEsa — Thu, 18 Aug 2016 01:52:48 GMT

Here's what I did. I took part of the two answers on this question and came up with the solution.

mysearch
| transaction devicename maxspan=5m
| eval transaction_period=strftime(_time,"%H%M")
| eval nighttime=if(transaction_period>=1800 OR transaction_period<=0830, "After Office Hours", "")
| eval attemptcount=if(eventcount>1, "Multiple Attempts", "")
| eval srccount=if(mvcount(srcip)>1, "Multiple Sources", "")
| eval hits=nighttime . "," . attemptcount . "," . srccount
| eval hits=split(hits, ",")

Explanation;

Line 3 takes the hours and minutes from the time.
Line 4 checks if the 24hour time is between 1800 and 0830. If it is, output will be After Office Hours else, it will be blank.
Line 5 checks if the count within the 5 minutes of transactions is more than 1, if it is, output will be Multiple Attempts else, it will be blank.
Line 6 checks if there are multiple source IPs in the transaction. If there is, output will be Multiple Sources else, it will be blank.
Line 7 combines all the outputs into a single field so that I can show it in one field. If used without Line 8, it will show as After Office Hours,Multiple Attempts, Multiple Sources if all hits are fulfilled.
Line 8 splits by using "," as a delimiter so, the output will be on separate lines as such;

After Office Hours
Multiple Attempts
Multiple Sources

If you are wondering why I'm using "" instead of null(), it's because if I use null(), when I combine the fields, it will show nothing even if only one of the field is null().