How to Create a Power BI Report using Configuration Manager Data for Devices Pending a Reboot

Estimated reading time: 3 minutes

Creating a query for Power BI from the System Center Configuration Manager (SCCM) database to identify devices pending a reboot involves several steps. Below is a detailed approach, including the SQL query to extract the relevant data.

1. Understand the SCCM Database Structure

The SCCM database contains several tables and views. Key tables/views for this query include:

• v_R_System: Contains system information.
• v_GS_PC_BIOS: Contains BIOS information, which includes reboot status.

2. Query to Identify Devices Pending a Reboot

Devices pending a reboot are often identified by specific status messages or flags within the SCCM database. You might need to join multiple tables to get comprehensive information.

Here’s a sample SQL query to retrieve devices that are pending a reboot:

SELECT 
    rs.Name0 AS 'Device Name',
    rs.User_Name0 AS 'User Name',
    bios.RebootPending AS 'Reboot Pending',
    rs.Last_Logon_Timestamp0 AS 'Last Logon Time',
    rs.Creation_Date0 AS 'Creation Date'
FROM 
    v_R_System AS rs
JOIN 
    v_GS_PC_BIOS AS bios
    ON rs.ResourceID = bios.ResourceID
WHERE 
    bios.RebootPending = 1
ORDER BY 
    rs.Name0;

3. Explanation of the Query

• SELECT Clause: Specifies the columns to be retrieved.
  • rs.Name0: Device name.
  • rs.User_Name0: User associated with the device.
  • bios.RebootPending: Flag indicating if a reboot is pending.
  • rs.Last_Logon_Timestamp0: Last logon time for the device.
  • rs.Creation_Date0: Date the device was added to the database.
• FROM Clause: Specifies the tables/views to retrieve the data from.
  • v_R_System (aliased as rs): Main system table/view.
  • v_GS_PC_BIOS (aliased as bios): BIOS information, which includes the RebootPending flag.
• JOIN Clause: Combines rows from v_R_System and v_GS_PC_BIOS based on a common column, ResourceID.
• WHERE Clause: Filters the results to only include devices where the RebootPending flag is set to 1.
• ORDER BY Clause: Orders the results by device name.

4. Importing the Query into Power BI

To use this query in Power BI:

1. Open Power BI Desktop.
2. Get Data: Click on Home > Get Data > SQL Server.
3. Enter Server Details: Input your SCCM database server name and database name.
4. Advanced Options: Paste the SQL query into the SQL statement box.
5. Load Data: Click OK to load the data into Power BI.

5. Visualizing Data in Power BI

Once the data is loaded, you can create various visualizations:

• Table: Display the list of devices pending a reboot.
• Charts: Create charts to visualize the number of devices pending a reboot over time or by user.

Additional Considerations

• Security: Ensure you have the necessary permissions to access the SCCM database.
• Performance: Large SCCM databases can slow down queries; consider optimizing your SQL or using indexed views.

By following these steps, you should be able to create a useful Power BI report to monitor devices pending a reboot from your SCCM database.