- Permet de mettre plusieurs adresse MAC sur une même ligne avec le séparateur ‘;’ afin d’éviter les doublons. Peut être utiliser sur d’autre table (software, IPaddress …).
SELECT DISTINCT v_R_System.Name0 as [MachineName],
STUFF((Select distinct (N’;’ + Mac.MACAddress0) AS [text()] From v_GS_NETWORK_ADAPTER_CONFIGURATION Mac Where v_R_System.Name0 = Mac.DNSHostName0 FOR XML PATH ( »)),1,1, ») AS [MacAddress]
FROM v_R_System INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGURATION ON v_R_System.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
- Permet de tronquer un nom netbios fqdn grâce au séparateur ‘.’
select
Substring(ServerRemoteName,0,CHARINDEX (‘.’,ServerRemoteName,0))
from
SysResList
- Permet de mettre plusieurs information sur une même ligne ET tronquer un nom grâce au séparateur ‘\\\\’. (Requête permettant d’afficher les boundary group et site system associé).
select sys1.Name, sys1.DefaultSiteCode,
(select SUBSTRING(sys2.ServerNALPath, CHARINDEX(‘\\\\’, sys2.ServerNALPath) + 2,
CHARINDEX(‘ »]’, sys2.ServerNALPath) – CHARINDEX(‘\\\\’, sys2.ServerNALPath) – 3 ) +
CASE sys2.Flags WHEN ‘1’ Then ‘ (Slow)’ WHEN ‘0’ THEN » END + ‘; ‘ as ‘data()’
from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID
for XML path( »)) as ‘Site System’,
(select sys4.Value + ‘; ‘ as ‘data()’ from vSMS_BoundaryGroupMembers as sys3
left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID
for XML path( »)) as ‘Boundary’, sys1.ModifiedOn, sys1.ModifiedBy
from vSMS_BoundaryGroup as sys1
- Trouver le package liée a un content (que l’on peut lire dans les logs du client sccm)
select * from CI_ContentPackages where Content_UniqueID like ‘%8363012b-4aaa-4113-bb91-594f2fd51c25%’
- État d’installation d’une application
select
a.MachineName,
a.CollectionID,
a.CollectionName,
b.ApplicationName,
case
WHEN a.ComplianceState = ‘0’ THEN ‘Compliance State Unknown’
WHEN a.ComplianceState = ‘1’ THEN ‘Compliant’
WHEN a.ComplianceState = ‘2’ THEN ‘Non-Compliant’
WHEN a.ComplianceState = ‘4’ THEN ‘Error’
ELSE ‘N/A’
END as ‘ComplianceState’,
case
WHEN a.EnforcementState = ‘1000’ THEN ‘Success’
WHEN a.EnforcementState = ‘1001’ THEN ‘Already Compliant’
WHEN a.EnforcementState = ‘1002’ THEN ‘Simulate Success’
WHEN a.EnforcementState = ‘2000’ THEN ‘In Progress’
WHEN a.EnforcementState = ‘2001’ THEN ‘Waiting for content’
WHEN a.EnforcementState = ‘2002’ THEN ‘Installing’
WHEN a.EnforcementState = ‘2003’ THEN ‘Restart to continue’
WHEN a.EnforcementState = ‘2004’ THEN ‘Waiting for maintenance window’
WHEN a.EnforcementState = ‘2005’ THEN ‘Waiting for schedule’
WHEN a.EnforcementState = ‘2006’ THEN ‘Downloading dependent content’
WHEN a.EnforcementState = ‘2007’ THEN ‘Installing dependent content’
WHEN a.EnforcementState = ‘2008’ THEN ‘Restart to complete’
WHEN a.EnforcementState = ‘2009’ THEN ‘Content Downloaded’
WHEN a.EnforcementState = ‘2010’ THEN ‘Waiting for Update’
WHEN a.EnforcementState = ‘2011’ THEN ‘Waiting for user session reconnect’
WHEN a.EnforcementState = ‘2012’ THEN ‘Waiting for user logoff’
WHEN a.EnforcementState = ‘2013’ THEN ‘Waiting for user logon’
WHEN a.EnforcementState = ‘2014’ THEN ‘Waiting to install’
WHEN a.EnforcementState = ‘2015’ THEN ‘Waiting Retry’
WHEN a.EnforcementState = ‘2016’ THEN ‘Waiting For Presentation Mode’
WHEN a.EnforcementState = ‘2017’ THEN ‘Waiting For Orchestration’
WHEN a.EnforcementState = ‘2018’ THEN ‘Waiting For Network’
WHEN a.EnforcementState = ‘2019’ THEN ‘Pending App-V Virtual Environment Update’
WHEN a.EnforcementState = ‘2020’ THEN ‘Updating App-V Virtual Environment’
WHEN a.EnforcementState = ‘3000’ THEN ‘Requirements not met’
WHEN a.EnforcementState = ‘3001’ THEN ‘Host Platform Not Applicable’
WHEN a.EnforcementState = ‘4000’ THEN ‘Unknown’
WHEN a.EnforcementState = ‘5000’ THEN ‘Deployment failed’
WHEN a.EnforcementState = ‘5001’ THEN ‘Evaluation failed’
WHEN a.EnforcementState = ‘5002’ THEN ‘Deployment failed’
WHEN a.EnforcementState = ‘5003’ THEN ‘Failed to locate content’
WHEN a.EnforcementState = ‘5004’ THEN ‘Dependency installation failed’
WHEN a.EnforcementState = ‘5005’ THEN ‘Failed to download dependent content’
WHEN a.EnforcementState = ‘5006’ THEN ‘Conflicts with another application deployment’
WHEN a.EnforcementState = ‘5007’ THEN ‘Waiting Retry’
WHEN a.EnforcementState = ‘5008’ THEN ‘Failed to uninstall superseded deployment type’
WHEN a.EnforcementState = ‘5009’ THEN ‘Failed to download superseded deployment type’
WHEN a.EnforcementState = ‘5010’ THEN ‘Failed to updating App-V Virtual Environment’
ELSE ‘N/A’
END as ‘LastStateMessage’
from vAppDeploymentAssetDetails a left join v_ApplicationAssignment b
on a.AssignmentID = b.AssignmentID
- Etat de compliance des machines d’une collection vis à vis des KB présent dans la console SCCM (déployé ou non)
declare @usersids as varchar(64)=’Disabled’
DECLARE @lcid AS INT
SET @lcid = dbo.fn_LShortNameToLCID(1033)
SELECT
v_r_system.name0,
UpdateClassification=cls.CategoryInstanceName,
ui.Title,
ui.BulletinID,
ui.ArticleID,
ui.DateRevised,
Installed=COUNT(CASE WHEN css.Status=3 THEN ‘*’ END),
IsRequired=COUNT(CASE WHEN css.Status=2 THEN ‘*’ END),
Deployed=COUNT(CASE WHEN ctm.ResourceID IS NOT NULL THEN ‘*’ END),
WaitingforInstall=COUNT(CASE WHEN css.Status=2 AND ctm.ResourceID IS NOT NULL THEN ‘*’ END),
Missing=COUNT(CASE WHEN css.Status=2 AND ctm.ResourceID IS NULL THEN ‘*’ END)
FROM fn_rbac_UpdateComplianceStatus(@UserSIDs) css
JOIN fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui ON ui.CI_ID=css.CI_ID
JOIN fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) cls ON cls.CI_ID=css.CI_ID AND cls.CategoryTypeName=’UpdateClassification’
LEFT JOIN fn_rbac_CITargetedMachines(@UserSIDs) ctm ON ctm.ResourceID = css.ResourceID AND ctm.CI_ID = css.CI_ID
JOIN v_ClientCollectionMembers coll ON coll.ResourceID = css.ResourceID
LEFT JOIN v_r_system on css.ResourceID=v_R_System.ResourceID
WHERE coll.CollectionID = ‘CAS00CDB’
GROUP BY
v_r_system.name0,
cls.CategoryInstanceName,
ui.Title,
ui.BulletinID,
ui.ArticleID,
ui.DateRevised
ORDER BY v_r_system.name0,ui.Title
