Snow Inventory SQL query

Snow-good asked a question.

January 23, 2019 at 8:00 AM

Hello all.

I have had a search on the forum to see if there is a solution to this but haven't found one. Apologies if I'm covering old ground here.

I have looked at reporting and know that I can list ALL applications against a single device. But what I would like to do is simply return a count of how many applications are on a device. We are looking at rolling out windows 10 and I know that our current windows 7 standard build has about 40-45 applications, therefore, if I can list the data and sort it, i will be able to identify the "Quick wins" the devices/applications that will not require a new certification and tinkering for windows 10.

I know that this data should be really easy to extract using SQL queries.. What I don't know is, which tables I should be looking at and how would I formulate the SQL query.

I'd really appreciate a steer here.

Regards

Jon

AxelLa
7 years ago
Hello Jon, how about this as a first idea? SELECT ComputerID, COUNT(ApplicationID) AS APPS FROM [SnowLicenseManager].[dbo].[tblComputerApplications] GROUP BY ComputerID; To get the Computername you need to add a join with the table dbo.tblComputer, e.g. like: SELECT dbo.tblComputer.HostName, count(dbo.tblComputerApplications.ApplicationID) AS APPS FROM dbo.tblComputer INNER JOIN dbo.tblComputerApplications ON dbo.tblComputer.ComputerID = dbo.tblComputerApplications.ComputerID group by dbo.tblComputer.HostName; Cheers, Axel
Expand Post
- Snow-good
  7 years ago
  This gave me the info I was after and given me an insight into simple SQL queries. use SnowLicenseManager SELECT dbo.tblComputer.HostName, count(dbo.tblComputerApplications.ApplicationID) AS APPS FROM [dbo].[tblComputer] INNER JOIN dbo.tblComputerApplications ON dbo.tblComputer.ComputerID = dbo.tblComputerApplications.ComputerID group by dbo.tblComputer.HostName; Thanks very much.
  Expand Post
- Oliver Berger (Flexera Software)
  7 years ago
  Hi ‌ pretty close and a good start. Nice! Hi , you could use the "Applications per Computer" Report and the parameter "Installed Application" like "windows 7%". If you want to follow the sql trail, don't forget to join into tblapplications and filter that "ishidden=1" applications are excluded. Otherwise, you cannot get a result, you could verify in the web. You could add more filters, to exclude bundled applications, or applications, where no license is required. Hope that helps! Cheers Oliver
  Expand Post
  - Snow-good
    7 years ago
    Hello ‌, and ‌ Thanks both for taking the time to respond. Oliver, I think that you may have misunderstood my original post. I haven't attempted any SQL yet (I'm not that gen'd up on SQL) What I'm trying to achieve is we have about 5000 devices and a whole host of different user types. Many users use standard build devices just using software ie. Office suite, Chrome, Sophos, Adobe etc.. and nothing really more than that. But then we have other users with all types of applications. We know that the standard build contains about 40 to 45 apps. We also know that the standard build apps work with our Windows 10 build. So by collecting all the number of Standard build devices we can up grade them to our Win10 build, knowing with some certainty that those devices will just work. Quick win. Meanwhile we can start looking into our none-standard apps and get them working in our Windows 10 test environment, before pushing them out to the remaining devices. (I hope that makes sense..) I have got a colleague who will run through the SQL queries with me shortly, so I will post back as to my findings! Thanks again both.
    Expand Post