topic Compare two searches in Alerting

Compare two searches

felipesodre — Tue, 20 Oct 2020 10:48:59 GMT

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

Second search:

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

Re: Compare two searches

to4kawa — Tue, 20 Oct 2020 11:06:32 GMT

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?

Re: Compare two searches

felipesodre — Tue, 20 Oct 2020 12:35:40 GMT

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]