Is there a way to compare the installed software for two different machines? If a PC fails in our environment and we have to replace it, We have about 44000 different applications in our environment, so we need to know what programs need to be installed.

Computer A List (broken PC) - Computer B List (replacement PC) = The difference between the two.


2 Comments   [ + ] Show Comments

Comments

  • See my post on your other question. The short answer is yes.
  • Thank you for your comment. I will look into making that possible. :)
    • KrummiesMama - send me a DM, we can set up a Zoom session and I will show you how to do this. It is easier to show then to type it up.
Please log in to comment

Answers

0
Software Dodge City!

Supporting that many applications isn't sustainable. Get SNOW installed, do some auditing and get rationalising! At the project before last I was involved in, it became clear pretty quickly that over 70% of the installed software was either never used or used once or twice, normally shortly after installation. Other stuff had no vendor support for modern Windows OSes.
Answered 01/12/2017 by: VBScab
Red Belt

  • I appreciate your answer, but sadly, as a hospital, we are unable to remove applications as they are all pertinent to our patient's safety. I'm sure we can remove some, but I doubt it would make a huge difference in the long run.
Please log in to comment
0
I created a report but I only tested it in mysql workbench. In a text editor, you must use the search and replace function on Computer-A and Computer-B with the two computers you are comparing.

The result column will show BOTH if the software is the same on both computers, otherwise it will show the computer name with the unique software record.

# Search and replace Computer-A and Computer-B with computers to compare

SELECT BOTH.DISPLAY_NAME, BOTH.DISPLAY_VERSION, BOTH.RESULT
FROM (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, 'RESULT' AS 'RESULT'
FROM SOFTWARE S
WHERE (1 in (SELECT 1 FROM MACHINE M, MACHINE_SOFTWARE_JT MS WHERE MS.SOFTWARE_ID = S.ID AND MS.MACHINE_ID = M.ID AND M.NAME = 'Computer-A'))
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) SOFTWARES

JOIN (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, 'BOTH' AS 'RESULT'
FROM SOFTWARE S
WHERE (1 in (SELECT 1 FROM MACHINE M, MACHINE_SOFTWARE_JT MS WHERE MS.SOFTWARE_ID = S.ID AND MS.MACHINE_ID = M.ID AND M.NAME = 'Computer-B'))
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY Software_id, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) BOTH
ON (SOFTWARES.Software_id = BOTH.Software_id AND SOFTWARES.DISPLAY_NAME = BOTH.DISPLAY_NAME AND SOFTWARES.DISPLAY_VERSION = BOTH.DISPLAY_VERSION)

GROUP BY BOTH.DISPLAY_NAME, BOTH.DISPLAY_VERSION, BOTH.RESULT
UNION

SELECT COMP1.DISPLAY_NAME, COMP1.DISPLAY_VERSION, COMP1.RESULT
FROM (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, 'RESULT' AS 'RESULT'
FROM SOFTWARE S
WHERE (1 not in (SELECT 1 FROM MACHINE M, MACHINE_SOFTWARE_JT MS WHERE MS.SOFTWARE_ID = S.ID AND MS.MACHINE_ID = M.ID AND M.NAME = 'Computer-A'))
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) SOFTWARES

JOIN (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME AS 'RESULT'
FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT MS ON (M.ID = MS.MACHINE_ID)
JOIN SOFTWARE S ON (S.ID = MS.SOFTWARE_ID)
WHERE (M.NAME = 'Computer-B')
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY Software_id, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) COMP1
ON (SOFTWARES.Software_id = COMP1.Software_id AND SOFTWARES.DISPLAY_NAME = COMP1.DISPLAY_NAME AND SOFTWARES.DISPLAY_VERSION = COMP1.DISPLAY_VERSION)

GROUP BY COMP1.DISPLAY_NAME, COMP1.DISPLAY_VERSION, COMP1.RESULT
UNION

SELECT COMP2.DISPLAY_NAME, COMP2.DISPLAY_VERSION, COMP2.RESULT
FROM (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, 'RESULT' AS 'RESULT'
FROM SOFTWARE S
WHERE (1 not in (SELECT 1 FROM MACHINE M, MACHINE_SOFTWARE_JT MS WHERE MS.SOFTWARE_ID = S.ID AND MS.MACHINE_ID = M.ID AND M.NAME = 'Computer-B'))
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) SOFTWARES

JOIN (
SELECT S.ID as 'Software_id', S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME AS 'RESULT'
FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT MS ON (M.ID = MS.MACHINE_ID)
JOIN SOFTWARE S ON (S.ID = MS.SOFTWARE_ID)
WHERE (M.NAME = 'Computer-A')
#AND S.DISPLAY_NAME NOT RLIKE ('Hotfix for Microsoft|Security Update for Microsoft|Update for Microsoft|Microsoft Visual C\+\+')
GROUP BY Software_id, S.DISPLAY_NAME, S.DISPLAY_VERSION, RESULT) COMP2
ON (SOFTWARES.Software_id = COMP2.Software_id AND SOFTWARES.DISPLAY_NAME = COMP2.DISPLAY_NAME AND SOFTWARES.DISPLAY_VERSION = COMP2.DISPLAY_VERSION)

GROUP BY COMP2.DISPLAY_NAME, COMP2.DISPLAY_VERSION, COMP2.RESULT
ORDER BY RESULT, DISPLAY_NAME, DISPLAY_VERSION

Answered 01/13/2017 by: flip1001
Second Degree Brown Belt

  • Also, search and replace the word BOTH for SAME. I made a mistake when I posted the code.
    • Thank you!! I will give this a try. :)
Please log in to comment
Answer this question or Comment on this question for clarity