topic Re: how to get the zero valued details also in stats table? in Splunk Search

how to get the zero valued details also in stats table?

rkishoreqa — Wed, 03 Feb 2021 16:32:14 GMT

Hi team,

I prepared a stats query and it is working fine. But I need to know the Application names which are not having the transactions. Below is the query that I was prepared.

index="index1" ApplicationName="app1" OR ApplicationName="app2" OR ApplicationName="app3" OR ApplicationName="app4" OR ApplicationName="app5" OR ApplicationName="app6" OR ApplicationName="app7" OR ApplicationName="app8" OR ApplicationName="app9" | chart count(ApplicationName) over ApplicationName by Status |addtotals

From the above query I am getting the results as follows:

ApplicationName Success Failed Total
app2 3 0 3
app5 9 1 10

Now I need to enhance the above query to get the results like below:

ApplicationName Success Failed Total
app1 0 0 0
app2 3 0 3
app3 0 0 0
app4 0 0 0
app5 9 1 10
app6 0 0 0
app7 0 0 0

can anyone help me on this, thanks in advance.

Re: how to get the zero valued details also in stats table?

me74fhfd — Wed, 03 Feb 2021 17:43:13 GMT

Whats excatly the issue here, SPL not showing the results for other applications? Also, if you have only 9 apps you can use this much cleaner SPL: index="index1" ApplicationName=app* | chart count(ApplicationName) over ApplicationName by Status |addtotals, alternatively use regex to filter it down.

Re: how to get the zero valued details also in stats table?

rkishoreqa — Thu, 04 Feb 2021 08:51:38 GMT

The requirement is to show us the table if Application has the 'Success' or 'Failed' log count. Also if one application is not logging then it needs to show 0,0 in 'Success' and 'Failed' columns.

Re: how to get the zero valued details also in stats table?

manjunathmeti — Thu, 04 Feb 2021 11:41:14 GMT

hi @rkishoreqa,

If you don't have data for some apps in the index then you need to create it. Check this.

Re: how to get the zero valued details also in stats table?

rkishoreqa — Thu, 04 Feb 2021 14:46:31 GMT

@manjunathmeti : Excellent, it is working as expected. How can I sort the 'Total' column.

Re: how to get the zero valued details also in stats table?

manjunathmeti — Thu, 04 Feb 2021 14:53:00 GMT

Using sort command.

| sort Total

https://docs.splunk.com/Documentation/Splunk/8.1.1/SearchReference/Sort

Re: how to get the zero valued details also in stats table?

rkishoreqa — Fri, 19 Feb 2021 09:19:38 GMT

@manjunathmeti : I used the below query, but the counts are not matching.

I changed the query as below, here some applications are like appname and some like appname.application. So I added app1*,app2*,....
Now the counts are perfect and getting the duplicate application names.

index="index1" ApplicationName IN (app1*,app2*,app3*,app4*,app5*,app6*,app7*,app8*,app9*)
| chart count(ApplicationName) over ApplicationName by Status
| addtotals
| append
[| makeresults
| eval ApplicationName=split("app1,app2,app3,app4,app5,app6,app7,app8,app9", ",")
| mvexpand ApplicationName
| fields - _time ]
| fillnull value=0
| stats max(*) as * by ApplicationName

Can you please help me on this.

Re: how to get the zero valued details also in stats table?

manjunathmeti — Mon, 22 Feb 2021 04:50:03 GMT

Can you provide some data and explain what is wrong?