Splunk Search

Comparing different fields from different rows

ozirus
Path Finder

Hi,

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

alt text

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,

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Oops.. try the updated answer.

ozirus
Path Finder

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?

0 Karma

inventsekar
Ultra Champion

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

0 Karma

ozirus
Path Finder

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

0 Karma

ozirus
Path Finder

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

0 Karma

sundareshr
Legend

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
0 Karma

ozirus
Path Finder

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

0 Karma

inventsekar
Ultra Champion

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
0 Karma

ozirus
Path Finder

They are not string. They are fields with values

FieldName = Value

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...