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
| 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...
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
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
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...
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
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...
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
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
Hi,
Thank you for your immediate response but I am not getting Grandtotal with this query
Please help me
Thanks
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
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