Splunk Search
Highlighted

How can I use the value of previous records field as new field value?

Communicator

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!

0 Karma
Highlighted

Re: How can I use the value of previous records field as new field value?

Influencer
 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

0 Karma
Highlighted

Re: How can I use the value of previous records field as new field value?

Splunk Employee
Splunk Employee

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
0 Karma