topic Re: How to count range of dates? in Splunk Search

How to count range of dates?

ositaumeozulu — Wed, 08 Jun 2022 15:43:54 GMT

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)

Re: How to count range of dates?

yuanliu — Wed, 08 Jun 2022 15:48:23 GMT

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.

Re: How to count range of dates?

ositaumeozulu — Thu, 09 Jun 2022 06:45:38 GMT

@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

Re: How to count range of dates?

yuanliu — Thu, 09 Jun 2022 11:16:19 GMT

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

Re: How to count range of dates?

ositaumeozulu — Thu, 09 Jun 2022 18:44:44 GMT

@yuanliu

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

i really appreciate