Kaseya Community

Monitoring Data from a SQL Query to Kaseya Server

  • Hey Community,

    Here's an idea I have that I was wondering if anyone else had dabbled in yet... I have this SQL query that produces the top X machines with # of event log events submitted to the Kaseya server, for over 1000 events in the last hour:

    use ksubscribers
    SELECT m.displayName,count(a.agentGuid) from ntEventLog20130830 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
    HAVING COUNT(a.agentGuid) > 1000
    ORDER BY count(a.agentGuid) desc

    I would love to have the Kaseya server be monitoring itself for these occurrences, and generating tickets anytime we get over 1000 events in an hour for a given agent.

    Any thoughts on how I might best accomplish this? Is it even possible? Figured I'd see if anyone had ideas here before I dove into the deep end.

    Cheers,

    Kerry

  • Hi Kerry,

    You would need to can put that SQL Query into a slightly different form and perhaps break it up into a couple SQL Queries, but you can use the sqlRead() step in an Agent Procedure and schedule it to run on any Agent or on the KServer Agent installed by default in 6.3...You can register your approved SQL Queries in an XML on the KServer itself and then execute them on any Agent when you schedule the agent procedure on them.  The query defaults to 'ksubscribers', so no need to do that part.  You can only pass back one result, but that could be the count of machines that have > 1000 event log entries, and then you could issue additional queries to create a concatenated list of machines that meet that criteria in another query if the count is > 0.  Then you can create a ticket using the sendAlert() agent procedure step.

    take a look at the sqlRead() and sendAlert() agent procedure steps in the online help at:

    help.kaseya.com/.../index.asp

    Hope this helps,

    Matt Warburton

    Kaseya Professional Services

  • Hi Kerry

    There's a slight flaw with what you want to achieve.  If the agent receives 1000 events from an agent in an hour  it stops collecting events for the next hour.  Though this may help what you want to achieve.

    If you use this query it will return the machines that have hit that limitation.

    select * from vNtEventLog

    where ApplicationName like 'kaseya agent'

    and eventId = 5000

    You can obviously replace the vNTEventLog with an individual table.

    I also use this query to see what are the top items that are filling up my tables if there are heavy hitters I can either try to fix or add the exclusions xml file so that the server stops collecting those events.  It saves me from collecting millions of events every day.

    --Count Events--

    use ksubscribers

    select top 50 a.eventId, a.logtype, a.eventtype, a.source, b.groupName, b.machName, count (a.agentguid) cnt

    from ntEventLog20130829 a, machnametab b

    where a.agentguid = b.agentguid

    group by a.eventid, b.groupName, a.source, b.machName, a.logtype, a.eventtype

    order by cnt desc

    AC

  • Awesome - thanks guys - I will check this stuff out!

    Kerry

  • any news on this :

    I would love to have the Kaseya server be monitoring itself for these occurrences, and generating tickets anytime we get over 1000 events in an hour for a given agent.

    is it possible to add an event automatically to an ignore / exclusion list if x occurences in an hour !!

    rgds