Splunk Search
Highlighted

How do I edit my search to get both subtotals and the grand total?

Communicator

hi,

Is it possible to get subtotals?

I have attached a file of how my output looks like. I want subtotals by Cluster.

I have used the search below to get the attached output. It is giving only Grandtotals, but I want subtotals by Cluster. Please help me to do this

|inputlookup _AADaily.csv |where Cluster!="ST6"
|eval CH = Cluster . "::" . host 
|eval AAtype=AAtype + "_" 
|chart count(ProjectName) as C over CH by AAtype
|eval Cluster=mvindex(split(CH,"::"),0)
|eval host=mvindex(split(CH,"::"),1)
|fields - CH|table Cluster host *_
|addtotals fieldname=Grand_Total
|addcoltotals labelfield=Cluster label=Grand_Total

Thanks in advance

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

I got the solution for this now and I am able to get the subtotals using appendpipe. Below is the search, but can you help me to get the correct grandtotal? This search is giving Grand total, but its adding subtotals also, so it's adding individual values + subtotals of those individual values, doubling the actual Grand total. Can anyone help me to do this? Example if NTotal=5, STotal=6, the GrandTotal should be 11 but it is showing 22.

|inputlookup _AADaily.csv |where Cluster="N" AND Cluster!="ST6"
|eval CH = Cluster . "::" . host 
|eval AAtype=AAtype + "_" 
|chart count(ProjectName) as C over CH by AAtype|addcoltotals labelfield=CH label=N_Total |appendpipe[|inputlookup _AADaily.csv |where Cluster="S" AND Cluster!="ST6"
|eval CH = Cluster . "::" . host 
|eval AAtype=AAtype + "_" 
|chart count(ProjectName) as C over CH by AAtype|addcoltotals labelfield=CH label=S_Total]
|eval Cluster=mvindex(split(CH,"::"),0)
|eval host=mvindex(split(CH,"::"),1)
|fields - CH|table Cluster host *_
|addtotals fieldname=Grand_Total
|addcoltotals labelfield=Cluster label=Total

Thanks in advance

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Legend

I think there is an easier way

| inputlookup _AADaily.csv
| where (Cluster="N" OR Cluster="S")  AND ProjectName=*
| eval AAtype=AAtype + "_"
| stats count by Cluster host AAtype
| append_pipe [ stats sum(count) as count by Cluster | eval host="*Subtotal" ]
| append_pipe [ where host="*Subtotal" | stats sum(count) as count | eval Cluster="*Grand Total" ]
| sort Cluster host AAtype

If you really want the AAType as separate columns, you can add the following at the end

| eval CH = Cluster . "::" . host
| xyseries CH AAType count
0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

Hi,

Thank you for your immediate response but I am not getting Grandtotal with this query

Please help me

Thanks

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

Hi, I am getting Grandtotal, there is small typo error in your query; below line you forgot to mention '*' symbol. I have added and getting grandtotal.

| append_pipe [ where host="Subtotal" | stats sum(count) as count | eval Cluster="Grand Total" ]

But I want AAtype as seperate columns,

| eval CH = Cluster . "::" . host
| xyseries CH AAType count

these 2 lines I have added at the end of the query, I am getting AAType wise count but missing subtotal and Grandtotals. when I delete last 2 lines its giving subtotals and grandtotal but not getting AAType as seperate columns.

Please help me to do this

Thanks in advance

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Legend
| inputlookup _AADaily.csv
| where (Cluster="N" OR Cluster="S")  AND ProjectName=*
| eval AAtype=AAtype + "_"
| eval CH = Cluster . "::" . host
| chart count by CH AAtype
| rex field=CH "?<Cluster>.*?::?<host>.*"
| append_pipe [ stats sum(*) as * by Cluster | eval host="*Subtotal" ]
| append_pipe [ where host="*Subtotal" | stats sum(*) as * | eval Cluster="*Grand Total" ]
| sort Cluster host

I think this may work if you want to see the output in a chart, although I am a little unsure about the stats commands...

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

Thank you for your response but this is giving an error;

i sorted this issue with below query

|inputlookup _AADaily.csv |where Cluster="N" AND Cluster!="ST6"
|eval CH = Cluster . "::" . host 
|eval AAtype=AAtype + "_" 
|chart count(ProjectName) as C over CH by AAtype|addcoltotals labelfield=CH label=N_Total |appendpipe[|inputlookup _AADaily.csv |where Cluster="S" AND Cluster!="ST6"
|eval CH = Cluster . "::" . host 
|eval AAtype=AAtype + "_" 
|chart count(ProjectName) as C over CH by AAtype|addcoltotals labelfield=CH label=S_Total]
|eval Cluster=mvindex(split(CH,"::"),0)
|eval host=mvindex(split(CH,"::"),1)
|fields - CH|table Cluster host Activate_ Publish_ Launch_
|addtotals fieldname=Grand_Total
|appendpipe[where Cluster="Nap_Total" OR Cluster="Slo_Total" |addcoltotals labelfield=Cluster label=Grand_Total|where Cluster="Grand_Total"]

Thanks

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

Hi,

from above query I am getting results like

Cluster--------------host------------Activate-------------Publish------------Launch
A--------------------- A1------------- 20------------------- 15----------------- 12
B--------------------- B1---------------- 30-------------------22------------------ 18
C--------------------- C1-------------- 25------------------18-------------------- 15

Is it possible to calculate ratio for Activate and Publish and I want to display the result in the format of the ratio like 1:2

Thanks in advance

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Legend

Calculating the ratio is easy, just add the following at the end of the search string

| eval Ratio =  Activate / Publish

However, calculating this as a ratio of two numbers is not trivial. To do it, you need to identify the greatest common factor of the two variables, and then divide each of them by that...

0 Karma
Highlighted

Re: How do I edit my search to get both subtotals and the grand total?

Communicator

Thank you, I tried this but I want to see the output as ratio. can you please tell me how to calculate common factor of the two variables with an example.

Thanks in advance

0 Karma