Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This sample demonstrates the changes made to the SCRM 2006 stored procedure SCRS_SWUStatusSpecificUpdateDataset in How to Add a Dataset Column in an SCRM Report. This customized stored procedure is based on SCRS_SWUStatusSpecificUpdate, which is in the SCRM 2006 SystemCenterPresentation database.
Requirements
SCRM 2006
Example
SCRS_SWUStatusSpecificUpdate is changed to add the SMS site code to the stored procedure's result set.
CREATE PROCEDURE [dbo].[SCRS_SWUStatusSpecificUpdate_CUSTOM]
(
--Declaration and initialization of the input parameters for this stored procedures.
@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>'
)
AS
BEGIN
SET NOCOUNT ON -- Prevent number of rows affected being returned as part of results.
/*
||
|| Declare local variables for dates and setting 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 ((@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 Dataset Column in an SCRM Report
How to Create an SCRM Stored Procedure