topic Show a 0 if no data on a source in Splunk Search

Show a 0 if no data on a source

Rajaion — Tue, 05 Dec 2023 13:58:58 GMT

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

Re: Show a 0 if no data on a source

ITWhisperer — Tue, 05 Dec 2023 14:08:40 GMT

Try something like this

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

Re: Show a 0 if no data on a source

Rajaion — Tue, 05 Dec 2023 15:58:41 GMT

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

Re: Show a 0 if no data on a source

ITWhisperer — Tue, 05 Dec 2023 16:10:17 GMT

Try like this

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

Re: Show a 0 if no data on a source

Rajaion — Tue, 05 Dec 2023 16:11:33 GMT

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

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

Re: Show a 0 if no data on a source

Rajaion — Tue, 05 Dec 2023 16:13:14 GMT

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