Compare two searches

[felipesodre]

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:

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

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

[to4kawa]

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 · Accepted solution]

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]