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

[rajyah]

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!

[woodcock · Accepted solution]

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)

[rajyah]

Thank you so much sir. You saved my life!

[rajyah]

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.

[DalJeanis]

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

[rajyah]

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.