6 Replies Latest reply on Aug 20, 2010 1:30 PM by dmitic

    SQL Database Inventory

    dmitic
    dmitic Aug 13, 2010 10:24 AM

      We are running NPM 10 and APM 3.5. We're also sort of using NPM as our inventory database, with some custom properties defined.

      I'd like to be able to list all SQL databases on a server that's listed as running SQL in APM. Does anyone know if this can be a custom NPM report, UnDP, APM template (even though it's not really performance we're tracking, just getting a list), or is there any other way to achieve this?

      • 1494 Views
      • Tags:
        • Re: SQL Database Inventory
          Capt. Obvious
          Capt. Obvious Aug 13, 2010 2:54 PM (in response to dmitic)

          This should be possible. But let me get a little more information from you...

           

          Do you want:

          A) a List of servers running SQL

          or

          B) a list of all databases in SQL Grouped by server.

          or

          C) Something different than either of the above.

            • Re: SQL Database Inventory
              dmitic
              dmitic Aug 13, 2010 3:15 PM (in response to Capt. Obvious)

              B) is exactly what I need :)

              although it would be good if it was derived from an automatic A) so I wouldn't manually have to enter the servers. I think there should be a way to grab only servers that have SQL monitors assigned to them in APM, and then do the query against them that gets B).

              But even if I manually have to enter and maintain the list of servers, I don't mind, as long as some monitor or report gets me B)

              Thanks for your help!

                • Re: SQL Database Inventory
                  Capt. Obvious
                  Capt. Obvious Aug 13, 2010 3:27 PM (in response to dmitic)

                  well first we would need APM to pull the list of Databases on the SQL server.  i would recommend using WMI and this is the class you would use:

                   

                  win32_perfformatteddata_mssqlserver_sqlserverdatabases

                   

                  Test this first on one of your sql servers using WBEMTEST and try :

                  select * from win32_perfformatteddata_mssqlserver_sqlserverdatabases

                  and see what that returns for you

                    • Re: SQL Database Inventory
                      dmitic
                      dmitic Aug 20, 2010 9:08 AM (in response to Capt. Obvious)

                      That worked, I made a WMI monitor with that query. I assume that APM now has this data in its own database?

                      How would I pull it from there to display on a custom report let's say? I'd also like to filter out the default databases like master, model, tempdb.

                      Thanks!

                        • Re: SQL Database Inventory
                          Capt. Obvious
                          Capt. Obvious Aug 20, 2010 12:06 PM (in response to dmitic)

                          Yes it can be put into a report, instead of fileting the report I would excluse them in the syntax of the WMI query as there is no need to store data on them in your DB if you don't need it

                            • Re: SQL Database Inventory
                              dmitic
                              dmitic Aug 20, 2010 1:30 PM (in response to Capt. Obvious)

                              I excluded them in the WMI query, that was easy, I get the right results using WBEMTEST.

                              However, I'm still not sure how to get this information from the NetPerfMon database now, I looked through some tables and views but couldn't find the results from this query (which I defined as an APM WMI monitor and assigned to a node).