Combine results from mutliple searches

[cyndiback]

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:

• form_id
• name
• employee name
• supervisor name
• start_date
• end_date
• (duration of time between draft and pending approval)
• (duration of time between pending approval and approve)
• (duration of time between approve and done)

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:

• Sometimes forms skip the Draft state (see form_id =1816)
• Sometimes the form is saved multiple times but the state doesn't change (see form_id=1810). I just search for the first time the form is saved by status

Any guidance would help.

Thanks,

Cyndi

[woodcock]

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