Show a 0 if no data on a source

[Rajaion]

Hello community,

I'm having a problem that's probably easy to solve, but I can't figure it out.

I have a query that will query an index that contains alerts from Splunk OnCall. And I count each alert source (via the associated routingkey from OnCall) and its status (Acknowledged or not).

`victorops_incidents`  | sort lastAlertTime desc | dedup incidentNumber | fields * | search org="*" routingKey=** pagedPolicies{}.policy.name!=0_Reroute_alertes currentPhase!=RESOLVED
| eval currentPhase=case(like(currentPhase, "%UNACKED%"), "Non acquitté", like(currentPhase, "%ACKED%"), "En cours") 
| eval routingKey=case(like(routingKey, "%routingcontrol-m%"), "Control-M", like(routingKey, "%dyn%"), "Dynatrace", like(routingKey, "%centreon%"), "Centreon", like(routingKey, "%servicepilot%"), "ServicePilot", like(routingKey, "%p_1%"), "P1")
| rename currentPhase as Etat, routingKey as Source
| chart count by Etat, Source
| sort - Etat

I have an almost perfect table which summarizes everything but I am missing some information: I sometimes have a source which has not generated any alert so it is absent from the table (in the screen below, I have the sources "Control-M", "Dynatrace" and "ServicePilot" but I am missing "Centreon" because the latter did not have any incidents in the period of time) :

My question is the following: how to make all the sources appear but display 0 when they have not had any alerts?

Best regards,

Rajaion

[ITWhisperer]

Try something like this

| table Etat, "Control-M", "Dynatrace", "ServicePilot", "Centreon"
| fillnull value=0

[Rajaion]

Hi @ITWhisperer,

Thank you for your help, I have my source "Centreon" but it does not display 0 yet. I had already tried the "fillnull" but poorly because it created extra fields.

Best Regards,

Rajaion

[ITWhisperer · Accepted solution]

Try like this

| table Etat, "Control-M", "Dynatrace", "ServicePilot", "Centreon"
| fillnull value=0 "Control-M", "Dynatrace", "ServicePilot", "Centreon"

[Rajaion]

I just saw your new message, it works even better and it's cleaner.
Thank you for your help !

[Rajaion]

By manually setting for a source, it works, even if it is not optimal.

| eval "Centreon"=if(isnull(Centreon),0,'Centreon')