Kaseya Community

SQL Script: Top Event Log Reporters, Source, Number of Events

  • I've found this little SQL script to be particularly useful in identifying which agents are sending in excessive amounts of Windows Event Logs. You'll want to adjust the date and run the query against your ksubscribers database in SQL Server Management Studio.

    I couldn't find it posted anywhere in the forums so thought I would share.

    SELECT m.displayName,a.source,count(a.agentGuid) from
    ntEventLog20130423 a LEFT OUTER JOIN machNameTab m ON
    a.agentGuid = m.agentGuid WHERE a.eventTime > DATEADD(hour, -1, CURRENT_TIMESTAMP)
    GROUP BY a.agentGuid, m.displayName,a.source
    HAVING COUNT(a.agentGuid) > 100
    ORDER BY count(a.agentGuid) desc
    On that note, anyone have other SQL gems they find valuable?
  • We have a similar script that is setup as a scheduled job. It emails the results to us using sp_send_dbmail. However, I don't really like it, because the formatting doesn't come through. Does anyone have a way to email the results, such that they come through in an HTML table, or a properly formatted CSV attachment, or something similar?

    Here's the script we use, by the way. Part of it was posted at some point on the Kaseya Forums by someone else, and we took it and modified it.


    -- Start T-SQL

       USE msdb

       EXEC sp_send_dbmail

         @profile_name = 'xxxReplacexxx',

         @recipients = 'xxx@xxx.com',

         @subject = 'Most Common Event Log IDs for All Agents Combined',

         @body = 'Below are the most common events monitored by the agents.',

         @body_format = 'TEXT',

         @importance = 'High',

         @attach_query_result_as_file = '0',

         @execute_query_database = 'ksubscribers',

         @query = 'SET NOCOUNT ON SELECT TOP 20 a.eventId, a.EventMessage, a.logtype, a.eventtype, b.groupName, b.machName, count (a.agentguid) cnt FROM vntEventLog a, machnametab b WHERE a.agentguid = b.agentguid GROUP BY b.groupName, b.machName, a.eventid, a.EventMessage, a.logtype, a.eventtype ORDER BY cnt desc'

    -- End T-SQL --

  • Have you tried using Reporting Services to build a subscribed report? You could have that sort and format to your liking and email you excel, html, pdf, etc......

    you could use report builder to make and publish a nice report based on the query you posted. SSRS (reporting services) is easy to make a report with too, like playing in excel.