Alerting

Compare two searches

felipesodre
Path Finder

Hi everybody, I hope someone can help me out. I appreciate any further comments.

 

I have two searches that I have to compare but they have different formats:

First search:

index="schedule" "uploaded to S3, number of rows:"
| rex "File (?<table_name>.*?csv)"
| eval table_name=substr(table_name, 0, len(table_name) - 28)
| eval rows = replace(Success,"number of rows:","")
| eval rows = substr(rows, 1, len(rows)-1)
| eval trans_date=strftime(_time, "%m-%d-%y")
| search trans_date=10-14-20
| stats sum(rows) as rows by warehouse, table_name, trans_date
| table table_name, trans_date, rows

felipesodre_3-1603190694286.png

 

Second search:

index=schedule TransactionPurgingSummary AND "\"newPurging\":true" | rex "TransactionPurgingSummary: (?<TransactionPurgingSummary>.*?})"
| spath input=TransactionPurgingSummary
| eval trans_date=strftime(_time, "%m-%d-%y")
| search objectsPurgedSummary.transaction0 > 0
| stats sum(objectsPurgedSummary.transaction0) as b_transaction0, sum(objectsPurgedSummary.auxiliarymessage) as b_auxiliarymessage, sum(objectsPurgedSummary.transactionmessage) as b_transactionmessage, sum(objectsPurgedSummary.transactionmessagevalue) as b_transactionmessagevalue, sum(objectsPurgedSummary.trans_transactionmessages) as b_trans_transactionmessages, sum(objectsPurgedSummary.labelling_transaction_pfd_link_tb) as b_labelling_transaction_pfd_link_tb, sum(objectsPurgedSummary.labelling_transaction_alert_link_tb) as b_labelling_transaction_alert_link_tb by warehouse, trans_date
| table trans_date, b_transaction0, b_auxiliarymessage, b_transactionmessage, b_transactionmessagevalue, b_trans_transactionmessages, b_labelling_transaction_pfd_link_tb, b_labelling_transaction_alert_link_tb

felipesodre_1-1603190384006.png

 

I am aim creating an alert that compares the first query table_name and rows to the second query the value in the correspondent table name by tarns_date eg:

 transaction0 rows = b_transaction0

 

 

 

 

 

Labels (1)
Tags (2)
0 Karma
1 Solution

felipesodre
Path Finder

The untable was the key for what I wanted. I just did a few adptations.

 

I really appreciate your help here is my final solution:

index=schedule TransactionPurgingSummary AND "\"newPurging\":true" | rex "TransactionPurgingSummary: (?<TransactionPurgingSummary>.*?})"
| spath input=TransactionPurgingSummary
| eval trans_date=strftime(_time, "%m-%d-%y")
| search objectsPurgedSummary.transaction0 > 0
| stats sum(objectsPurgedSummary.transaction0) as transaction0, sum(objectsPurgedSummary.auxiliarymessage) as auxiliarymessage, sum(objectsPurgedSummary.transactionmessage) as b_transactionmessage, sum(objectsPurgedSummary.transactionmessagevalue) as transactionmessagevalue, sum(objectsPurgedSummary.trans_transactionmessages) as trans_transactionmessages, sum(objectsPurgedSummary.labelling_transaction_pfd_link_tb) as labelling_transaction_pfd_link_tb, sum(objectsPurgedSummary.labelling_transaction_alert_link_tb) as labelling_transaction_alert_link_tb by warehouse, trans_date
| table trans_date, transaction0, auxiliarymessage, transactionmessage, transactionmessagevalue, trans_transactionmessages, labelling_transaction_pfd_link_tb, b_labelling_transaction_alert_link_tb
| untable trans_date table_name b_rows
| join warehouse, table_name, trans_date
[search index="schedule" "uploaded to S3, number of rows:"
| rex "File (?<table_name>.*?csv)"
| eval table_name=substr(table_name, 0, len(table_name) - 28)
| eval rows = replace(Success,"number of rows:","")
| eval rows = substr(rows, 1, len(rows)-1)
| eval trans_date=strftime(_time, "%m-%d-%y")
| search trans_date=10-14-20
| stats sum(rows) as rows by warehouse, table_name, trans_date
| table table_name, trans_date, rows]

View solution in original post

0 Karma

to4kawa
Ultra Champion

first_search 
| append [ search second_search
| rename b_* as *
| untable trans_date table_name rows]
| stats values(*) as * by trans_date


I don't know how to make the comparison.

  .... | eval rows=-1 * rows]
  | stats sum(*) as * by trans_date
  | where rows > 0

like that?

felipesodre
Path Finder

The untable was the key for what I wanted. I just did a few adptations.

 

I really appreciate your help here is my final solution:

index=schedule TransactionPurgingSummary AND "\"newPurging\":true" | rex "TransactionPurgingSummary: (?<TransactionPurgingSummary>.*?})"
| spath input=TransactionPurgingSummary
| eval trans_date=strftime(_time, "%m-%d-%y")
| search objectsPurgedSummary.transaction0 > 0
| stats sum(objectsPurgedSummary.transaction0) as transaction0, sum(objectsPurgedSummary.auxiliarymessage) as auxiliarymessage, sum(objectsPurgedSummary.transactionmessage) as b_transactionmessage, sum(objectsPurgedSummary.transactionmessagevalue) as transactionmessagevalue, sum(objectsPurgedSummary.trans_transactionmessages) as trans_transactionmessages, sum(objectsPurgedSummary.labelling_transaction_pfd_link_tb) as labelling_transaction_pfd_link_tb, sum(objectsPurgedSummary.labelling_transaction_alert_link_tb) as labelling_transaction_alert_link_tb by warehouse, trans_date
| table trans_date, transaction0, auxiliarymessage, transactionmessage, transactionmessagevalue, trans_transactionmessages, labelling_transaction_pfd_link_tb, b_labelling_transaction_alert_link_tb
| untable trans_date table_name b_rows
| join warehouse, table_name, trans_date
[search index="schedule" "uploaded to S3, number of rows:"
| rex "File (?<table_name>.*?csv)"
| eval table_name=substr(table_name, 0, len(table_name) - 28)
| eval rows = replace(Success,"number of rows:","")
| eval rows = substr(rows, 1, len(rows)-1)
| eval trans_date=strftime(_time, "%m-%d-%y")
| search trans_date=10-14-20
| stats sum(rows) as rows by warehouse, table_name, trans_date
| table table_name, trans_date, rows]

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...