3 Replies Latest reply on Dec 5, 2018 5:31 AM by phoffmann

    Running Query of all Software Installed?

    tvangwrc Rookie

      Has anyone figure out how to run a query of all the software installed on the computers they managed? I'd like to get a list of all software installed on each of the computers we have.

        • 1. Re: Running Query of all Software Installed?
          phoffmann SupportEmployee

          My first response to this sort of request is really "no - you really don't".


          Allow me to explain & help you to refine your query a bit.


          So - doing the "all software on all devices" thing is reasonably simple in theory. You could choke your SQL Server for a bit (or even your console, if you insist) ... but eventually results should be there.


          The problem is that you'd end up with "un-openable" files.


          To give you some context -- I've been asked (/forced) to "do just this very thing" for a customer who has got some external people in to help them with some software licensing overview stuff. Along came the request "we'd like to know everything on every device".


          Here's the list of issues to contend with:

          • Sheer SIZE of data. Running a "everything on every device"-type report will VERY quickly burst any size limitations that Excel & co can cope with. (It's REALLY easy getting over 3 GB of raw CSV file, and Excel doesn't display more than 1,000,000 (and some change) lines). So that's why it's preferable to "filter" & query the data set more sensibly.
          • "How" you get your data. So you CAN run a report from SQL directly ... but ... when SQL saves to a file, it doesn't encapsulate its strings with ""-s (stupid £$)(*"£$ oversight).

            This causes a problem, as a *LOT* of software vendors / names / etc tend to have a "," in them ... thus breaking CSV files. Yay. You can try to store things as a tab-delimited TXT, but I wouldn't put it beyond certain software packaging tools to have a tab hidden somewhere as well. So - "fun".

            A few workarounds exist (/I've had to come up with), but by and large, I've managed to convince pretty much everyone who has ever requested this sort of thing, that "querying the database" makes the most sense ... as you can refine it ("make a list of stuff I care about from the 'total list' of software"), and filter on certain characteristics (such as "executed, but not part of a registered software package").

            ... there's a bunch of stuff here (and it can get somewhat complicated ... but, that's what I'm trying to explain. The original question at its heart needs more consideration for those very reasons.
          • Different OS'es have data in different places. And they have different concepts of "software"
            • Do you deal with MAC-s and/or LINUX devices? This is important as not only is their software stuff in entirely different tables, but it's also "a lot messier there".

              Case in point - so Windows has the concept of "application suites". So if you install "The Office Suite", there's (usually) breadcrumbs to link "Excel" and "Word" and "Powerpoint" to "Office vesion Y, edition Z" (simplifying somewhat here).

              In Linux you may deploy "OpenOffice" for instance, but what you end up with is a list of 500+ individual packages, with no real concept that "they are part of the Open Office Suite". So ... data paradigms can vary MASSIVELY between operating systems.
            • Depending on what you're after, Windows has 3 separate "data" tracking points to be aware of for instance:
              • Installed Applications (that register in "Add/Remove Programs" (but not everything registers, or UN-registers upon uninstall).
              • Stuff the MSI database knows about (so stuff you'll be able to find with a MSI SID-code in the registry).
              • Executables that DO NOT register with the OS, but are executed (say "Putty.exe" is a common example for instance).
            • ... so there's *a lot" to think about (and narrow down).


          ... hope that helps to give you a few things to think about & refine your requirements from there.


          Your node-count will have quite an impact here too ... running a report for "everything" on 400 nodes is "painful but doable" ... whereas, running that report on 40,000 nodes (for instance) will usually result in an output file that's too large to consume by something that isn't a database engine in the first place.

          2 of 2 people found this helpful
          • 2. Re: Running Query of all Software Installed?
            ldms_4mfe Apprentice

            In addition to Paul's response.


            SQL Query does not have to be anything complicated in SQL Server Manager.

            And also you don't have to juggle with ODBC anymore.


            You only need a user for the database (the best one is a readonly).

            With this you can then simply query a database via Excel.

            Import Data from Database using Native Database Query (Power Query) - Excel


            Here a Query you can use


            SELECT DISTINCT A0.DISPLAYNAME, A0.TYPE, A2.OSTYPE, A3.RELEASEID, A4.SUITENAME, A4.PUBLISHER, A4.INSTALLDATE  FROM Computer A0 (nolock) LEFT OUTER JOIN ScopeComputer A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn LEFT OUTER JOIN Operating_System A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn LEFT OUTER JOIN OSNT A3 (nolock) ON A0.Computer_Idn = A3.Computer_Idn LEFT OUTER JOIN AppSoftwareSuites A4 (nolock) ON A0.Computer_Idn = A4.Computer_Idn  ORDER BY  A0.DISPLAYNAME


            Regards, Marco

            • 3. Re: Running Query of all Software Installed?
              phoffmann SupportEmployee

              Yep - there's many "simple" and "complex" solutions here ... a lot of things depends on what you actually NEED.


              So - clarifications would help .