Splunk Search

Joining Incident and SLA table creates multiple rows

DonBaldini
Explorer

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

Labels (1)
0 Karma

tscroggins
Influencer

@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".

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...