topic Re: How to count dates of a field that are over now() and Values with no entry? in Splunk Enterprise

How to count dates of a field that are over now() and Values with no entry?

UMDTERPS — Mon, 22 Jun 2020 20:58:18 GMT

I have a CSV that looks like the following:

Organization	System	Scan Due Date
ABC	Jack	7-Feb-21
ABC	Jill	9-May-20
123	Bob	Unspecified
123	Alice	Unspecified
456	James	10-Jan-21

How do I do a count of the " Scan Due Date Field" that shows all of the events that are Overdue, Not Expired, and Unspecified? ( I want to eventually want to put the results of that search into a pie chart.

Any help is appreciated!

Re: How to count dates of a field that are over now() and Values with no entry?

richgalloway — Fri, 19 Jun 2020 21:05:50 GMT

How do you define "Overdue" and "Not Expired"?

Re: How to count dates of a field that are over now() and Values with no entry?

to4kawa — Sat, 20 Jun 2020 00:11:10 GMT

sample:

| makeresults | eval _raw="Organization,System,Scan_Due_Date ABC,Jack,1418459200 ABC,Jill,1593547277 123,Bob,Unspecified 123,Alicez,Unspecified 456,James,1625083277" | multikv forceheader=1 | table Organization,System,Scan_Due_Date | rename COMMENT as "the logic" | eval status=case(Scan_Due_Date >= now(),"Not Expired",Scan_Due_Date="Unspecified",Scan_Due_Date,true(),"Overdue") | stats count by status

try Visualization >> Pie Chart

Re: How to count dates of a field that are over now() and Values with no entry?

UMDTERPS — Mon, 22 Jun 2020 16:39:37 GMT

Overdue (Expired) means the "Scan Due Date" is a past date.
Not-Expired means the "Scan Due Date" is a date sometime in the future.

Re: How to count dates of a field that are over now() and Values with no entry?

UMDTERPS — Mon, 22 Jun 2020 19:22:43 GMT

I put a strptime in and got it partially working:

| inputlookup scan_due_date.csv | eval date = strptime('Scan Date', "%d-%b-%y") | eval status=case(date >= now(),"Not Expired",date="Unspecified",date true(),"Overdue") | stats count by status

However, it's not counting the "Unspecified" field values.

Any ideas?

Re: How to count dates of a field that are over now() and Values with no entry?

UMDTERPS — Thu, 25 Jun 2020 18:59:57 GMT

| inputlookup scan_due_date.csv | eval date = strptime('Scan Date', "%d-%b-%Y") | eval duedate = if(isnull(date) OR date="", "Unspecified", date) | eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue") | fields date duedate status | stats count by status

Okay, figured it out. I had to convert the date using strptime using a capital "%Y" (Capital "%Y" is needed to convert "2020", lowercase "%y" would only work with "20"). Because striptime fills in "Unspecified" with blanks/null values, I had to do an eval "if" statement to fill in the blanks strptime created with "Unspecified" to bring it back.