Share via


Code Sample: SCRS_SWUStatusSpecificUpdate_CUSTOM SCRM Stored Procedure

This sample demonstrates the changes made to the SCRM 2006 stored procedure SCRS_SWUStatusSpecificUpdate in How to Add a New Filter to an SCRM Report. SCRS_SWUStatusSpecificUpdate is in the SCRM 2006 SystemCenterPresentation database.

Requirements

SCRM 2006

Example

SCRS_SWUStatusSpecificUpdate is changed to add the SMS site code as an input parameter and also add it to the stored procedure's result set.

CREATE    PROCEDURE [dbo].[SCRS_SWUStatusSpecificUpdate_CUSTOM]        
(    
 --Declaration and initialization of the input parameters for this stored procedure.      
 @ScanType nvarchar(255) = '<ALL>',  
 @Bulletin nvarchar(64),  
 @Qnumber nvarchar(64),  
 @Title nvarchar(255) ,    
 @ComputerType  VARCHAR(255)='<ALL>',      
 @HostType  NVARCHAR(255)='<BOTH>',      
 @Domain   NVARCHAR(255)='<ALL>',      
 @ComputerOwner  NVARCHAR(256)='<ALL>',      
 @Costcenter  NVARCHAR(16)='<ALL>',      
 @Manager  NVARCHAR(64)='<ALL>',  
 @CollectionName NVARCHAR(510)='<ALL>',        
 @Location  NVARCHAR(32)='<ALL>',      
 @LocationParameter NVARCHAR(128)='<ALL>',
 @SMSSiteCode NVARCHAR(255)='<ALL>'    
)        
AS        
BEGIN     
SET NOCOUNT ON     -- Prevent number of rows affected being returned as part of results.        
/*            
||            
|| Declare local variables for dates and seting values to default.           
||            
*/            
DECLARE  @Building NVARCHAR(15)        
SET  @Building = 'Building'        
DECLARE         @Campus  NVARCHAR(15)        
SET  @Campus  = 'Campus'        
DECLARE         @City  NVARCHAR(15)        
SET  @City  = 'City'        
DECLARE         @Country NVARCHAR(15)        
SET  @Country = 'Country'        
DECLARE         @Floor  NVARCHAR(15)        
SET  @Floor  = 'Floor'        
DECLARE         @Office  NVARCHAR(15)        
SET  @Office  = 'Office'        
DECLARE         @Rack  NVARCHAR(15)        
SET  @Rack  = 'Rack'        
DECLARE         @Region  NVARCHAR(15)        
SET  @Region  = 'Region'        
DECLARE         @StateProvince NVARCHAR(15)        
SET  @StateProvince = 'State/Province'        
DECLARE         @WorldRegion NVARCHAR(15)        
SET  @WorldRegion = 'World Region'        
DECLARE         @PostalCode NVARCHAR(15)        
SET  @PostalCode = 'Postal Code'        
        
        
DECLARE @VAR_NONE NVARCHAR(10)        
DECLARE @VAR_ALL NVARCHAR(50)        
DECLARE @VAR_BOTH NVARCHAR(50)        
    
        
SET @VAR_NONE = '<NONE>'        
SET @VAR_ALL='<ALL>'        
SET @VAR_BOTH='<BOTH>'        
       
    
       
 ----------------------------------------------------------------------------  
select      
    node.ComputerFullNTName as ComputerFullNTName,         
    Sum(isnull(swufact.SoftwareUpdateApplicable,0)) SoftwareUpdateApplicable,    
    Sum(isnull(convert(int, status.IsInstalled),0)) as SoftwareUpdateInstalled,
    MAX(SiteCode) as SMSSiteCode
into #temp1  
   from     
    scrs_softwareupdate_fact_view swufact    
    inner join scrs_statusmessage_dimension_view status on status.StatusMessageKey = swufact.SoftwareUpdateStatusKey    
    inner join scrs_softwareupdate_dimension_view swudimension   on swudimension.SoftwareUpdateKey = swufact.SoftwareUpdateKey    
    inner join scrs_managednode_dimension_view node on node.NodeKey = swufact.NodeKey    
    inner join scrs_SMSSite_Dimension_View sms on swufact.SMSSiteKey = sms.SMSSiteKey
   where     
    swufact.SoftwareUpdateEndDateKey is null    
    AND  (@ScanType = @VAR_ALL OR softwareupdateType = @ScanType)     
    AND  (SoftwareUpdateTitle = @Title)     
    AND  (SoftwareUpdateQNumbers = @Qnumber)     
    AND  (SoftwareUpdateBulletinID = @Bulletin)    
   group by ComputerFullNTName   
  
--fn_SWUComplianceStateByBulletinQNTitle(@ScanType,@Bulletin,@Qnumber,@Title)     
 SELECT     
  MNDIM.ComputerFullNTName,    
  case when SWU.ComputerFullNTName is null then 2    
  when SoftwareUpdateApplicable<>SoftwareUpdateInstalled then 1 -- [NonCompliant/ Atleast one of the update is required but not installed]    
  when SoftwareUpdateInstalled > 0 then 3 --[Compliant - All required updates are installed]      
  else 4 --[Complaint - None of the updates are required]    
  end as [CompliantStatus],
  SWU.SMSSiteCode as SMSSiteCode 
 INTO #temp2  
 FROM     
   SCRS_managednode_dimension_latest_view MNDIM     
      LEFT OUTER JOIN #temp1 SWU    
     on SWU.ComputerFullNTName = MNDIM.ComputerFullNTName          
  
create clustered index #ix_temp1 on #temp2(computerfullntname)  
create index #ix_temp2 on #temp2(compliantstatus)  
----------------------------------------------------------------------------  
  -- Selecting the required fields to display          
  SELECT       
  CollectionName as CollectionName,  
  --COUNT(DISTINCT CMP.ComputerFullNTName) AS Total,    
  SUM(1) AS Total,    
  SUM(CASE WHEN CMP.CompliantStatus=3 THEN 1 ELSE 0 END) AS UpdateReqandInstalled,        
  SUM(CASE WHEN CMP.CompliantStatus=1 THEN 1 ELSE 0 END) AS UpdateReqandNotInstalled,        
  SUM(CASE WHEN CMP.CompliantStatus=4 THEN 1 ELSE 0 END) AS UpdateNotReq,        
  SUM(CASE WHEN CMP.CompliantStatus=2 THEN 1 ELSE 0 END) AS UnknownStatus,
  MAX(CMP.SMSSiteCode) as SMSSiteCode
  INTO #temp3      
  FROM [dbo].[SCRS_ManagedNode_DIMENSION_Latest_View] MNDIM        
  INNER JOIN   #temp2 CMP  
 --dbo.fn_SWUComplianceStateByBulletinQNTitle(@ScanType,@Bulletin,@Qnumber,@Title) CMP     
 ON CMP.ComputerFullNTName = MNDIM.ComputerFullNTName      
  INNER JOIN [dbo].[SCRS_NodeLocationAndAssociation_FACT_Latest_View] NLAFCT      
 ON NLAFCT.ComputerFullNTName=MNDIM.ComputerFullNTName    
  INNER JOIN dbo.SCRS_CollectionMemberNode_Fact_Latest_View CMNF     
 ON CMNF.ComputerFullNTName = MNDIM.ComputerFullNTName      
  WHERE         
   (@CollectionName=@VAR_ALL OR CMNF.CollectionNameID=@CollectionName)    
 AND    
    (@Domain = @VAR_ALL  OR MNDIM.ComputerNTDomainname = @Domain)      
  AND (@ComputerType = @VAR_ALL OR MNDIM.ComputerType = @ComputerType)      
  AND (@HostType=@VAR_BOTH OR (@HostType='Virtual' AND MNDIM.Virtual=1) OR (@HostType='Physical' AND MNDIM.Virtual=0))      
  AND    
  (@CostCenter = @VAR_ALL OR NLAFCT.Costcenter = @CostCenter)      
  AND (@Manager = @VAR_ALL OR NLAFCT.Manager = @Manager)        
  AND (@ComputerOwner=@VAR_ALL OR NLAFCT.FullName = @ComputerOwner)  
  AND (@SMSSiteCode=@VAR_ALL OR CMP.SMSSiteCode = @SMSSiteCode)    
  AND ((@Location=@VAR_ALL) OR --When User does Not Select Location      
  --When User Selects Locations      
  (((@Location=@Building)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationBuilding = @LocationParameter))      
  OR ((@Location=@Campus)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCampus = @LocationParameter))      
  OR ((@Location=@City)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCity = @LocationParameter))      
  OR ((@Location=@Country)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationCountry = @LocationParameter))      
  OR ((@Location=@Floor)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationFloor = @LocationParameter))      
  OR ((@Location=@Office)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationOffice = @LocationParameter))      
  OR ((@Location=@Rack)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationRack = @LocationParameter))      
  OR ((@Location=@Region)  AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationRegion = @LocationParameter))      
  OR ((@Location=@StateProvince) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationStateProvince = @LocationParameter))      
  OR ((@Location=@WorldRegion) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationWorldRegion = @LocationParameter))      
  OR ((@Location=@PostalCode) AND (@LocationParameter = @VAR_ALL OR NLAFCT.LocationPostalCode = @LocationParameter))      
  ))     
 GROUP BY      
   CollectionName  
  
---- Order the data.  
select * from #temp3 ORDER BY CollectionName,Total  
  
if object_id('tempdb..#temp1') is not null  
  drop table #temp1  
if object_id('tempdb..#temp2') is not null  
  drop table #temp2  
if object_id('tempdb..#temp3') is not null  
  drop table #temp3  
  
END

See Also

Tasks

How to Add a New Filter to an SCRM Report
How to Create an SCRM Stored Procedure

Other Resources

SCRM 2006 SMS Views
SCRM 2006 Stored Procedures