IT Management Suite

Hi, many apologies if this has been answered before, I can't seem to find exactly what I'm after and am unable to adapt what I've seen from search results.

My question seems simple enough, but I imagine the answer is not:

I need a report that will list all computers targeted by a Managed Software Delivery Policy called (for the sake of example) 'policyname1'.  How can this be achieved?

Searching around I found this SQL:

SELECT nri.Name [Policy], ri.Name [Resource Target]
FROM vNonResourceItem nri
JOIN vResourceTargetUses rtu
   ON rtu.ItemGuid = nri.Guid
JOIN vNonResourceItem ri
   ON ri.Guid = rtu.ResourceTargetGuid
WHERE nri.ClassGuid = '2D3A170E-5028-4570-BA0C-3DB775CB8BDE' 
ORDER BY [Policy], [Resource Target]

The above SQL gives me a list of the policies and the target name(s) associated.  I have also found SQL that will give me a list computers for a single target.  But I can't figure out how to list all the computers targeted by a policy, even if there multiple targets applied to that policy, e.g. show me all computers in:  target1 exclude not in target2 include target3

BONUS: it would be brilliant if I could also get a column displaying whether the computer is compliant, non-compliant, or yet to receive policy !!

Many thanks in advance for any pointers.

Regards, Darren.

You could try and use SQP profiler to see what query NS uses when it displays the targets of a Policy using the Computer view.

Andy thanks for the response, but I'm afraid I only have access to the Management Console.

I work with Ian Atkin who has said he will look into it for me but he's tied up right now.  So I just thought I'd ask on Connect in the hope that someone had done this already, or already knew the bits of the database to join.

Cheers, Darren. 

I think:

exec spLoadItemAppliesTo @itemGuid='0080DB39-521D-4373-B244-7B4E55E904F6'

Is run first, using the GUID of the Policy, which returns the list of GUID of all the targets:

ResourceTargetGuid
AF5FFD9B-33DE-47C6-8386-C2E9F86CE219

which then has:

exec spGetResourceTargetsMembershipScopedWithType @targetGuids=N'af5ffd9b-33de-47c6-8386-c2e9f86ce219',@trustees=N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{AB2BC4AA-6123-4FD7-8E37-45C492784FF2},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}',@resourceTypeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F'

run against each GUID returned above.

spLoadItemAppliesTo:

USE [Symantec_CMDB]
GO
/****** Object:  StoredProcedure [dbo].[spLoadItemAppliesTo]    Script Date: 21/10/2015 10:57:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLoadItemAppliesTo]
    @itemGuid uniqueidentifier
AS

SELECT iat.ResourceTargetGuid
FROM ItemAppliesTo iat
INNER JOIN vNonResourceItem vi
ON iat.ResourceTargetGuid = vi.Guid
WHERE iat.ItemGuid = @itemGuid

spGetResourceTargetsMembershipScopedWithType

USE [Symantec_CMDB]
GO
/****** Object:  StoredProcedure [dbo].[spGetResourceTargetsMembershipScopedWithType]    Script Date: 21/10/2015 10:59:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetResourceTargetsMembershipScopedWithType]
    @targetGuids NVARCHAR( MAX ), 
    @trustees NVARCHAR( MAX ),
    @resourceTypeGuid UNIQUEIDENTIFIER = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @targetGuidsLocal NVARCHAR( MAX )
    DECLARE @trusteesLocal NVARCHAR( MAX )

    SET @targetGuidsLocal = @targetGuids
    SET @trusteesLocal = @trustees

    DECLARE @targetGuidsTable TABLE ( [Guid] UNIQUEIDENTIFIER PRIMARY KEY )

    INSERT INTO @targetGuidsTable ( [Guid] )
    SELECT ui FROM fnListToTableUniqueIdentifier( @targetGuidsLocal, DEFAULT )

    IF ( @resourceTypeGuid IS NULL )
    BEGIN
        SELECT DISTINCT rtmc.ResourceTargetGuid, rtmc.ResourceGuid, irt.ResourceTypeGuid, '' AS 'Domain'
        FROM ResourceTargetMembershipCache rtmc
        INNER JOIN @targetGuidsTable tgt
        ON rtmc.ResourceTargetGuid = tgt.[Guid]
        INNER JOIN
        (
            SELECT ResourceGuid
            FROM ScopeMembership
            WHERE ScopeCollectionGuid IN
            (
                SELECT * FROM dbo.fnGetTrusteeScopeCollections( @trustees )
            )
        ) a ON rtmc.ResourceGuid = a.ResourceGuid
        INNER JOIN ItemResourceType irt ON rtmc.ResourceGuid = irt.Guid
    END
    ELSE
    BEGIN
        IF ( @resourceTypeGuid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F' )
        BEGIN
            SELECT DISTINCT rtmc.ResourceTargetGuid, rtmc.ResourceGuid, irt.ResourceTypeGuid, ISNULL( id.Domain, '' ) AS 'Domain'
            FROM ResourceTargetMembershipCache rtmc
            INNER JOIN @targetGuidsTable tgt
            ON rtmc.ResourceTargetGuid = tgt.[Guid]
            INNER JOIN
            (
                SELECT ResourceGuid
                FROM ScopeMembership
                WHERE ScopeCollectionGuid IN
                (
                    SELECT * FROM dbo.fnGetTrusteeScopeCollections( @trustees )
                )
            ) a ON rtmc.ResourceGuid = a.ResourceGuid
            INNER JOIN ItemResourceType irt ON rtmc.ResourceGuid = irt.Guid
            INNER JOIN ResourceTypeHierarchy rth ON irt.ResourceTypeGuid = rth.ResourceTypeGuid
            LEFT JOIN Inv_AeX_AC_Identification id ON irt.Guid = id._ResourceGuid
            WHERE rth.BaseResourceTypeGuid = @resourceTypeGuid
        END
        ELSE
        BEGIN
            IF ( @resourceTypeGuid = 'FD864F19-4437-4a4f-8709-58EB5E3AE0A4' )
            BEGIN
                SELECT DISTINCT rtmc.ResourceTargetGuid, rtmc.ResourceGuid, irt.ResourceTypeGuid, ISNULL( id.Domain, '' ) AS 'Domain'
                FROM ResourceTargetMembershipCache rtmc
                INNER JOIN @targetGuidsTable tgt
                ON rtmc.ResourceTargetGuid = tgt.[Guid]
                INNER JOIN
                (
                    SELECT ResourceGuid
                    FROM ScopeMembership
                    WHERE ScopeCollectionGuid IN
                    (
                        SELECT * FROM dbo.fnGetTrusteeScopeCollections( @trustees )
                    )
                ) a ON rtmc.ResourceGuid = a.ResourceGuid
                INNER JOIN ItemResourceType irt ON rtmc.ResourceGuid = irt.Guid
                INNER JOIN ResourceTypeHierarchy rth ON irt.ResourceTypeGuid = rth.ResourceTypeGuid
                LEFT JOIN Inv_Global_Windows_Users id ON irt.Guid = id._ResourceGuid
                WHERE rth.BaseResourceTypeGuid = @resourceTypeGuid
            END
            ELSE
            BEGIN
                SELECT DISTINCT rtmc.ResourceTargetGuid, rtmc.ResourceGuid, irt.ResourceTypeGuid, '' AS 'Domain'
                FROM ResourceTargetMembershipCache rtmc
                INNER JOIN @targetGuidsTable tgt
                ON rtmc.ResourceTargetGuid = tgt.[Guid]
                INNER JOIN
                (
                    SELECT ResourceGuid
                    FROM ScopeMembership
                    WHERE ScopeCollectionGuid IN
                    (
                        SELECT * FROM dbo.fnGetTrusteeScopeCollections( @trustees )
                    )
                ) a ON rtmc.ResourceGuid = a.ResourceGuid
                INNER JOIN ItemResourceType irt ON rtmc.ResourceGuid = irt.Guid
                INNER JOIN ResourceTypeHierarchy rth ON irt.ResourceTypeGuid = rth.ResourceTypeGuid
                WHERE rth.BaseResourceTypeGuid = @resourceTypeGuid
            END
        END
    END

END

I think this is simpler than it seems. If you know the Policy GUID, you can simply list all the computers targeted by that policy with the following T-SQL,

select vri.Name,vri.Guid from vPolicyAppliesToResource vpr 
join vResourceItem vri on vpr.ResourceGuid=vri.Guid
where vpr.PolicyGuid='<INSERT_GUID_HERE>'

That will give you all the computers (and their GUIDs) that are affected by a particular software delivery policy.

Kind Regards,
Ian./

 

If you want to check the compliance of your policy you can use the following SQL.

SELECT
	[CS]._ResourceGuid as 'Machine Guid',
	[VC].Name as 'Machine Name',
	[CS].PolicyGuid as 'Policy Guid',
	[IT].Name as 'Policy Name',
	'Status' = 
		CASE [CS].Compliance
			 WHEN 0 THEN 'Noncompliant'
			 WHEN 1 THEN 'Unknown' --First time policy has loaded compliance
			 WHEN 2 THEN 'Warning' --According to Symantec this status isn't used
			 WHEN 3 THEN 'Compliant'
			 WHEN 4 THEN 'Pending Reboot'
			 WHEN 5 THEN 'Pending User Logoff'
		END
FROM [Inv_Policy_Compliance_Status] [CS]
JOIN [VComputer] [VC] on [VC].Guid = [CS]._ResourceGuid
JOIN [Item] [IT] on [IT].Guid = [CS].PolicyGuid
WHERE [IT].Name = 'policyname1'

It shows the resources the policy is targeting as well as their compliance status. I've filtered the policy by the name that you specified in your question however you would be better filtering this on the policy's GUID.

Hope this helps.