Splunk Search

how to remove duplicates in appended queries

rkishoreqa
Communicator

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.

Labels (1)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

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

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

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

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.

tscroggins
Motivator

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

rkishoreqa
Communicator

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

0 Karma

tscroggins
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...