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

[rkishoreqa]

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.

[rkishoreqa]

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

[manjunathmeti]

Using sort command.

| sort Total

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

[rkishoreqa]

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

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

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.

[manjunathmeti]

Can you provide some data and explain what is wrong?

[manjunathmeti · Accepted solution]

hi @rkishoreqa,

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

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

[me74fhfd]

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.

[rkishoreqa]

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.