ConfigMgr Report for Antimalware Policies

Posted on Updated on

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.

ConsoleAntimalwarePolicies

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

    FindSQLqueryView

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"

GrantSQLview

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. 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

      AntimalwareReportBuilder

    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!

AntimalwareReportResults

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s