Splunk Search

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

rkishoreqa
Communicator

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.

Labels (1)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

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

 

View solution in original post

rkishoreqa
Communicator

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

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

rkishoreqa
Communicator

 

@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.

 

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Can you provide some data and explain what is wrong?

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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
Path Finder

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.

0 Karma

rkishoreqa
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...