topic Re: Splunk search query in Splunk Search

Splunk search query

mm12 — Thu, 11 Nov 2021 15:23:28 GMT

Hi All,

I need splunk query to identify orders which are ordered but not submitted even after 72 hours

Any one help me on this

Thanks

Re: Splunk search query

soumyasaha25 — Thu, 11 Nov 2021 15:30:44 GMT

check this page if it helps.

If not, could you please share some sample data.

Please ensure to mock/anonymize any sensitive information in your data/code before posting on Splunk community.

Re: Splunk search query

gcusello — Thu, 11 Nov 2021 15:37:45 GMT

Hi @mm12,

your request is just a little vague...

Anyway supposing that you have in one index events with an Order_Id field and an Order_Status field (with values Ordered and Submitted), you could run something like this:

index=orders (Status="Ordered" OR Status="Submitted") | stats dc(Status) AS dc_status values(Status) AS Status earliest(_time) AS earliest latest(_time) AS latest BY Order_Id | eval Order_Status=if(dc_status=1 AND Status="Ordered","Not Submitted",if(latest-earliest>259200,"Submitted Late","Submitted"), Order_Date=strftime(earliest,"%Y-%m-%d %H:%M:%S"), Submission_Date=dc_status=1 AND Status="Ordered","Not Submitted",strftime(latest,"%Y-%m-%d %H:%M:%S")) | table Order_Id Order_Status Order_Date Submission_Date

Ciao.

Giuseppe

Re: Splunk search query

mm12 — Fri, 12 Nov 2021 09:15:30 GMT

@gcusello thanks for the reply. But I have the below search query and I want to achieve the below use case.

I want to know what batches were ordered in the selected time period, and have EITHER not been submitted at all, OR were submitted more than 48 hours after being ordered.

Thanks

Re: Splunk search query

gcusello — Fri, 12 Nov 2021 13:05:43 GMT

Hi @mm12,

your search is correct but uses subsearches that have the limit of 50,000 results and uses more resources; so please, try this:

index= sfg_objects (eventtype=Object_Ordered OR eventtype="Object_Submitted") | stats dc(eventtype) AS dc_eventtypes values(eventtype) AS eventtype BY Job_name | where dc_eventtypes=1 and eventtype=Object_Ordered

Ciao.

Giuseppe

Re: Splunk search query

mm12 — Fri, 12 Nov 2021 16:18:54 GMT

@gcusello In my query it checks for the orders that were not submitted at all. But I should check for the orders that were not submitted only after 48 hours giving 2 days time to process the orders.

Thanks

Re: Splunk search query

gcusello — Sat, 13 Nov 2021 07:11:55 GMT

Hi @mm12,

please see again my first solution:

index=orders (Status="Ordered" OR Status="Submitted") | stats dc(Status) AS dc_status values(Status) AS Status earliest(_time) AS earliest latest(_time) AS latest BY Order_Id | eval Order_Status=if(dc_status=1 AND Status="Ordered","Ordered But Not Submitted",if(dc_status=2 AND (latest-earliest>172800),"Ordered But Submitted Late","Ordered and Submitted"), Order_Date=strftime(earliest,"%Y-%m-%d %H:%M:%S"), Submission_Date=dc_status=1 AND Status="Ordered","Not Submitted",strftime(latest,"%Y-%m-%d %H:%M:%S")) | table Order_Id Order_Status Order_Date Submission_Date

Using it, you have:

The Order_Id,
the Status of your orders:
- Ordered and Submitted (if you have both Ordered and Submitted),
- Ordered but Not Submitted (if you have Ordered but Not Submitted),
- Ordered but late Submitted if you have both Ordered and Submitted but the difference between the dates is more than 48 hours;
the Order Date,
the Submission Date.

If you want to highligh only one of these Statuses you can modify my complete search or filter results.

Ciao.

Giuseppe

Re: Splunk search query

PickleRick — Sat, 13 Nov 2021 08:40:14 GMT

index=sfg_objects eventtype=Object_Ordered OR eventtype=Object_Submitted
| stats values(eventtype) as types max(_time) as maxtime min(_time) as mintime by Job_name
| where NOT (eventtype="Object_Submitted" OR maxtime-mintime<48*3600)

Re: Splunk search query

gcusello — Thu, 18 Nov 2021 16:05:47 GMT

Hi @mm12,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉