10 Replies Latest reply on Jan 27, 2016 5:09 PM by alankass

    Find SQL Server installations




      We need to find how many installations of SQL Servers we have in the organization.

      I have created a query to find the following:

      Computer.Software.Add or remove Programs.Program.Name = Microsoft SQL Server 2000 or 2005 or 2008


      Query returns all server have these installations but on many of them, there is no SQL Management studio installed but only the SQL tools.

      For example, attached is a screenshot of a server that has only the SQL Server configuration manager installed but in the Add remove programs, the SQL Server 2008 R2 seems to be installed.


      Is there any way to find what we want?

      It is for licensing purposes in order to specify if there is need to purchace more SQL licenses.


      Thanks in advance,


        • 1. Re: Find SQL Server installations
          Catalysttgj Expert

          You may need to try a compound query. Try changing the "=" to a "Like", and then change the value to something like "Microsoft SQL", and see how long a list you get. It will have more than you want to see most likely, but then you can just add additional lines to the query to filter out the noise that you don't want to see using a "not like", or even a "<>" value. Make sure to use ANDs between them. So example might look something like:


          "Computer" - "Software" - "Add Remove Programs" - "Name" like "Microsoft SQL"

          AND "Computer" - "Software" - "Add Remove Programs" - "Name" not like "Client"

          AND "Computer" - "Software" - "Add Remove Programs" - "Name" not like "Tools"



          This is just an example. You'll probably have to kick this a few times to get it to be just right for your need.



          The alternative to this approach is going to be isolating specific executables and looking for those "Computer" - "Software" - "Package". You can look at the file path, the file version, and all that sort of thing to try to pinpoint a specific item to a specific product. It gets pretty complicated, so if you can get the "Add Remove Programs" to get what you need, its a bit simpler.


          Good luck!

          • 2. Re: Find SQL Server installations

            I have tried many selections and currently trying to find something in packages or to find an executable that exists only at server installations.

            As I have mentioned, there is an installation were only the server configuration is installed (not the SQL Server) but in the add/remove programs it is the SQL Server 2008 mentioned.


            I Hoped that LANDesk could have find something about. I can not believe that only our customers would like to know how many SQL Server installations they have in order to purchase the appropriate licenses.


            I will keep searching..



            • 3. Re: Find SQL Server installations
              Catalysttgj Expert

              So if you have a specific device that you know has this item in the "Add Remove Programs" area, you should be able to go under all devices in the console, select this specific device and right-click and choose inventory to call up the device's inventory into the browser window, and then you should be able to go down into "Software" - "Add Remove Programs", so on and so forth, and you ought to be able to see this item in the device's list. If you don't, then a good question is what do you see there? If by chance you don't see much, or anything at all, then i would start to suspect that something's wrong with inventory of at least the device in general if not all devices, and you'd probably want to troubleshoot that. If you do get plenty of listings of items but still not the specific item, then the question becomes why does not this one particular item get inventoried properly? If its in the Add Remove Programs on the device, then It should be in the list. I'd be concerned with the inventory scanning and whether the results are making it into the core.

              • 4. Re: Find SQL Server installations



                If you check the attachment (in the first post), SQL Server 2008 R2 is installed.

                This appears also in LANDesk inventory.

                The problem is that this IS NOT an SQL installation, only the configuration manager. The same thing appears when applications install on their own SQL installations for local databases.


                I am trying to find specific files, services, registry entries in order to distinguish REAL sql installations.

                For example, I cannot search for Management studio, since it could be installed without Server installation. Or I cannot check for SQL Instanses since some DBs are from SQL Express.


                Not even Microsoft has something to help.

                • 5. Re: Find SQL Server installations
                  btorstei Apprentice

                  My experience is that it  is quite difficult to detect which editions of SQL server that is installed.


                  Best results I have by doing a registry inspection like this


                  <pseduo code>

                  instance_names = List installed SQL Instances [HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL]

                  For each instance in instance_names

                       installed edtion=HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Setup\Edition

                       installed version=HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Setup\Version


                  </pseudo code>


                  Insert "installed version" and edition info  into a registry location and instruct ldiscn32 to pick this up (using ldappl3.ini) and put into custom data in the inventory DB.


                  It would be a great enhancement if the LANDesk agent could pick this up automatically.


                  best regards,


                  • 6. Re: Find SQL Server installations
                    Catalysttgj Expert

                    Okay, I see what you have going on now. You can not rely on add remove programs only as its essentially not enough information to identify a server install from just a client tool install. Sorry, I got confused on that. We're not running 2008 on our database, but we do have server 2005.

                    The executable for 2005 is "sqlservr.exe" I also see "SqlWb.exe" running as a process on our DB as well. There are a few others.


                    Try a query in "software" - "packages" - "path" like "sqlservr"


                    Also, put the same column item in the output, so that you'll get the results back and be able to see the exact location of the file.

                    This might reveal some copies of this application that are not actually installed.

                    You'll be able to isolate the ones that are installed by seeing the full path and then deducing from there.

                    I see 101 instances of the file in our environment, but only 42 of them appear under "C:\Program files".

                    Some are in unusualy places. Another thing that can be done is add a usage column value  in there to see if the apps have been used as at all.

                    There's Duration (which is total in seconds), Last Duration, Times Run. These are all in the same area.



                    Hopefully that gets you in the right direction.

                    • 7. Re: Find SQL Server installations
                      Catalysttgj Expert

                      And on the matter of differentiating which SQLservr.exe; you should be able to get the file version itself in the output as well, if you need that also.

                      • 8. Re: Find SQL Server installations

                        It looks like SP3 will have the ability to determine this with under the Computer.Software.Product class.

                        • 9. Re: Find SQL Server installations
                          Catalysttgj Expert

                          There may be improvements with it in SP3, but its already doable now.

                          I can see version of the SQLservr.exe already and can tell by that what installation they have.

                          "Version" under "Software" - "Packages" works pretty well for this.

                          • 10. Re: Find SQL Server installations
                            alankass Rookie

                            This query works for me in version 9.6:


                            Query Name: MS SQL on Servers


                            "Computer"."Software"."Add or Remove Programs"."Program"."Name"  Like  "SQL Server"

                            "Computer"."Type"  =  "Server"


                            Use these columns for display:


                            "Computer"."Display Name" Device Name 1

                            "Computer"."Type" Type

                            "Computer"."OS"."Name" OS Name

                            "Computer"."System"."Board Name" Board Name

                            "Computer"."System"."Serial Number" Serial Number

                            "Computer"."Network"."TCPIP"."Address" Address

                            "Computer"."Primary Owner" Primary Owner

                            "Computer"."Full Name" Full Name

                            "Computer"."Network"."TCPIP"."Domain" Domain

                            "Computer"."Software"."Add or Remove Programs"."Program"."Name" Name

                            "Computer"."Software"."Add or Remove Programs"."Program"."Version" Version