Splunk search query

[mm12]

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

[mm12]

@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.

index= sfg_objects eventtype=Object_Ordered host=* NOT
[| search index= sfg_objects eventtype="Object_Submitted" host=*
| dedup Job_name
| fields Job_name]
| stats count by orders | sort 10 -count

Thanks

[PickleRick]

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)

[gcusello]

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

[mm12]

@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

[gcusello · Accepted solution]

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

[gcusello]

Hi @mm12,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

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

[soumyasaha25]

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.