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