Splunk Search

Passing dynamic value from stats to the remaining part of search

Communicator

I want to pass latestdate for null value so that inprogress count sits there as there is no completion date for inprogress records. Thats the ask. Here latestdate 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
0 Karma
1 Solution

Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

Communicator

Awesome.. This worked.. 🙂 Thanks a lot

0 Karma

Communicator

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

0 Karma

SplunkTrust
SplunkTrust

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(completeddate) as latestdate 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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

Thanks for your time. I will try this..

0 Karma