This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Am I on the right track using this SQL query within APM?

I'm trying to build a monitor to tell me when any database gets added to a SQL server or else gets removed.

This query below will list databases when used with Orion Database Manager or else Visual Studio, however, I'm trying to automate this using APM.

Here's the query

use master

SELECT name FROM sysdatabases

But when I try to use it as an APM component I get this when 'testing' it against a SQL server node:

Testing on node xxxxxxx : failed with 'Down' status

Unable to convert query result.

Does anyone have any suggestions? Thanks!!

  • Yes, this something I would like too, but from what I know, right now SAM/APM only accepts numerics emoticons_sad.png, so you could select Select ABS(Count(Name)-<INSERT_COUNT_OF_DBs>) From sysdatabases, then set the warning and critical at > 0.  Not pretty... but that doesn't get renames either emoticons_sad.png or a drop then create new. emoticons_sad.png

    But it is something

  • I haven't tried it yet but I've been wanting to issue a SQL query via an APM powershell script so I could actually interpret the message.  Like I said I haven't done it yet but I don't see why it wouldn't work.

  • but you still need to provide Statistic Data, right?  and that is only a numeric.  You can put things in the message, but that isn't compared, just displayed, right?

    You can also query SQL through a batch file using sqlcmd (if you have sql tools installed).

    The only thing I could come up with is using NCM to do it because it does keep text.

  • Yeah you are right you still can't evaluate the text.  You would have to write into your query the database names that currently exist and then output a 1 or something to show a change along with a message text showing what had changed.  Are there any event logs created when a DB is added or removed?  That might be another approach.

  • What about remembering database names in user table on SQL server and comparing it with current set each poll?

    USE [master]

    IF object_id('previous_database_names') IS NULL

    BEGIN

        SELECT name INTO [previous_database_names] FROM sysdatabases

        SELECT COUNT(1) as changesDetected, 'Initialized, ' + CONVERT(varchar(50), COUNT(1)) + ' databases detected'

            FROM [previous_database_names]

    END

    ELSE

    BEGIN

        DECLARE @cntNew int, @cntRemoved int

        SELECT @cntRemoved = COUNT(1) FROM [previous_database_names] prev

            LEFT JOIN sysdatabases curr ON prev.name = curr.name

                WHERE curr.name IS NULL

        SELECT @cntNew = COUNT(1) FROM sysdatabases curr

            LEFT JOIN [previous_database_names] prev ON prev.name = curr.name

                WHERE prev.name IS NULL

        IF (@cntNew > 0 OR @cntRemoved > 0)

        BEGIN

            SELECT @cntNew + @cntRemoved AS changesDetected, CONVERT(varchar(50),@cntNew) + ' new database(s) and ' + CONVERT(varchar(50),@cntRemoved) + ' removed database(s) detected'

            TRUNCATE TABLE [previous_database_names]

            INSERT INTO [previous_database_names] (name)

                SELECT name FROM sysdatabases

        END

        ELSE

            SELECT 0 AS changesDetected, 'No database change detected'

    END

  • All,

    I am moving this to our new forum. emoticons_happy.png


    Thanks,

    Danielle