How to compare the same element with two values ​​and two states?

[Rajaion]

Hello community,

I'm having a very specific problem and I can't find a solution after several days of attempts, all of which failed.
I will explain the situation to you: we have a Splunk OnCall which serves as our hypervisor and which reports the incidents of several of our monitoring tools. Our users acknowledge alerts directly to Splunk OnCall for incident support.
We then pull all this data into Splunk Enterprise (via an official plugin).

For several weeks, I have been trying to make the delta between the number of alerts of a type (based on its title) and the number of times this alert has been acknowledged. For this, OnCall sends the same information back to Enterprise several times but with different details:
- when an alert appears on OnCall, Enterprise has the info with the status "UNACKED".
- when an alert is acknowledged, it goes up with the status "ACKED"
- when an alert is over, it goes up with the status "RESOLVED".

So I can have up to 3 times the same information in Enterprise.

Now that the (long) scene is set, here is my problem: I manage to output the RESOLVED and ACKED alerts in the same table, in order to make a delta between the number of RESOLVED and the number of ACKED but I cannot "align" the information. I use this search :

index=oncall_prod routingKey=*
| search currentPhase=RESOLVED 
| dedup incidentNumber
| rename entityDisplayName as Service
| stats count by Service
| appendcols [ search index=oncall_prod routingKey=*
| search currentPhase=ACKED
| dedup incidentNumber 
| rename entityDisplayName as Service_ACKED
| stats count by Service_ACKED
| rename count AS ACKED]
| eval matchfield=coalesce(Service,Service_ACKED)
| table Service count Service_ACKED ACKED

and the result is the following:

On the screen, you can see my problem: for some alerts, there has never been an acknowledgment and suddenly, there is a shift on the lines. And when I do a delta by a simple calculation, it does it row by row so the values ​​don't mean anything because it's not comparing the right things.

I tried several methods, found here and there on the forum, to properly align my table, including the following search:

index=oncall_prod routingKey=*
| search currentPhase=RESOLVED 
| dedup incidentNumber
| rename entityDisplayName as Service
| stats count by Service
| eval matchfield=Service
| join matchfield [ search index=oncall_prod routingKey=*
| search currentPhase=ACKED
| dedup incidentNumber 
| rename entityDisplayName as Service_ACKED
| stats count by Service_ACKED
| rename count AS ACKED
| eval matchfield=Service_ACKED]
| table Service count Service_ACKED ACKED

but I can't because the result shows me ONLY the lines with both a RESOLVED and ACKED status, leaving the alerts that only had the RESOLVED status undisplayed:

How to make the acknowledgments face the correct RESOLVED lines? And how to leave the rows without acknowledgments empty, with a value of 0?

If you have an idea, I'm interested.

Best regards,

Rajaion

[yuanliu · Accepted solution]

This is indication that addcols and join are not the right tools for your goal, which you didn't clearly state.  I speculate that you want to know among RESOLVED incidents, how many got ACKED, and vice versa.

Try a simpler stats.

index=oncall_prod routingKey=* currentPhase IN (ACKED, RESOLVED)
| rename entityDisplayName as Service
| stats dc(incidentNumber) as count_by_currentPhase_Service by Service currentPhase ``` dedup is expensive so replace with dc ```
| eval Service_ACKED = if(currentPhase == "ACKED", count_by_currentPhase_Service, 0)
| eval Service_RESOLVED = if(currentPhase == "RESOLVED", count_by_currentPhase_Service, 0)
| stats sum(Service_ACKED) as Service_ACKED sum(Service_RESOLVED) as Service_RESOLVED by Service ``` sum is superficial here ```

[Rajaion]

Hi @yuanliu,

That's exactly what I'm looking for!
Thank you for the details in the research, I am still learning to use the tool, I discover more every day.
Indeed, my approach was, in hindsight, not logical but I also thought that I was not using the appendcols and join functions correctly, hence my original question.

Thanks again for your help.

Best regards,

Rajaion