Splunk Search

How to count range of dates?

ositaumeozulu
Explorer

hello team

please i need solution to these question

i have three column fields, startDate,endDate, ARTstartDate. i want to count the number of ARTstartDate that falls inbetween the two dates (i.e from startDate to endDate)

 

Labels (1)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

Before you run stats, try observe actual data fields including extracted fields.  You would notice that no events were left after "where".  In this case, instead of using strptime() directly in where command, eval them into variables.  You'll see that the strptime() doesn't give you any value.


| where strptime('10/1/2021', "%m/%d/%Y") < ASD AND ASD < strptime('5/27/2022', "%m/%d/%Y")

That's because SPL uses double quotes to delimit literal strings; single quote is used to delimit field names.  If you change to

| where strptime("10/1/2021", "%m/%d/%Y") < ASD AND ASD < strptime("5/27/2022", "%m/%d/%Y")

there should be some events. (If your data satisfies the condition, of course.)

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

Assuming all fields use the format '2022-06-08' (%F, or %Y-%m-%d), it can be as simple as

| eval nARTStartDate = strptime(ARTstartDate, "%F")
| where strptime(startDate, "%F") < nARTstartDate AND nARTstartDate < strptime(endDate, "%F")

then perform a count.

0 Karma

ositaumeozulu
Explorer

@yuanliu 

| eval ASD = strptime(ARTStartDate, "%m/%d/%Y")
| where strptime('10/1/2021', "%m/%d/%Y") < ASD AND ASD < strptime('5/27/2022', "%m/%d/%Y")
| stats count(ASD) as "Tx_new" by FacilityName

i used the above code as directed, still no result, is there a way i can eval the where clause as a separate field so i can run a count on it

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Before you run stats, try observe actual data fields including extracted fields.  You would notice that no events were left after "where".  In this case, instead of using strptime() directly in where command, eval them into variables.  You'll see that the strptime() doesn't give you any value.


| where strptime('10/1/2021', "%m/%d/%Y") < ASD AND ASD < strptime('5/27/2022', "%m/%d/%Y")

That's because SPL uses double quotes to delimit literal strings; single quote is used to delimit field names.  If you change to

| where strptime("10/1/2021", "%m/%d/%Y") < ASD AND ASD < strptime("5/27/2022", "%m/%d/%Y")

there should be some events. (If your data satisfies the condition, of course.)

ositaumeozulu
Explorer

@yuanliu 

wow, just wow, many thanks dear. code is working perfectly

i really appreciate 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...