how to remove duplicates in appended queries

[rkishoreqa]

I used the  below query, 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 anyone please help me on this.

[scelikok · Accepted solution]

Hi @rkishoreqa,

Can you try renaming fields like this?

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

[tscroggins]

@rkishoreqa 

To remove the duplicate ApplicationName values with counts of 0 in the various Status columns, untable and then re-chart the data by replacing your final stats command with the following two commands:

| untable ApplicationName Status count
| chart max(count) over ApplicationName by Status

[ITWhisperer]

I am not clear what it is you are asking - you have have counts which are correct and you have the extra values for the apps you added - is this not what you expected or are you trying to do something different?

[rkishoreqa]

@ITWhisperer 
 I am preparing a query to get the Application Vs Status counts along with the Applications  which are not having the transactions (like below table).  Below is the query that I was prepared.  As I said earlier here some applications are like app1,app2... and some like app3.application, app4.application......

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

  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

@tscroggins   I tried with the two commands given by you, but no difference.

[tscroggins]

Here's a functional example using _internal logs that you can adapt to your source type:

index=_internal sourcetype=splunkd source=*/splunkd.log*
| chart count over component by log_level
| append [| makeresults | eval component=split("AdminManager,ApplicationLicense,BucketMover", ",") | mvexpand component | fields - _time]
| fillnull value=0
| untable component log_level count
| chart max(count) over component by log_level

If you need totals, add the addtotals command to the end of the search:

index=_internal sourcetype=splunkd source=*/splunkd.log*
| chart count over component by log_level
| append [| makeresults | eval component=split("AdminManager,ApplicationLicense,BucketMover", ",") | mvexpand component | fields - _time]
| fillnull value=0
| untable component log_level count
| chart max(count) over component by log_level
| addtotals

For example:

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