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.
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
@manjunathmeti : Excellent, it is working as expected. How can I sort the 'Total' column.
Using sort command.
| sort Total
https://docs.splunk.com/Documentation/Splunk/8.1.1/SearchReference/Sort
@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.
Can you provide some data and explain what is wrong?
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
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.
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.