topic Re: Comparing different fields from different rows in Splunk Search

Comparing different fields from different rows

ozirus — Tue, 29 Sep 2020 10:53:30 GMT

Hi,

I've a search query that returns 2 events with two different fields;

EXTRA_FIELD_3 = XXXXXX
GUNCELSAYI = YYYYYY

I want to compare if EXTRA_FIELD_3 > GUNCELSAYI*2 and raise an alert.

How can I do this?

Regards,

Re: Comparing different fields from different rows

inventsekar — Wed, 07 Sep 2016 13:34:00 GMT

please check if rex is extracting the fields properly.. then we can find the difference between two and create an alert -

 your search | rex field=_raw "EXTRA_FIELD_3\s\=\s(?<EXTRA_FIELD_VALUE>\d+[^\n]) | rex field=_raw "GUNCELSAYI\s\=\s (?<GUNCELSAYI_VALUE>\d+[^\n]) | table EXTRA_FIELD_VALUE, GUNCELSAYI_VALUE

Re: Comparing different fields from different rows

sundareshr — Wed, 07 Sep 2016 13:39:16 GMT

Try this

your base search | streamstats window=1 current=f values(GUNCELSAYI)  as GUNCELSAYI | where isnotnull(EXTRA_FIELD_3)  AND EXTRA_FIELD_3 > GUNCELSAYI*2

Re: Comparing different fields from different rows

ozirus — Tue, 29 Sep 2020 10:53:32 GMT

My query is;

When I add "| rex field=_raw "EXTRA_FIELD_3\s=\s(?\d+[^\n]) | rex field=_raw "GUNCELSAYI\s=\s (?\d+[^\n]) | table EXTRA_FIELD_VALUE, GUNCELSAYI_VALUE" to this;

this gives

Error in 'SearchParser': Missing a search command before '^'.

Any idea?

Re: Comparing different fields from different rows

inventsekar — Tue, 29 Sep 2020 10:50:33 GMT

sorry, updated the missing double quotes -

rex field=_raw "EXTRA_FIELD_3\s=\s(?\d+[^\n])" | rex field=_raw "GUNCELSAYI\s=\s (?\d+[^\n])" | table EXTRA_FIELD_VALUE, GUNCELSAYI_VALUE

Re: Comparing different fields from different rows

ozirus — Wed, 07 Sep 2016 13:56:22 GMT

It didn't return any result while I try both > and < in last compare statement Empty.

Re: Comparing different fields from different rows

ozirus — Tue, 29 Sep 2020 10:53:35 GMT

Error in 'rex' command: Encountered the following error while compiling the regex 'EXTRA_FIELD_3\s=\s(?\d+[^\n])': Regex: unrecognized character after (? or (?-

Re: Comparing different fields from different rows

ozirus — Tue, 29 Sep 2020 10:53:38 GMT

BTW, EXTRA_FIELD_3 and GUNCELSAYI are both fields and values after = is field values. They're not single string like "GUNCELSAYI=2"

Re: Comparing different fields from different rows

ozirus — Wed, 07 Sep 2016 14:17:41 GMT

They are not string. They are fields with values

FieldName = Value

Re: Comparing different fields from different rows

somesoni2 — Wed, 07 Sep 2016 14:18:19 GMT

Try like this (using appendcols function will add the search result of 2nd query as column available in same events and it would be easy to compare)
Updated

host="AVGMAILCOUNT" date_wday = [|stats count | eval A=strftime(now(),"%A") | return $A] | addinfo | eval saat=strftime(info_search_time,"%H") | eval dakka=strftime(info_search_time,"%M") | eval sonuc=if( date_hour == saat AND date_minute == dakka, "Evet","Hayır") | search sonuc=Evet | stats values(*) as * | appendcols [search index=websense earliest=-10m | stats count as GUNCELSAY ] | where EXTRA_FIELD_3 > GUNCELSAYI*2

This should give you results when your condition is fulfilled. You can save this as alert.

Re: Comparing different fields from different rows

ozirus — Wed, 07 Sep 2016 14:29:34 GMT

Error in 'appendcols' command: You can only use appendcols after a reporting command (such as stats, chart, or timechart).

Re: Comparing different fields from different rows

somesoni2 — Wed, 07 Sep 2016 14:31:29 GMT

Oops.. try the updated answer.