SCCM Reporter

  • SCCM Reporter is a Reporting Tool for SCCM SQL Databases.
    It resolves the hierarchical structure of Collections and included Collections stored in a relational SCCM SQL Database.
    It includes Queries for Applications and Deploymenttypes where Properties are stored in XML Format.

  • SCCM Reporter is a Reporting Tool to generate Standard, Special and Custom Reports.

  • SCCM Reporter Standard Reports:
    - All Applications
    - All Collections
    - All Content Source
    - All Deployments
    - All Deployments Status
    - All Network Printers
    - All Software Metering Results
    - All Software Metering Rules
    - All Systems
    - All System Malware Threats
    - Collections Membership
    - Custom Reports

  • Under all Categories you may add/edit/delete Custom Queries.

  • Under All Applications you find this Special Reports:
    - All Applications used in Dependencies
    - All Applications with Owner (XML Properties)
    - All Applications with Requirements (XML Properties)
    - All Applications with Supersedence

  • Under All Collections and Members you may add Special Reports:
    #0 Details Network
    #1 Details ClientVersion
    #2 Details OperatingSystem
    #3 Details Installed Software
    #4 Details Errors

  • Under All Systems you find this Special Reports (as samples for your own Custom Queries):
    - All Systems Diskspace less 5GB
    - All Systems with Adobe Acrobat Reader
    - All Systems with Microsoft Office 2013
    - All Systems with Microsoft Office 2016
    - All Systems with Microsoft Office 2019
    - All Systems with Microsoft Office 2021
    - All Systems with VLC Media Player

© Coopware 2022 (http://coopware.in2.info)


Installation

Manual Installation

Copy the followig files into a directiory of your choice e.g. "C:\Program Files\SCCM Reporter"

    sccmrepo.exe | main executable
sccmrepo.chm | help file
SCCM_ERROR_CODES.txt | Error Descriptions for Devices in collections

If you put the files from the table on an USB stick SCCM Reporter will be a portable tool.
You may also copy the files on a network share and link sccmview.exe to the start menu of multiple computers.

Manual Configuration

    Before you start choose a Connect String for your SQL SCCM Database:

  1. There are 3 Options available:
    • ADO: Server=tcp:sqlsrv.org.net; Database=YourDB; Integrated Security=true
    • OLE: Provider=sqloledb; Server=sqlsrv.org.net; Database=YourDB; Integrated Security=SSPI
    • ODBC: Use [image] in Toolbar to define an ODBC User DSN:
      - Enter a name for the DSN, a description and the name of your SCCM SQL Server.
      - Use Windows Authentication and set the Standard DB to the Name of your SCCM SQL Database.
      - Specify the Connect String: DSN= in the SCCM Reporter Settings.
      - For the ODBC Connection you may alternatively import a Registry excerpt:
      REGEDIT4
      // Set your SCCM SQL Server and then use this ODBC USER DSN in SCCM Reporter
      [HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\sccm.org.net]
      "Driver"="C:\\Windows\\system32\\SQLSRV32.dll"
      "Description"="sccm.org.net" // Your SCCM Server
      "Database"="CM_DB" // Your SCCM SQL DATABASE
      "Trusted_Connection"="Yes"

  2. Root Directory may be changed to a valid path of your choice.


How to use SCCM Reporter

  1. Define a Custom Query
  2. Define a Custom Query under All Collections
  3. Get Collection Membership for a Device
  4. Verbose Logging

1.Define a Custom Query

Choose a TreeView Item right click on Add query.
Fill in the form beside:

  • Choose a Name for your custom query.
  • Path will automatically be completet.
  • Include Collection and Exclude Collection are locked since this is only available for the next section.
  • Adapt the SQL Query to your intention.
    This requieres a deeper understanding of SQL and the SCCM SQL schema.
    Refer to the table description e.g. v_R_System
  • Choose a Report View (sorted, unsorted or table) and press the Save settings button.

2.Define a Custom Query under All Collections

On All Collections in the TreeView right click and choose Add query.
Fill in the form:

  • Click in the Edit field Include Collections and WAIT ...
  • A List View with available Collections will be shown.
  • Select Collections you want to include in your query.
  • Name and Path will automatically be populated
    but you can change them if you like.
  • Choose a Report View (sorted, unsorted or table) and press the Save settings button.

3.Get Collection Membership for a Device

  • On Collection Memberships in the TreeView right click and choose Edit query then copy the SQL Code.
  • On Collection Memberships in the TreeView right click and choose Add query then fill in the SQL Code
    and choose a Name (e.g. TEST-PC)
  • Now insert your Device Name in the SQL Code and confirm with OK:

4.Verbose Logging

  • Standard Logfile: %TEMP%\sccmrepo.log
  • Run sccmrepo.exe /lvrb=1 to log more details (same path as sccmrepo.exe).
  • Run sccmrepo.exe /lvrb=2 to log deep details (same path as sccmrepo.exe).


Graphical user interface (GUI)

  1. Menu and Shortcuts
  2. Toolbar
  3. Configuration

1. Menu and Shortcuts

  • Export Report Configuration
  • Import Report Configuration

  • F1 ... Show Help
  • F2 ... Show Info

2. Toolbar

  • Create a new ODBC User DSN to yor SCCM SQL Server
  • Run SQL Query for selected TreeView Item ... Refreshes the current SQL Query for the Report View
  • Refresh Collection Membership for included Collections ...

3. Configuration

  • SCCM Reporter stores Settings in the Windows Registry:
    HKEY_CURRENT_USER\Software\Coopware\SCCM Reporter
  • Export and Import of this Registry Key is available in
    Menu and Shortcuts