Create a Software Compliance Report that only shows Active MSD Policies

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

Is there a way to create a report that Only shows Managaged Software Delivery (MSD) polices that are Active?  

Environment

8.x

Resolution

Create a new Stored Proc using sp_SWM_GetSoftwareComplianceByManagedDelivery as a template.

New SP name: CREATE PROCEDURE [dbo].[sp_SWM_GetSoftwareComplianceByManagedDelivery_Active]   

ADD the Bold / Italicized syntax below to the previous procedure.  

   INNER JOIN ClassBaseClass [cbc] on [cbc].ClassGuid = [ic].ClassGuid  
       
         LEFT JOIN [String] AS s   
          INNER JOIN dbo.fnGetBaseCultures(@in_Culture) AS bc ON bc.[Culture] = s.[Culture] ON       
           s.[BaseGuid] = vi.[Guid]   
          AND s.[StringRef] = 'item.name'  
          Join ItemActive ia
          on ia.Guid = vi.Guid
  
         WHERE               
                ia.Enabled = 1
                AND cbc.[BaseClassGuid] = '2D3A170E-5028-4570-BA0C-3DB775CB8BDE'   --Managed Delivery Policy  
                AND (@PolicyName   = N'%' OR LOWER(vi.[Name]) LIKE @PolicyName)  
                AND (@CheckDate IS NULL OR vi.[CreatedDate] >= @CheckDate)  
        ) tmp  

 

And then use the new SP in any reports that you create.  

As an Example, here is a quick report using this SP:

DECLARE @v1_TrusteeScope varchar(max)

SELECT  @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{90FCD432-9F7D-4ECD-9542-3542A826AFF9},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{BE6C80DD-151E-4BA1-B9EA-8AEDAF96A5F6},{EBBDE226-1429-4080-816F-EB529CAA63AB}'

EXEC [dbo].[sp_SWM_GetSoftwareComplianceByManagedDelivery_Active]
    @DaysAgo             = NULL,
    @ScopeCollectionGuid = '91c68fcb-1822-e793-b59c-2684e99a64cd',  -- All Computer filter
    @TrusteeScope        = @v1_TrusteeScope