How to edit my search to track transactions that start yesterday that continue up to the time the report starts the next day?

[_gkollias]

I have a search that allows us to track any invoice that did not get sent out for the previous day to be reported the following day.

Then, if there are some invoices from the previous day that are in some failed state, we report those the following morning at 7AM EST.

Now, what we are finding is that sometimes there are batches out there that are in a processing/queued state around 11PM EST, so once 12AM comes, those batches are considered to be failed or stuck out there. In some cases they actually are not stuck, but they just get processed successfully at 1AM the following day, for example.

I'm looking for a way to enhance my search to get past this scenario. How can I look at yesterday's data, and use anything I find for yesterday to track through to the next day up until the report kicks off at 7AM EST? Here is a good sample of the search I am using for this:

index=contract_gateway sourcetype=esb_audit earliest=-1d@d latest=@d bp_bp_name=Invoice* NOT TPCode=810 NOT status=ACCEPTED NOT status=START
| rex field=msg_stuff "[^|]*\|(?<transaction_format>[^|]*)\|.*"
| stats count as event_count,
    earliest(_time) AS start_time,
    latest(_time) as end_time,
    latest(bp_bp_name) as bp_bp_name,
    latest(Amount) as Amount,
    latest(TPCode) as TPCode,
    values(transaction_format) as transaction_format,
    latest(status) as exit_status,
    latest(other) as exit_message,
    values(SourceSystem) as SourceSystemValues,
    latest(bp_context_id) as bp_context_id,
by InvoiceNumber
| where (like(SourceSystemValues, "%ESB%") AND NOT like(SourceSystemValues, "%CTG%") AND NOT like(exit_status, "%SUCCESS%"))

Thanks in advance!

[somesoni2 · Accepted solution]

As per my understanding of your requirement is that you want to look at data from -1d@d to @m and exclude the Invoices started after 12:00 AM. To do this, try something like this

index=contract_gateway sourcetype=esb_audit earliest=-1d@d latest=@m bp_bp_name=Invoice* NOT TPCode=810 NOT status=ACCEPTED NOT status=START
 | rex field=msg_stuff "[^|]*\|(?<transaction_format>[^|]*)\|.*"
 | stats count as event_count,
     earliest(_time) AS start_time,
     latest(_time) as end_time,
     latest(bp_bp_name) as bp_bp_name,
     latest(Amount) as Amount,
     latest(TPCode) as TPCode,
     values(transaction_format) as transaction_format,
  latest(status) as exit_status,
     latest(other) as exit_message,
     values(SourceSystem) as SourceSystemValues,
     latest(bp_context_id) as bp_context_id,
 by InvoiceNumber
 | where start_time<relative_time(now(),"@d") AND  (like(SourceSystemValues, "%ESB%") AND NOT like(SourceSystemValues, "%CTG%") AND NOT like(exit_status, "%SUCCESS%"))

Changes:
Latest time for search updated to @m
Add condition to where clause to exclude any Invoice which started after 12:00 AM (today as per search execution schedule of 7:00 AM)

[_gkollias]

Thank you! I am still testing this, but this seems to be what I am looking for.

[woodcock]

Spell out a successful example with sample data and a problematic example with sample data and describe the logic that you are trying to apply to compensate.

[richgalloway]

Is it not enough to use latest=@m?

[_gkollias]

I'm trying not to pick up any invoices on the next day/ between 12AM and 7AM of report kick off. Using latest=@m will include all invoices for the previous day AND next day up to when the report is generated. Basically I only want to use what is found for yesterday, and at 12AM still watch for those but not anything else the next day. This is to ensure we are able to process everything that is found yesterday leading up to when the report is kicked off. Hope this helps!

[_gkollias]

Also, I was thinking of summarizing the results of this search for all invoices for the previous day in to its own summary index, and then searching on that for the 12AM yesterday to 7AM today period to find out what has really failed for yesterday. That is plausible, however I'm looking for any insights or feedback on if there is a way to modify the initial query rather than summarizing the data. Thanks!