Ci-dessous, une série de requêtes SQL sur les déploiements, les assignations et les mise a jours.
/**** QUERIES: DEPLOYMENTS,ASSIGNMENTS,UPDATES ****/
--- LIST OF DEPLOYMENTS NAME
SELECT
--DeploymentID = cia.Assignment_UniqueID,
DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
FROM [dbo].[vCI_CIAssignments] CIA
LEFT JOIN [dbo].[vCI_AssignmentTargetedGroups] ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
WHERE CIA.AssignmentType in (1,5)
--- LIST OF ASSIGNMENTS NAME WITH RELATED DEPLOYMENTS NAME
SELECT
--AssignmentID,
CIA.AssignmentName,
--,DEPLOY.DeploymentID,
DEPLOY.DeploymentName
FROM v_CIAssignment CIA
INNER JOIN (
SELECT DeploymentID = cia.Assignment_UniqueID
, DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
FROM [dbo].[vCI_CIAssignments] CIA
LEFT JOIN[dbo].[vCI_AssignmentTargetedGroups] ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
WHERE CIA.AssignmentType in (1,5)
) DEPLOY on DEPLOY.DeploymentID = CIA.Assignment_UniqueID
---- LIST OF UPDATES
SELECT DISTINCT
--CI_UniqueID,
DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
,DateCreated
, (CASE ui.Severity
WHEN 0 THEN 'NONE'
WHEN 2 THEN 'LOW'
WHEN 6 THEN 'MODERATE'
WHEN 8 THEN 'IMPORTANT'
WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
FROM v_UpdateInfo UI
ORDER BY DateCreated DESC
---- LIST OF UPDATES AND RELATED ASSIGNMENTS NAME
SELECT DISTINCT
--CI_UniqueID,
DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
,DateCreated
,(CASE ui.Severity
WHEN 0 THEN 'NONE'
WHEN 2 THEN 'LOW'
WHEN 6 THEN 'MODERATE'
WHEN 8 THEN 'IMPORTANT'
WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
,CIA.AssignmentName
FROM v_CIAssignment CIA
INNER JOIN v_CIAssignmentToCI ATC on ATC.AssignmentID=CIA.AssignmentID
INNER JOIN v_UpdateInfo UI on UI.CI_ID=ATC.CI_ID
ORDER BY DateCreated DESC
---- LIST OF UPDATES AND RELATED DEPLOYMENTS NAME
SELECT
--CI_UniqueID,
DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
,UI.DateCreated
,(CASE ui.Severity
WHEN 0 THEN 'NONE'
WHEN 2 THEN 'LOW'
WHEN 6 THEN 'MODERATE'
WHEN 8 THEN 'IMPORTANT'
WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
,DEPLOY.DeploymentName
FROM v_CIAssignment CIA
INNER JOIN v_CIAssignmentToCI ATC on ATC.AssignmentID=CIA.AssignmentID
INNER JOIN v_UpdateInfo UI on UI.CI_ID=ATC.CI_ID
INNER JOIN (
SELECT DeploymentID = cia.Assignment_UniqueID
, DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
FROM [dbo].[vCI_CIAssignments] CIA
LEFT JOIN [dbo].[vCI_AssignmentTargetedGroups] ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
WHERE CIA.AssignmentType in (1,5)
) DEPLOY on DEPLOY.DeploymentID = CIA.Assignment_UniqueID
ORDER BY DateCreated DESC