topic Re: Table of bytes_out by user, hostname where total bytes out > 1MB in Alerting

Table of bytes_out by user, hostname where total bytes out > 1MB

_smp_ — Tue, 29 Sep 2020 14:58:14 GMT

I have proxy logs that contain three relevant fields: user, hostname, and bytes_out. I have been challenged to generate a notification when the total bytes_out for user A, B, or C exceeds 100MB in the last 24 hours. That notification needs to include a table with the total bytes_out by user, hostname and their total for the 24 hour period.

So for example say the total bytes_out in the last 24 hours for user=A is 10MB, user=B is 150, and user=C is 200. I should get two alerts - one for user=B and one for user=C. The alert should contain a table like this:

B   www.microsoft.com  20MB  150MB
B   www.google.com  40MB  150MB
B   www.apple.com  90MB  150MB
C  www.amazon.com  100  200MB
C  www.dropbox.com  50  200MB
C  www.yahoo.com  40  200MB
C  www.youtube.com  10  200MB

I think we could also handle one report with the entire result set. Anyone willing to take a shot at this? It's a bit beyond my current skill level.

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

cmerriman — Tue, 18 Jul 2017 23:02:53 GMT

How about something like

Index=proxylogs earliest=-24h latest=now |eventstats sum(bytes_out) as total_bytes_out by user|stats sum(bytes_out) as bytes_out max(total_bytes_out) as total_bytes_out by user hostname|search total_bytes_out>100

And you can set an alert for whenever this produces results or one per result, depending on preference.

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

richgalloway — Tue, 18 Jul 2017 23:08:19 GMT

I'll take a shot at it. See if this gets you started. It should produce the table you desire. If it works as you expect, then schedule the search and trigger an alert if the number of results is not zero.

index=foo hostname=* bytes_out=* (user="A" OR user="B" OR user="C") | streamstats sum(bytes_out) as Total_bytes_out by user | eval Total_bytes_out=Total_bytes_out/(1024*1024) | where Total_bytes_out > 100 | table user hostname bytes_out Total_bytes_out

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

woodcock — Wed, 19 Jul 2017 00:40:14 GMT

Just add this to your existing search:

| eventstats sum(bytes_out) AS TotalBytesOutThisUser BY User
| search TotalBytesOutThisUser > 104857600
| table user hostname bytes_out TotalBytesOutThisUser

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

DalJeanis — Wed, 19 Jul 2017 01:53:09 GMT

streamstats is going to add them up one record at a time, so the earlier records will not qualify and will be lost. use eventstats to non-destructively calculate the sum and add it to the entire record set for the user.

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

DalJeanis — Tue, 29 Sep 2020 14:58:25 GMT

Efficiency note - move the search right after the eventstats to eliminate the unwanted records as early as possible. Then you can get rid of total_bytes_out from the stats command.

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

DalJeanis — Wed, 19 Jul 2017 01:56:27 GMT

...subject to the assumption that his current search calculates the total bytes by hostname and user...

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

cmerriman — Wed, 19 Jul 2017 02:16:05 GMT

He did state that he wanted the total bytes listed in the results table. But that is a good efficiency note.

Re: Table of bytes_out by user, hostname where total bytes out > 1MB

_smp_ — Wed, 19 Jul 2017 14:54:59 GMT

Thank you everyone for your feedback. The 'eventstats' command was the key for me. In fact, the idea to try eventstats hit me randomly last night. I worked on the query a bit this morning and came up with this exact solution independently. I just came to verify it against all the comments.

Thanks again very much for the feedback. It was a useful exercise for me to help wrap my head around the eventstats command.