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!

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...