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:
- Identify the SQL views being referenced by the SCCM console.
- Grant read permission of the SQL view to the SRSS reporting service account.
- Create the SRSS report.
Step 1: Identify the SQL views being referenced by the SCCM console.
- In the SCCM console, open the Antimalware Policies tab on the computer record
- Open the site server log SMSProv.log (and scroll to the end)
- 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.
- 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”
- Open SQL Management Studio (with a user account that has permissions to modify SQL permissions) and select the SCCM database
- 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.
- Used report “Computer information for a specific computer” as an example baseline for selecting the computer name for a variable.
- Create a new report (using SQL Report Builder) to mimic the above report with the appropriate Data Source, Data Set(s), and Parameters
- 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
- Test execute the report to confirm the results
Tip: in Report Builder, click the Run button on the Home tab
- Save, finish, and report!
This is the wildest thing I’ve ever seen. My client was describing to me the problem, but I had a hard time believing them until I could see it reproduced for myself. When attempting to add direct memberships to a collection, sometimes it only shows returns a subset of the limiting collection and not the full list. I have found out that this is indeed a known bug/case that is open with the ConfigMgr development team.
When using the direct rule wizard and using a mouse with clicks, it only makes a handful of resources available from the limiting collection.
Then when repeating the steps but using the only a keyboard and buttons, all 1000+ systems became available.
The limiting “parent” collection was a created in a unique way that is new to ConfigMgr 2012. Of that collection, it’s limited to All Systems, but the memberships rules is using the new “include collections”.
What made this “funny” at my client is that literally every time I did a direct membership, it worked for me….and every time the client did it, it failed. We all couldn’t help but laugh because they thought I was somehow messing with them.
Ran into a situation with a remote CM12 console being unable to connect to a site server. According to the local SMSAdminUI.log file:
Transport error; failed to connect, message: 'The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) '\r\n Microsoft.ConfigurationManagement.ManagementProvider.SmsConnectionException \r\n The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)\r\n at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryProcessor.ExecuteQuery (String query, Int32 blockSize, Dictionary`2 contextInformation)
Initial troubleshooting indicated that this was a problem with the Windows Firewall blocking remote administration. However, the firewall was off one the site server. Yet some posts indicated that the ports needed to be allowed, regardless of the firewall being on/off. So I opened up those, but it still couldn’t connect.
I took a step back and went to the basics – could I ping the FQDN? No, I couldn’t! Yahtzee! I could ping the short name just fine, so I just needed to have Windows map the IP to the FQDN. So as a quick fix, I just added an entry into the C:\Windows\System32\drivers\etc\Hosts file – and voila, the remote CM12 console could now connect!