Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

SQL Report of current installed version VS most recent version

Hello, I am trying to make a SQL report which will show computers that have an old version of a piece of software, along with what the newest version is.  To try and get this to work I am making a SQL query but I must have something wrong because the query keeps hanging.

 

Since this is a work in progress, the code I have so far is not exactly right but it demonstrates the idea:

 

SELECT
Computers.ComputerId as ComputerID
, Computers.Name as ComputerName
,(select value from SystemVariables where name = 'AppVerMozillaFirefox') as CurrentVersion
,applications.Name as ApplicationName
FROM
Computers
INNER JOIN
CollectionComputers ON Computers.ComputerId = CollectionComputers.ComputerId
INNER JOIN
Collections ON CollectionComputers.CollectionId = Collections.CollectionId
Inner join Applications
on computers.ComputerId = Applications.ComputerId
WHERE
Collections.Path = 'Collection Library\Applications\Internet Browsers\Mozilla Firefox\Mozilla Firefox 64-bit\Mozilla Firefox 64-bit (Old)'
AND
CollectionComputers.IsMember = 1
AND
computers.ComputerId = 175

 

The above code will run in a few milliseconds and list the computer along with its applications installed, however if I do the following to try and get all the computers in the collection, it takes a very long time. 

 

SELECT
Computers.ComputerId as ComputerID
, Computers.Name as ComputerName
,(select value from SystemVariables where name = 'AppVerMozillaFirefox') as CurrentVersion
,applications.Name as ApplicationName
FROM
Computers
INNER JOIN
CollectionComputers ON Computers.ComputerId = CollectionComputers.ComputerId
INNER JOIN
Collections ON CollectionComputers.CollectionId = Collections.CollectionId
Inner join Applications
on computers.ComputerId = Applications.ComputerId
WHERE
Collections.Path = 'Collection Library\Applications\Internet Browsers\Mozilla Firefox\Mozilla Firefox 64-bit\Mozilla Firefox 64-bit (Old)'
AND
CollectionComputers.IsMember = 1

 

Is there something I can do to get the correct information?  Ultimately what I am looking for is a report with the following information:

 

Computer, SoftwareName,CurrentInstalledVersion, MostRecentVersion

 

0

Comments

1 comment
Date Votes
  • Update, I think I figured out the issue, unfortunately there is no easy way  to do what I want it seems like.

     

    The issue is that the ~Applications table in the PDQ Inventory database stores the computerID field as text instead of an integer, so when doing the queries above it is trying to do the join on text rather than an integer which is much slower.

     

    To test this, I created a new table called ~Applications2, but made the ComputerID column an integer instead of a text field, and then selected all of the contents of the ~Applications table into it.  After doing this, the query that took a very long time before completed in 400ms.

    0