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!
When attempting to set up and configure the ConfigMgr 2012 site system role for reporting services, a frequently experienced “problem” is that the instance name can be blank/empty in the wizard and thereby unable to proceed with the wizard. This usually occurs when SRS has not been pre-configured properly.
While it is common “knowledge” that the reporting services database needs to be created first, an oft-overlooked step is to use the Reporting Services Configuration Manager to create the virtual directories for IIS. And it is these steps which need to be completed to get you on your way.
- Open Reporting Services Configuration Manager
- Connect to the server/instance
- Click on Web Service URL – make a fake change, such as changing the name of the virtual directory and then putting it back to ReportServer – and clicking Apply. This will then create the new virtual directories.
- Click on Report Manager URL – again, make a change to the name and put back to just Reports – and click Apply to generate the new virtual directories.
- Close configuration manager
- Return to the CM12 site role setup program, click “Verify” for the database connection, and voila the instance is now populated correctly!
We went to install an SRS database on our SCCM 2007 server for FEP 2010, but when attempting to create a new report server db, we were getting error 1807 “System.Data.SqlClient.SqlException: Could not obtain exclusive lock on database ‘model’. Retry the operation later.” It was discovered that the install failed because the Model database had become corrupted and detached. And more unfortunately someone removed backups of the database (lesson learned: do monitor the backups of your system databases!)
Our initial attempts to restore Model was to ‘import’ the model db from another server with the exact same SQL version. While the import was successful and the database was automagically moved into System Databases, the error continued. Knowing that a service pack in effect reinstalls SQL, we upgraded to SQL 2005 SP4. There was still no joy. A proposed solution was to do a cut-over of SCCM to a new server with a clean install of SQL and SCCM. However, in my experience, that would have caused more issues resulting a longer drawn out resolution path. So I wanted to avoid that. Based upon a forum thread somewhere, we had indications that the error was now being caused by Model not having the proper system database ID value. To confirm this, we used a Microsoft support case.
The issue was caused by the `model` db having the incorrect database_id. It was registered as 7, but all system databases must have the specific db_id, with model being ID 3. Which means that model was actually a user database, despite being listed in the System Databases category. To fix the issue, all user databases, model, and msdb were detached. Then attaching them in order model, msdb, user dbs resulted in creating model with ID 3 and msdb with ID 4. The ability to create databases was then fully functional again…