Splunk Enterprise

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

Communicator

I have a CSV that looks like the following: 

OrganizationSystem Scan Due  Date 
ABCJack7-Feb-21 
ABCJill9-May-20 
123BobUnspecified 
123AliceUnspecified 
456James10-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! 

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

Ultra Champion

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

View solution in original post

0 Karma

Ultra Champion

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

View solution in original post

0 Karma

Communicator

 

 

 

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

Communicator

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?

0 Karma

SplunkTrust
SplunkTrust
How do you define "Overdue" and "Not Expired"?
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Communicator

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. 

0 Karma