Joining Incident and SLA table creates multiple rows

[DonBaldini]

I have a join where there are 2 different SLAs (Active and E2E) that need to be linked to incidents on one row. How can I follow the below up to do that? All the fields in table show twice except for dv_sla which shows 1 each of the SLAs. Both SLAs contribute to different measures that I need to follow up with.

index=servicenow sourcetype=incident
| fields sys_id, number, closed_at, dv_state, dv_u_technical_service, dv_problem_id, proactive, dv_parent_incident
| join type=inner number max=0 [ | search index=servicenow sourcetype="task_sla" dv_sla="Active*" OR dv_sla="E2E*" | fields sys_id, dv_task, dv_sla, dv_stage, dv_has_breached, business_duration | rename dv_task as number, dv_state as task_state ]
| stats latest(*) as * by sys_id
| search dv_stage="Completed" AND proactive="false"

| table number, dv_sla, closed_at, dv_state, dv_u_technical_service, dv_problem_id, proactive, dv_parent_incident

Thanks

[tscroggins]

@DonBaldini 

Do you need to transpose the Active and E2E task_sla columns into a single table row joined with the incident columns? If yes, you can achieve the desired result using stats and a time range large enough to include the most recent events for both the incident and task_sla tables:

index=servicenow (sourcetype=incident OR (sourcetype=task_sla dv_sla IN (Active E2E)))
| eval number=coalesce(number, dv_task)
| stats latest(closed_at) as closed_at latest(dv_parent_incident) as dv_parent_incident latest(dv_problem_id) as dv_problem_id latest(dv_state) as dv_state latest(dv_u_technical_service) as dv_u_technical_service latest(proactive) as proactive latest(eval(case(like(dv_sla, "Active"), business_duration))) as Active_business_duration latest(eval(case(like(dv_sla, "Active"), dv_has_breached))) as Active_dv_has_breached latest(eval(case(like(dv_sla, "Active"), dv_stage))) as Active_dv_stage latest(eval(case(like(dv_sla, "E2E"), business_duration))) as E2E_business_duration latest(eval(case(like(dv_sla, "E2E"), dv_has_breached))) as E2E_dv_has_breached latest(eval(case(like(dv_sla, "E2E"), dv_stage))) as E2E_dv_stage by number

In this example, the task_sla.dv_sla = 'Active' columns are displayed in the Active_* fields, and the task_sla .dv_sla = 'E2E' columns are displayed in the E2E_* fields, grouped by incident.number and task_sla.dv_task aliased to number.

I used like() in the stats evals to accommodate % wildcards, but using wildcards here implies you have e.g. Active1, Active2, E2E1, E2E2, etc. If you only have two task_sla labels, you can replace like(field, "value") with field=="value".