Comparing different fields from different rows

[ozirus]

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,

[somesoni2 · Accepted solution]

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.

[ozirus]

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

[somesoni2]

Oops.. try the updated answer.

[ozirus]

My query is;

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 | append [search index=websense earliest=-10m | stats count as GUNCELSAYI]

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?

[inventsekar]

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

[ozirus]

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

[ozirus]

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

[sundareshr]

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

[ozirus]

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

[inventsekar]

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

[ozirus]

They are not string. They are fields with values

FieldName = Value