Hello!
I'm trying to do a search for our projects (we use Microsoft Project Online and have Splunk pull the data from the Projects table) which are late. I'm defining late as a project that has a due date of yesterday or earlier and I'm having trouble making the search. So my current search is simply:
index=projectonline | dedup ProjectName | search ProjectStatus=Active |WHERE ProjectPercentCompleted !=100 |
but I'm unsure how to add the WHERE ProjectFinishDate [is before today]
. I can clarify further if necessary but any and all help would be most appreciated!
Thank you very much
What is the format of the date in ProjectFinishDate
. What you need to do is first convert it to epoch time and use that to compare with today(). Try something like this
index=projectonline | dedup ProjectName | search ProjectStatus=Active |WHERE ProjectPercentCompleted !=100 | eval ProjectFinishDate_Epoch=strptime(ProjectFinishDate, "%m/%d/%Y %H:%M%:%S") | where ProjectFinishDate_Epoch<relative_time(now(), "@d")
What is the format of the date in ProjectFinishDate
. What you need to do is first convert it to epoch time and use that to compare with today(). Try something like this
index=projectonline | dedup ProjectName | search ProjectStatus=Active |WHERE ProjectPercentCompleted !=100 | eval ProjectFinishDate_Epoch=strptime(ProjectFinishDate, "%m/%d/%Y %H:%M%:%S") | where ProjectFinishDate_Epoch<relative_time(now(), "@d")
Thank you for your response!
The ProjectFinishDate format is M/D/Y H:M
(no seconds) - does that effect how I convert it? Would it look more like:
...eval ProjectFinishDate_Epoch=strptime(ProjectFinishDate, "%m/%d/%Y %H:%M") | where ProjectFinishDate_Epoch < relative_time(now(), "@d")
That seems to make sense, however, no results are produced. Weird.