I want to pass latest_date for null value so that inprogress count sits there as there is no completion date for inprogress records. Thats the ask. Here latest_date is not passed as token. Im just getting string latest date. is this doable? can some one help?
index="test" IG="*" |rename "Completion Date" as completed_date "Savings in k" as Dollar_Savings|eventstats last(completed_date) as latest_date|fillnull value=latest_date completed_date | stats count(eval(('Assessment Status'="Closed") OR ('Assessment Status'="Completed"))) as "# of Assessments completed (Projects)" sum(Dollar_Savings) as "Cost Savings Committed (K $)" sum("FTE impact") as FTE_impact sum("Baseline FTE") as baseline_FTE count(eval('Assessment Status'="In Progress")) as "# of Assessments In-progress" by completed_date
I believe what you want to do is replace the fillnull
portion with the following:
...| eval completed_date=if(isnull(completed_date), latest_date, completed_date) | ...
The value X in fillnull value=X
is always treated as a string. In your case, the string "latest_value".
I believe what you want to do is replace the fillnull
portion with the following:
...| eval completed_date=if(isnull(completed_date), latest_date, completed_date) | ...
The value X in fillnull value=X
is always treated as a string. In your case, the string "latest_value".
Awesome.. This worked.. 🙂 Thanks a lot
I could do this by passing it as token from different query. Just want to know if it can be done in a single query instead of calling index twice
You can use the map command to pass on completed_date from base search to new search within same SPL.
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Map
I however, feel eventstats is not required if you have your subsequent stats command return single result (since there is only one last(completed_date) as latest_date used as stats split by field. You even don't need the rename command and fillnull.
index="test" IG="*"
| stats count(eval(('Assessment Status'="Closed") OR ('Assessment Status'="Completed"))) as "# of Assessments completed (Projects)" sum(Dollar_Savings) as "Cost Savings Committed (K $)" sum("FTE impact") as FTE_impact sum("Baseline FTE") as baseline_FTE count(eval('Assessment Status'="In Progress")) as "# of Assessments In-progress" last("Completion Date") as latest_date
| map search="| makeresults
| eval DateSelected=\"$latest_date$\""
You need to replace map SPL query with your own. Make sure all double quotes in inner query are escaped with backslash and use $latest_date$ value as token from the base search.
Please try out and confirm.
Thanks for your time. I will try this..