I have a tabled data set like:
ID Assessment Name Workflow Name Phase Name Process Name Step Name Step Owner Status Step Start Date Projected Start Date Step Date Completed Projected Completion Date Step Due Date Days Past Due SLA Step Order
KgHaubhnZWgvTSiWc Electrical Contractor Services - SIG Lite 2018 SIG ASSESSMENT ASSESS SIG REVIEW SIG Finalized Bob Smith Not Started PlaceHolder 2018-02-26 16:11:04.139000 114 5 4
KgHaubhnZWgvTSiWc Electrical Contractor Services - SIG Lite 2018 SIG ASSESSMENT ASSESS SIG REVIEW Preliminary Findings Call Bob Smith Not Started PlaceHolder 2018-02-19 16:11:04.139000 114 5 3
KgHaubhnZWgvTSiWc Electrical Contractor Services - SIG Lite 2018 SIG ASSESSMENT ASSESS SIG REVIEW SIG Reviewed by Assessor Bob Smith Completed 2018-02-05 14:54:48.132000 2018-02-05 14:54:48.132000 2018-02-05 14:54:48.132000 2018-02-10 14:54:48.132000 2018-02-12 16:11:04.139000 114 5 2
KgHaubhnZWgvTSiWc Electrical Contractor Services - SIG Lite 2018 SIG ASSESSMENT ASSESS SIG REVIEW SIG Received from Vendor Bob Smith Completed 1/3/2018 00:00:00.000000 1/3/2018 00:00:00.000000 2018-02-05 14:54:33.923000 2018-02-05 14:54:33.923000 2018-02-05 16:11:04.139000 109 10 1
KgHaubhnZWgvTSiWc Electrical Contractor Services - SIG Lite 2018 SIG ASSESSMENT ASSESS SIG REVIEW SIG Sent to Vendor Bob Smith Completed 1/3/2018 0:00 1/3/2018 0:00 1/3/2018 0:00 1/3/2018 0:00 2018-01-22 16:11:04.139000 116 3 0
What I am trying to do is, where Status == "Not Started", use the "Step Date Completed" of the previous record as the "Projected Start Date" of the current record, steps order is defined by the field "Step Order". Currently "Projected Start Date" is created by:
base search ... | eval Status=case('Step Start Date' == "" AND 'Step Date Completed' == "", "Not Started", 'Step Start Date' != "" AND 'Step Date Completed' == "", "Started", 'Step Start Date' != "" AND 'Step Date Completed' != "", "Completed" )
| eval "Projected Start Date"=if(Status == "Not Started", "PlaceHolder", 'Step Start Date')
I just don't know how to get "PlaceHolder" to do the above. Can anyone help?
Thanks a ton!
You could also use streamstats to set the last value of a field which gets passed on to the next event. Then you could use the value of that field if Status == "Not Started". Here is an example of using streamstats in this way.
sourcetype="apache:access" action=*
| streamstats current=f window=1 global=f last(action) as lastaction
| where action != lastaction
base search ... | eval Status=case('Step Start Date' == "" AND 'Step Date Completed' == "", "Not Started", 'Step Start Date' != "" AND 'Step Date Completed' == "", "Started", 'Step Start Date' != "" AND 'Step Date Completed' != "", "Completed" )| autoregress "Step Date Completed" as prev_step_date_completed | eval "Projected Start Date"=if(Status == "Not Started", prev_step_date_completed , 'Step Start Date')
autoregress - http://docs.splunk.com/Documentation/Splunk/7.1.1/SearchReference/Autoregress