Splunk Search

Splunk search query

mm12
Explorer

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

 

 

 

 

 

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

mm12
Explorer

@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

0 Karma

PickleRick
SplunkTrust
SplunkTrust
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)

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma

mm12
Explorer

@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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @mm12,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma

soumyasaha25
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...