amp_audit.sql

SELECT [amp].[Name] AS [Antimalware_Policy],
       [collection].[CollectionName] AS [Deployed_To],
       [amps].[PropertyName],
       [col].[query]('.').value('.', 'nvarchar(max)') AS [Property]
       FROM [vSMS_AntimalwareConfig] [amps]
            RIGHT JOIN [vSMS_AntimalwareSettings] [amp] ON [amp].[ID] = [amps].[SettingsID]
            LEFT JOIN [vSMS_AmPolicySummary] [collection] ON [amp].[ID] = [collection].[ClientSettingsID]
            CROSS APPLY [XmlValue].[nodes]('/StringArrayXML/Value') AS [Tbl]([col])
       WHERE [amps].[XmlValue].value('(/StringArrayXML//Value/node())[1]', 'nvarchar(max)') IS NOT NULL
AND [amps].[xmlvalue] IS NOT NULL