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.
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)
We're going to go with the summary index. Thanks for all the input!
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."
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)
eventstats generates an auto-finalize error. Hoping to resolve this without touching limits, if possible.
May be you can use summary indexing in interval of an hour and then run your report on results from summary index ?
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.
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
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)"