Splunk Search

Multiple sums in stats query

rwalker1072
New Member

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.

Tags (1)
0 Karma
1 Solution

Vijeta
Influencer

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)

View solution in original post

0 Karma

rwalker1072
New Member

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

0 Karma

rwalker1072
New Member

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."

0 Karma

Vijeta
Influencer

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)
0 Karma

rwalker1072
New Member

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

0 Karma

Vijeta
Influencer

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

0 Karma

rwalker1072
New Member

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.

0 Karma

kmaron
Motivator

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
0 Karma

rwalker1072
New Member

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)"

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...