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 Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...