I am trying to create a master report from logs tracking a webform moving through a process. I have information coming in from two different sources
source_1 - has basic webform information:
form_id, name, employee name, supervisor name, start_date, end_date
1816, Server migration, Employee1, Supervisor2, 1/7/2012 15:00, 1/8/2012 1:00
1810, P2V - server, Employee1, Supervisor2, 1/13/2012 23:00, 1/14/2012 2:00
1811, Other rando work to be completed, Employee2, Supervisor3, 1/7/2012 0:00, 1/7/2012 0:10
source_2 - has the process state everytime the form is saved:
form_id, form_changed_by, changed_date, process_state
1816, Employee1, 1/7/2012 23:56, Done
1816, Supervisor2, 1/5/2012 17:33, Approve
1816, Employee1, 1/5/2012 17:19, Pending Approval
1811, Employee2, 1/6/2012 15:00, Done
1811, Supervisor3, 1/5/2012 13:32, Approve
1811, Employee2, 1/5/2012 12:30, Pending Approval
1811, Employee2, 1/5/2012 12:30, Draft
1810, Supervisor2, 1/6/2012 16:11, Approve
1810, Employee1, 1/6/2012 16:10, Pending Approval
1810, Employee1, 1/5/2012 8:40, Draft
1810, Employee1, 1/5/2012 8:35, Draft
The webform moves through the following states:
- Draft -> Pending Approval -> Approve -> Done
I'd like to create a master report that has the following fields:
I use the following searches which gather the information but I am unclear out to combine everything.
Gather basic form information:
source=source_1 | form_id, name, employee name, supervisor name, start_date, end_date
Gather process status history (search for the first time the form is saved with a new status):
source=source_2 | stats last(changed_date) as c_date last(changed_by) as c_by by form_id, status
Calculate duration (I do this in separate searches):
source=source_2 | transaction form_id startswith="Draft" endswith="Pending Approval" maxpause=-1 maxspan=-1 | eval pretty_time=tostring(duration, "duration")
source=source_2 | transaction form_id startswith="Pending Approval" endswith="Approve" maxpause=-1 maxspan=-1 | eval pretty_time=tostring(duration, "duration")
source=source_2 | transaction form_id startswith="Approve" endswith="Done" maxpause=-1 maxspan=-1 | eval pretty_time=tostring(duration, "duration")
Some of the gotchas:
Any guidance would help.
Thanks,
Cyndi
This should do it all in 1 search and not care about any missing states:
source="source_1" OR source="source_2" | reverse | dedup form_id status source | streamstats current=f last(_time) AS prevTime by form_id source | eventstats first( form_id) AS form_id first(name) AS name first(employee name) AS employee_name first(supervisor_name) AS supervisor_name first(start_date) AS start_date first(end_date) AS end_date BY form_id | where source="source_2" | eval state_wait=if(isnull(prevTime),0,(_time-prevTime)) | fields form_id, name, employee_name, supervisor_name, form_changed_by, changed_date, process_state, state_wait, start_date, end_date