Splunk Search

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

Laya123
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

lguinn2
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

Laya123
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

Laya123
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

lguinn2
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

Laya123
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

lguinn2
Legend

You can look up the algorithm on the Internet with the search "calculate greatest common factor of two variables." I would have to do a bunch of research and write code - possibly even a custom Splunk command - to do this. Sorry, but that is more work than I think you can expect from the community.

Unless someone else has already solved this problem and is willing to share their code...

0 Karma

Laya123
Communicator

Hi,

I can understand the situation, I tired all possibility ways but no luck, that is the I have written to you. but its okay if any one have tried this before please share with me

Thank you

0 Karma

lguinn2
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

Laya123
Communicator

Hi,

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

Please help me

Thanks

0 Karma

Laya123
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

Laya123
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 N_Total=5, S_Total=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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...