Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Find a list of machines without installed software (With hardware)

Created: 13 May 2014 • Updated: 14 May 2014 | 7 comments
This issue has been solved. See solution.

This query works great to find computers with a software that is NOT installed.

https://www-secure.symantec.com/connect/forums/alt...

SELECT *

FROM vComputer

WHERE Guid NOT IN (SELECT inst._ResourceGuid

                   FROM vRM_Software_Component_Item sci

                   JOIN Inv_InstalledSoftware inst

                      ON inst._SoftwareComponentGuid = sci.Guid

                      AND inst.InstallFlag = 1

                   WHERE LOWER(sci.Name) LIKE '%enterprise%vault%')

I am trying to figure out how to add data from vHWComputerSystem like Model and also user information from vuser.

Is it possible? Thanks in advance!

Operating Systems:

Comments 7 CommentsJump to latest comment

Gavin Sanish's picture

Hl Huseinm,

    let me know what are the columns must be added in both the tables

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
huseinm's picture

Gavin, I would like to see Manufacturer, Model, user display name, username, email, department.

huseinm's picture

Hi Chin, I am trying to create a report of a piece of software that is not installed and the report shoud also show the primary user and also hardware info like manufacturer and model.

Gavin Sanish's picture

Hi Huseinm,

    This query contain all the details and also joined to the query which you have given. Please check and elt me know

SELECT     vc.Name AS Computer_Name, vc.[OS Name] AS Operating_System, pu.[User] AS Primary_User, vu.[Office Telephone] AS Telephone, vu.Department, vu.Email,
                      s.Manufacturer AS [System Manufacturer], s.Model AS [Computer Model], s.[Identifying Number] AS [Serial Number]
FROM         dbo.vComputer AS vc INNER JOIN
                      dbo.Inv_AeX_AC_Primary_User AS pu ON pu._ResourceGuid = vc.Guid LEFT OUTER JOIN
                      dbo.vUser AS vu ON vu.Name = pu.[User] INNER JOIN
                      dbo.vHWComputerSystem AS s ON s._ResourceGuid = vc.Guid
WHERE     (vc.Name LIKE '%') AND (NOT (vc.Guid IN
                          (SELECT     inst._ResourceGuid
                            FROM          dbo.vRM_Software_Component_Item AS sci INNER JOIN
                                                   dbo.Inv_InstalledSoftware AS inst ON inst._SoftwareComponentGuid = sci.Guid AND inst.InstallFlag = 1
                            WHERE      (LOWER(sci.Name) LIKE '%enterprise%vault%'))))

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
SOLUTION
huseinm's picture

Thanks a ton Gavin! This did the trick. I filtered out the models as needed!