Happy New Year!

I was faced with a need to identify any machines that had non-responsive C:\ drive counters, i.e. those machines which had a counter status of "Not Responding" or "No Current Data."  Identifying these machines in SQL is easy enough (I believe there are queries already on the forums to get a list of machines with non-responsive counters); however, I needed a way to flag these machines in Kaseya so that I could throw the kitchen sink at them script-wise (ensure that Performance Logs & Alerts is Automatic and Started and that LogicalDisk performance counters aren't explicitly disabled in the Registry).

So, I made the query you see below.  A few must read caveats:

1) You need to be on-prem with direct database access in order to run this query

2) You'll need to create a Machine Custom Field (I named mine Unique_Snowflake* but you can do whatever you'd like--just update the variable in the SQL code) that will contain either True or False--True if the machine has a non-responsive C:\ drive counter and False if it's fine:

3) (optional) You'll need to pre-populate the Machine Custom Field you just created with False

4) You'll need to create a view for machines where the Unique_Snowflake* (or whatever) MCF is True -and- False to help identify machines that are borked and OK, respectively:

5) Your Monitor Set that monitors the C:\ drive must have the C:\ drive explicitly mentioned as the monitored object, and you must know the Counter Name of that object:

6) This script uses today's monitorCounterLogYYYYMMDD table, where YYYYMMDD are calculated when the script is run (hence the on-the-fly query creation).  Make sure your Disk monitor set has collected data (for example, if you're polling for disk free space on an hourly basis, the monitorCounterLogYYYYMMDD table might not have data until at least 1 AM for all Agents).

7) USE THIS SCRIPT AT YOUR OWN RISK.  This script directly modifies your Kaseya database by updating the auditRsltManualFieldValues for the MCF that you specify.  Kaseya's database schema may change.  This has been tested on R8 8.0.0.12, but it's possible that these tables' names will change and that this query might break.  This might not be the best or most efficient SQL query, so please go easy on me 

<Beginning of SQL>

-- Declare the user-defined variables

DECLARE @fieldName NVARCHAR(100)

DECLARE @monitorSetName NVARCHAR(100)

DECLARE @driveLetter NVARCHAR(1)

-- Set the user-defined variables

SET @fieldName = 'Unique_Snowflake*' -- The name of the MCF that acts as a "flag" set to True on problem machines

SET @monitorSetName = '_AM - DISK (Disk Free Space - Servers)*' -- The name of the Monitor Set that monitors the primary drive

SET @driveLetter = 'C' -- The letter of the primary hard drive that the Monitor Set references via Counter Name

-- Validate that the MCF exists

DECLARE @fieldNameFK NVARCHAR(100)

SET @fieldNameFK = ISNULL((SELECT id FROM auditRsltManualFields      WHERE fieldName = @fieldName),'Does Not Exist')

IF @fieldName != 'Does Not Exist'

BEGIN

       -- Nobody is a unique snowflake yet... set any existing defined values for the "flag" MCF to False

       UPDATE auditRsltManualFieldValues

       SET fieldValue = 'False'

       WHERE fieldNameFK = @fieldNameFK

       -- Define the tableName based on today's date

       DECLARE @monitorCounterLogTableName VARCHAR(100)

       DECLARE @currentYear NVARCHAR(4)

       SET @currentYear = DATENAME(YEAR, GETDATE()) -- Returns 4 digits

       DECLARE @currentMonth NVARCHAR(2)

       SET @currentMonth = CAST(RIGHT('0' + RTRIM(DATEPART(MONTH, GETDATE())),2) AS NVARCHAR(2)) -- Returns 2 digits

       DECLARE @currentDay NVARCHAR(2)

       SET @currentDay = CAST(RIGHT('0' + RTRIM(DATENAME(DAY, GETDATE())),2) AS NVARCHAR(2)) -- Returns 2 digits

       SET @monitorCounterLogTableName =

              (SELECT 'monitorCounterLog'

                           + @currentYear

                           + @currentMonth

                           + @currentDay)

       SELECT @monitorCounterLogTableName AS 'Using table:'

       -- From here on out, we have to use EXEC because the table names are themselves variables

       DECLARE @sqlCommand NVARCHAR(MAX)

       SET @sqlCommand = 'IF OBJECT_ID(''' + @monitorCounterLogTableName + ''') IS NOT NULL

                                         BEGIN

                                                DECLARE @uniqueSnowflakeAgentGUID NVARCHAR(100)

                                                DECLARE uniqueSnowflakeCursor CURSOR LOCAL FOR

                                                       SELECT DISTINCT m.agentGuid FROM ' + @monitorCounterLogTableName + ' AS l

                                                       JOIN machNameTab AS m ON l.agentGuid = m.agentGuid

                                                       JOIN monitorCounter AS c ON l.monitorCounterId = c.monitorCounterId

                                                       JOIN monitorSet AS s ON c.monitorSetId = s.monitorSetId

                                                       WHERE c.name = ''' + @driveLetter + '''

                                                       AND l.counterValue < 0

                                                       AND s.name = ''' + @monitorSetName + '''

                                                       OPEN uniqueSnowflakeCursor

                                                       FETCH NEXT FROM uniqueSnowflakeCursor INTO @uniqueSnowflakeAgentGUID

                                                       WHILE @@FETCH_STATUS = 0

                                                       BEGIN

                                                              IF EXISTS (SELECT agentGuid FROM auditRsltManualFieldValues

                                                              WHERE agentGuid = @uniqueSnowflakeAgentGUID

                                                              AND fieldNameFK = ' + @fieldNameFK + ')

                                                              BEGIN

                                                                     SELECT @uniqueSnowflakeAgentGUID AS ''MCF being updated for agentGuid:''

                                                                     UPDATE auditRsltManualFieldValues

                                                                     SET fieldValue = ''True''

                                                                     WHERE agentGuid = @uniqueSnowflakeAgentGUID

                                                                     AND fieldNameFK = ' + @fieldNameFK + '

                                                              END

                                                              ELSE

                                                              BEGIN

                                                                     SELECT @uniqueSnowflakeAgentGUID AS ''MCF being added for agentGuid:''

                                                                     INSERT INTO auditRsltManualFieldValues VALUES(@uniqueSnowflakeAgentGUID, ' + @fieldNameFK + ', ''True'')

                                                              END

                                                              FETCH NEXT FROM uniqueSnowflakeCursor INTO @uniqueSnowflakeAgentGUID

                                                       END

                                                CLOSE uniqueSnowflakeCursor

                                                DEALLOCATE uniqueSnowflakeCursor

                                         END

                                         ELSE

                                         BEGIN

                                                SELECT ''Unable to proceed... the ' + @monitorCounterLogTableName + ' table does not exist'' AS ''Status:''

                                         END'

       SELECT @sqlCommand AS 'Executing the following SQL query:'

       EXEC (@sqlCommand)

END

<End of SQL>

I hope at least someone else out there has needed a SQL query like this