ConfigMgr Report for Antimalware Policies

Posted on September 15, 2017 Updated on September 6, 2017

Let’s say for a moment that your organization uses SCCM for management of Windows Defender Antivirus (WDAV in Windows 10, Server 2016) or System Center Endpoint Protection (SCEP for legacy platforms).  Currently in SCCM (1706 or older) the only out-of-box mechanism to identify and report upon the antimalware policies being applied to a computer is through the SCCM console, such as in the image below.

What if the organization has a separate team or individual that needs that data – but you don’t want to provide them with the SCCM console?  You give them a report of course!  This quick guide will show you key things to do to obtain that info. The key steps are:

1. Identify the SQL views being referenced by the SCCM console.
2. Grant read permission of the SQL view to the SRSS reporting service account.
3. Create the SRSS report.

Step 1: Identify the SQL views being referenced by the SCCM console.

1. In the SCCM console, open the Antimalware Policies tab on the computer record
2. Open the site server log SMSProv.log (and scroll to the end)
3. Find the correlating “Execute SQL=” query to identify the SQL view(s) being used

   

Step 2: Grant read permission of the SQL view to the SRSS reporting service account.

1. Identify the service account being used by SCCM for SRSS reporting
   Tip: navigate to Administration > Security > Accounts, then locate the account being used for “ConfigMgr Reporting Services Point”
2. Open SQL Management Studio (with a user account that has permissions to modify SQL permissions) and select the SCCM database
3. Run the following GRANT command against the SCCM database

GRANT SELECT ON [dbo].[vSMS_G_SYSTEM_AmPolicyStatus] TO "DOMAIN\user"

Step 3: Create the SRSS report.  First off, there are many different ways that you can design the report.  To mimic what the SCCM console does, I usedan existing report with a selection box for the Computer Name, then just modified the executing query.

1. 1. Used report “Computer information for a specific computer” as an example baseline for selecting the computer name for a variable.
   2. Create a new report (using SQL Report Builder) to mimic the above report with the appropriate Data Source, Data Set(s), and Parameters

      

   3. Modified the SQL query to use the following code

SELECT APS.Name, APS.Priority, APS.LastMessageTime, @variable AS 'Computer Name'
FROM vSMS_G_SYSTEM_AmPolicyStatus as APS
JOIN v_R_System as SYS on APS.MachineID = SYS.ResourceID
WHERE SYS.Name0 = @variable

1. Test execute the report to confirm the results
   Tip: in Report Builder, click the Run button on the Home tab
2. Save, finish, and report!