Multiple sums in stats query

[rwalker1072]

Hi!

I'm attempting to take an existing query and update it to do the following:

For the last 24 hours, sum and list records where Source IP has total outgoing bytes greater than 5GB. Each record should have User, Source IP, Destination IP, Application, total bytes for that record (App Outgoing Bytes (GB)) and the total for the source IP (Source IP Outgoing Bytes(GB)).

Current Results - single sum
User    Source IP   Destination IP  Application App Outgoing Bytes (GB)
unknown 1.1.1.1 7.7.7.7 WEB  38.51
unknown 2.2.2.2 8.8.8.8 SSL  24.33

Desired Results

User    Source IP   Destination IP  Application App Outgoing Bytes (GB) Source IP Outgoing Bytes(GB)
unknown 1.1.1.1 7.7.7.7 WEB  38.51   43.51 
unknown 1.1.1.1 2.2.2.2 WEB  2.50    43.51 
unknown 1.1.1.1 3.3.3.3 WEB  2.50    43.51 
unknown 2.2.2.2 8.8.8.8 SSL  24.33   24.33 

Current query - single sum

stats sum(bytes_out) as BytesOut by User, "Source IP", "Destination IP", "Application" 
|  eval outgoingBytes = round(BytesOut / (1024 * 1024 * 1024),4) 
|  search outgoingBytes >= 5 
|  rename outgoingBytes as "App Outgoing Bytes (GB)" 
|  sort - "App Outgoing Bytes (GB)" 
|  fields - BytesOut

Attempted to use eventstats, but that raises an auto-finalize error. Hoping to resolve this without touching limits, if possible.

[rwalker1072]

We're going to go with the summary index. Thanks for all the input!

[rwalker1072]

Looking at this from a for each way: first, perform a summary on the source IP addresses:

stats sum(summary_bytes_out) as SumBytesOut by "Summary Source IP"
| eval sumOutgoingBytes = round(SumBytesOut / (1024 * 1024 * 1024),4)
| search sumOutgoingBytes >= 5
| rename sumOutgoingBytes as "Summary Source IP Outgoing Bytes (GB)"
| sort - "Summary Source Outgoing Bytes (GB)"
| fields - SumBytesOut

That generates the following:

Summary Source IP Summary Source IP Outgoing Bytes (GB)
1.1.1.1 43.51
2.2.2.2 24.33

Then Use a for each to feed each Source IP Address into the detail query, like this:

stats sum(summary_bytes_out) as SumBytesOut by "Summary Source IP"
| eval sumOutgoingBytes = round(SumBytesOut / (1024 * 1024 * 1024),4)
| search sumOutgoingBytes >= 5
| rename sumOutgoingBytes as "Summary Source IP Outgoing Bytes (GB)"
| sort - "Summary Source Outgoing Bytes (GB)"
| fields - SumBytesOut
| for each "Summary Source IP"

[

stats sum(bytes_out) as BytesOut by User, "Source IP", "Destination IP", "Application"
| eval outgoingBytes = round(BytesOut / (1024 * 1024 * 1024),4)
| rename outgoingBytes as "App Outgoing Bytes (GB)"
| sort - "App Outgoing Bytes (GB)"
| fields - BytesOut

]

Unfortunately, getting "Your search isn't generating any statistic or visualization results."

[Vijeta · Accepted solution]

eventstats should work, try using something like below:-

<yourquery>| eventstats sum(bytes_out) as BytesOut by SourceIP| eval outgoingBytes = round(BytesOut / (1024 1024 1024),4) 
 | where outgoingBytes >= 5 | table User Source IP Destination IP Application App Outgoing Bytes (GB) Source IP Outgoing Bytes(GB)

[rwalker1072]

eventstats generates an auto-finalize error. Hoping to resolve this without touching limits, if possible.

[Vijeta]

May be you can use summary indexing in interval of an hour and then run your report on results from summary index ?

[rwalker1072]

Possibly. I'll look into this. Thanks!

Also will look into the map command and for each to see if there's anything that can be done there.

[kmaron]

I think what you need is an appendpipe. Try this.

stats sum(bytes_out) as BytesOut by User, "Source IP", "Destination IP", "Application" 
| eval outgoingBytes = round(BytesOut / (1024 1024 1024),4) 
| search outgoingBytes >= 5 
| rename outgoingBytes as "App Outgoing Bytes (GB)" 
| appendpipe [ stats sum(bytes_out) as SourceBytesOut by "Source IP"
| eval SourceBytesOut = round(SourceBytesOut/(1024 1024 1024),4)
| rename SourceBytesOut as "Source IP Outgoing Bytes (GB)"]
| sort - "Source IP" 
| fields - BytesOut

[rwalker1072]

Thank you for trying! Unfortunately, appendpipe generates 10,000 rows, incorrect results for "App Outgoing Bytes (GB)" and no results (blanks) for "Source IP Outgoing Bytes (GB)"