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

[_smp_]

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.

[woodcock · Accepted solution]

Just add this to your existing search:

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

[DalJeanis]

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

[_smp_]

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.

[richgalloway]

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

[DalJeanis]

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.

[cmerriman]

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.

[DalJeanis]

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.

[cmerriman]

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