topic Re: How do I combine subtotals and totals in a search query? in Splunk Search

How do I combine subtotals and totals in a search query?

rajyah — Tue, 25 Sep 2018 01:45:46 GMT

Is it possible to do this?

Should I use appendcol? multisearch? join? Please enlightened me.

Scenario: The IP below the Sub-Total is the "server" while the others are "clients".

I used the tutorialdata.zip of Splunk in this case but the IPs indicated are only samples.

Thank you!

Re: How do I combine subtotals and totals in a search query?

DalJeanis — Tue, 25 Sep 2018 02:53:19 GMT

Here's one way.

| makeresults 
| eval mydata="1server,12.34.56.78,1.1!!!! 1server,99.99.99.99,1.2!!!! 1server,88.88.88.88,1.3!!!! 1server,11.11.11.22,1.4!!!! 2client,123.232.22.11,50" 
| makemv delim="!!!! " mydata 
| mvexpand mydata
| makemv delim="," mydata 
| eval type=mvindex(mydata,0), IP=mvindex(mydata,1), value=mvindex(mydata,2)
| table type IP value
| rename COMMENT as "The above just enters your sample data." 

| rename COMMENT as "Mark the records as details so we can identify them to process them multiple times." 
| eval rectype="detail"

| rename COMMENT as "Create and mark the desired subtotal records." 
| appendpipe [
    | where rectype="detail" AND type!="2client" 
    | stats sum(value) as value by type 
    | eval IP="subtotal", rectype="subtotal"
    ]

| rename COMMENT as "Create and mark the desired total record." 
| appendpipe [
    | where rectype="detail" 
    | stats sum(value) as value 
    | eval IP="total", rectype="total", type="total"
    ]

| rename COMMENT as "Format the output values, sort records into order, rename fields, drop unneeded fields." 
| eval value=round(1.00*tonumber(value),1)
| sort 0 type rectype IP
| rename IP as ClientIP value as GBytesUsed
| table ClientIP GBytesUsed

Re: How do I combine subtotals and totals in a search query?

rajyah — Tue, 25 Sep 2018 05:32:03 GMT

Thanks a lot for replying sir. I really appreciate it. I'm fairly new in Splunk but I think your query only search for a static list of ip. Here is my query:

index="tutorial" host=www1
| stats sum(bytes) as Bytes by clientip
| head 10
| addcoltotals Bytes label="Total GBytes" labelfield=clientip
| eval Bytes=round(Bytes/1024/1024,2)

I used the tutorialdata.zip of Splunk. The query logically goes until "sub-total" from the given problem. What I wanted to know is if there's a way to add another search result below the given query then add them again to get the "Total".

Thank you sir.

Re: How do I combine subtotals and totals in a search query?

rajyah — Wed, 26 Sep 2018 07:06:59 GMT

Or should I say, the IP below the 'Sub-Total' will be the top 1 from source=www3 while the other clients will be from source=www1.

Re: How do I combine subtotals and totals in a search query?

woodcock — Wed, 26 Sep 2018 16:10:56 GMT

Yes, like this:

index="tutorial" host=www1
| stats sum(bytes) as Bytes by clientip 
| head 10
| addcoltotals Bytes label="SubTotal GBytes" labelfield=clientip
| eval Bytes=round(Bytes/1024/1024,2)
| append
[search index="tutorial" host=www1
| stats sum(bytes) as Bytes by clientip 
| head 10
| addcoltotals Bytes label="SubTotal GBytes" labelfield=clientip
| eval Bytes=round(Bytes/1024/1024,2)]
| addcoltotals Bytes label="Total_GBytes" labelfield=clientip
| eval Bytes=if((clientip=="Total_GBytes"), Bytes/2, Bytes)

Re: How do I combine subtotals and totals in a search query?

rajyah — Thu, 27 Sep 2018 01:29:03 GMT

Thank you so much sir. You saved my life!