Automating a SQL Report in MECM

🚀 Automating a SQL Report in MECM

Automating a SQL report in Microsoft Endpoint Configuration Manager (MECM) means setting it up to run on a schedule and deliver results—typically via email or file share—using SQL Server Reporting Services (SSRS). Here’s how to do it from start to finish:

🛠️ Step 1: Create the Report in SSRS

You’ll need to use SQL Server Report Builder or Visual Studio to design the report.

1. Open Report Builder (you can launch it from the MECM console or directly).
2. Choose New Report > Table or Matrix Wizard.
3. Connect to your MECM database (usually named CM_<SiteCode>).
4. Paste your SQL query (e.g., the Windows 11 24H2 report).
5. Design the layout—group by collection, sort by computer name.
6. Save the report to a folder in SSRS (e.g., Custom Reports).

📦 Step 2: Add the Report to MECM Console

1. In the MECM console, go to Monitoring > Reporting > Reports.
2. Right-click and choose New > Report.
3. Point to the SSRS report you created.
4. Give it a name and description.

⏰ Step 3: Schedule the Report via SSRS Subscriptions

1. Open SSRS Report Manager (typically at http://<ReportServer>/Reports).
2. Navigate to your report.
3. Click Manage > Subscriptions.
4. Choose New Subscription:
   • Delivery method: Email or file share.
   • Schedule: Daily, weekly, or custom.
   • Format: PDF, Excel, CSV, etc.
   • Recipients: Add email addresses (requires SMTP setup).

📨 If email delivery isn’t working, check SSRS configuration for SMTP settings in ReportingServicesConfig.

🔐 Step 4: Set Permissions

Make sure:

• The MECM service account has access to the report.
• Users receiving the report have read permissions on the SSRS folder.

🧭 Optional: Use PowerShell for Advanced Automation

If you want to go beyond SSRS, you can use PowerShell to:

• Query the MECM database.
• Export results to CSV or Excel.
• Email the file using Send-MailMessage.