Clean up Newly Discovered / Undefined Software view in the Software Catalog

Products

• Software Management Solution

Issue/Introduction

The Software Catalog can become large and cumbersome to use.

Cause

Large amounts of software that may not need to be evaluated or no longer exist in the environment are part of the Newly discovered/undefined software list

Resolution

The following query can be run through an SQL Server Task or other means to help move software to the unmanaged section.

This script will move software from the Newly discovered/undefined software list to Unmanaged software if there are no computers in the environment that currently have it installed.  It will also move the software back if it is reported as installed at a future date. It is important to put this on a recurring schedule because of the fact that software can move both directions.

DECLARE @status as nvarchar (20)          = 'Active'--% = Any Status, Active = Only Active
DECLARE @ismanaged as nvarchar (20)       = '1'--1 = Managed, 0 = Non Managed, % = all

/*
      Set the state of Newly Discovered to Non Managed if there are no
      Active managed computers with the Component currently installed
*/
UPDATE Inv_Software_Component_State
SET IsManaged = 2
FROM vRM_Software_Component sc
LEFT JOIN 
      (
            SELECT distinct iis._SoftwareComponentGuid
            FROM Inv_InstalledSoftware iis 
                  JOIN vFixedAssetResourceStatus c ON c.Guid = iis._ResourceGuid
                        AND c.Status like @status
            WHERE iis.InstallFlag = 1
                  AND c.IsManaged like @ismanaged
      ) iis ON iis._SoftwareComponentGuid = sc.Guid
WHERE Inv_Software_Component_State.IsManaged = 0
AND iis._SoftwareComponentGuid IS NULL
and sc.Guid = Inv_Software_Component_State._ResourceGuid

/*
      If software that was set to non managed is installed in the environment again,
      set it back to Newly discovered so it can be evaluated.
*/
UPDATE Inv_Software_Component_State
SET IsManaged = 0
FROM vRM_Software_Component sc
JOIN 
      (
            SELECT distinct iis._SoftwareComponentGuid
            FROM Inv_InstalledSoftware iis 
                  JOIN vFixedAssetResourceStatus c ON c.Guid = iis._ResourceGuid
                        AND c.Status like @status
            WHERE iis.InstallFlag = 1
                  AND c.IsManaged like @ismanaged
      ) iis ON iis._SoftwareComponentGuid = sc.Guid
WHERE Inv_Software_Component_State.IsManaged = 2
and sc.Guid = Inv_Software_Component_State._ResourceGuid