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 ksubscribersSELECT m.displayName,count(a.agentGuid) from ntEventLog20130830 aLEFT OUTER JOIN machNameTab m ON a.agentGuid = m.agentGuidWHERE a.eventTime > DATEADD(hour, -1, CURRENT_TIMESTAMP)GROUP BY a.agentGuid, m.displayNameHAVING COUNT(a.agentGuid) > 1000ORDER 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.
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:
Hope this helps,
Kaseya Professional Services
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.
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
Awesome - thanks guys - I will check this stuff out!
any news on this :
is it possible to add an event automatically to an ignore / exclusion list if x occurences in an hour !!