Kaseya Community

SQL Query for Monitor-Set / Monitor relationsihps

This question is answered

Hi,

I'm trying to query ksubscribers to pull the all monitor sets that we have together with the monitors under each monitor set.  After exploring all the different tables, I've come up with a query to pull this for the counter monitors:

SELECT
ms.monitorsetid,
mc.monitorCounterID,
ms.name AS monitorsetname,
mc.name AS MonitorCounterName
FROM ksubscribers.dbo.monitorCounter mc
INNER JOIN ksubscribers.dbo.monitorset ms on mc.monitorsetid = ms.monitorsetid

And I've prepared similar queries for the service and process monitor types.  It seems to work and pull what I need, but...

Question 1:

The query I have doesn't use the "Kaseya Provided Views".  Such as those found in documentation: http://help.kaseya.com/webhelp/en/vsa/6030000/en_databaseviews63.pdf.  This is a concern because I want to use this query in an application that will run the query again and again and I'm not certain about the reliability of the tables in this query.  Those views are really the stable interface provided to clients right?  Are these table in the query too much "under the hood"?  Could they change in future releases?  Could they be snapshots that might disappear?  Can you speak to whether or not I'm safe to query these tables:

  1. ksubscribers.dbo.monitorset
  2. ksubscribers.dbo.monitorCounter
  3. ksubscribers.dbo.monitorProcess
  4. ksubscribers.dbo.monitorService

Question 2:

So far it looks to me like only the Counter, Process, and Service types of monitors fall under the umbrella of "Monitor Set", but are they are any other types that I missed that are children of monitor sets?  How about System Check or Alert monitors?

Thank you!

Verified Answer
  • Hi ,

    To answer the several questions is question 1 - I have broken them down piece by piece. Smile

    The query I have doesn't use the "Kaseya Provided Views".  Such as those found in documentation: http://help.kaseya.com/webhelp/en/vsa/6030000/en_databaseviews63.pdf.  This is a concern because I want to use this query in an application that will run the query again and again and I'm not certain about the reliability of the tables in this query.  Those views are really the stable interface provided to clients right?

    The views essentially are mirror what is inside the database tables. Therefore, querying the tables directly should provide the most reliable information.

    Are these table in the query too much "under the hood"?

    No, they would not be too much - however I am not quite sure what you are specifically trying to query this information for. Are you just trying to get a full list of monitors you have available?

    Could they change in future releases?

    Yes, the database tables/schema could possibly change in future releases of the VSA. However, it is very unlikely as this would require several code changes.

    Could they be snapshots that might disappear? 

    These specific tables are not snapshots and should not disappear.

    The MoniotrCounterLogXXXXXXX, MonitorProcessLogXXXXXX, MonitorServiceLogXXXXXXX, and ntEventLogXXXXXXX tables are the only monitor tables that are 'removed' from the database depending on how long you specific to keep logs before archiving them.

    Essentially, they get archived after the time listed in Agent > Log History.

    Can you speak to whether or not I'm safe to query these tables:

    1. ksubscribers.dbo.monitorset
    2. ksubscribers.dbo.monitorCounter
    3. ksubscribers.dbo.monitorProcess
    4. ksubscribers.dbo.monitorService

    There should not be an issue querying these tables.

    So far it looks to me like only the Counter, Process, and Service types of monitors fall under the umbrella of "Monitor Set", but are they are any other types that I missed that are children of monitor sets?  How about System Check or Alert monitors?

    I do not believe there to be any children monitor sets. Perhaps someone else can clarify if they know of any.

    In regards to the System Checks and Alert Monitors, if you want to query the configurations - in the same fashion you are for the monitor sets - the schema convention is slightly different and each alert type has its own db table.

    System Checks = dbo.alertsyscheck

    Alerts = dbo.alertHWChange, dbo.alertBackup, dbo.alertgetfile, dbo.alertsystem, dbo.alertpatch, dbo.alertlowdisk, dbo.alertnewagent, dbo.alertnewapp

    Those are most of the alert tables, I am sure you can find the remaining ones if you are interested in them based on the provided schema structure.

    Hope this helps your query and let me know if I can help further.

    Best Regards,

    Nicolas

All Replies
  • Hi ,

    To answer the several questions is question 1 - I have broken them down piece by piece. Smile

    The query I have doesn't use the "Kaseya Provided Views".  Such as those found in documentation: http://help.kaseya.com/webhelp/en/vsa/6030000/en_databaseviews63.pdf.  This is a concern because I want to use this query in an application that will run the query again and again and I'm not certain about the reliability of the tables in this query.  Those views are really the stable interface provided to clients right?

    The views essentially are mirror what is inside the database tables. Therefore, querying the tables directly should provide the most reliable information.

    Are these table in the query too much "under the hood"?

    No, they would not be too much - however I am not quite sure what you are specifically trying to query this information for. Are you just trying to get a full list of monitors you have available?

    Could they change in future releases?

    Yes, the database tables/schema could possibly change in future releases of the VSA. However, it is very unlikely as this would require several code changes.

    Could they be snapshots that might disappear? 

    These specific tables are not snapshots and should not disappear.

    The MoniotrCounterLogXXXXXXX, MonitorProcessLogXXXXXX, MonitorServiceLogXXXXXXX, and ntEventLogXXXXXXX tables are the only monitor tables that are 'removed' from the database depending on how long you specific to keep logs before archiving them.

    Essentially, they get archived after the time listed in Agent > Log History.

    Can you speak to whether or not I'm safe to query these tables:

    1. ksubscribers.dbo.monitorset
    2. ksubscribers.dbo.monitorCounter
    3. ksubscribers.dbo.monitorProcess
    4. ksubscribers.dbo.monitorService

    There should not be an issue querying these tables.

    So far it looks to me like only the Counter, Process, and Service types of monitors fall under the umbrella of "Monitor Set", but are they are any other types that I missed that are children of monitor sets?  How about System Check or Alert monitors?

    I do not believe there to be any children monitor sets. Perhaps someone else can clarify if they know of any.

    In regards to the System Checks and Alert Monitors, if you want to query the configurations - in the same fashion you are for the monitor sets - the schema convention is slightly different and each alert type has its own db table.

    System Checks = dbo.alertsyscheck

    Alerts = dbo.alertHWChange, dbo.alertBackup, dbo.alertgetfile, dbo.alertsystem, dbo.alertpatch, dbo.alertlowdisk, dbo.alertnewagent, dbo.alertnewapp

    Those are most of the alert tables, I am sure you can find the remaining ones if you are interested in them based on the provided schema structure.

    Hope this helps your query and let me know if I can help further.

    Best Regards,

    Nicolas

  • Thanks for that answer, very helpful.