Reporting

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

SplunkTrust
SplunkTrust

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!

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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)

View solution in original post

SplunkTrust
SplunkTrust

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)

View solution in original post

SplunkTrust
SplunkTrust

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

0 Karma

Esteemed Legend

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.

0 Karma

SplunkTrust
SplunkTrust

Is it not enough to use latest=@m?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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!

0 Karma

SplunkTrust
SplunkTrust

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!

0 Karma