SCCM Power Plan SQL Queries
In one of my customers environments there was a request for a quick review of ConfigMgr SCCM Power Plan settings. This turned out to show us that there were over 20+ power plans in the environment and needed to be reduced. Below is the quick query I came up with for the customer.
— individual systems with power plans and collection they belong to
select
SMS_R_System.Name0 AS [System Name],
V_Collection.Name AS [Collection Name],
__R_MANAGEMENT_CONFIGURATION0.NonPeakPowerPlanName00 AS ‘Non Peak Power Plan Name’,
__R_MANAGEMENT_CONFIGURATION0.PeakPowerPlanName00,
__R_MANAGEMENT_CONFIGURATION0.PowerConfigID00 AS [Collection Power setting Source]
from
vSMS_R_System AS SMS_R_System
INNER JOIN POWER_MANAGEMENT_CONFIGURATION_DATA AS __R_MANAGEMENT_CONFIGURATION0 ON __R_MANAGEMENT_CONFIGURATION0.MachineID = SMS_R_System.ItemKey
Inner JOIN V_Collection on V_Collection.CollectionID = __R_MANAGEMENT_CONFIGURATION0.PowerConfigID00
Order by
SMS_R_System.Name0
— collections with count of systems with power plans
select
V_Collection.Name AS [Collection Name],
__R_MANAGEMENT_CONFIGURATION0.PowerConfigID00 AS [Collection Power setting Source],
count (V_Collection.Name) AS Count,
__R_MANAGEMENT_CONFIGURATION0.NonPeakPowerPlanName00 AS ‘Non Peak Power Plan Name’,
__R_MANAGEMENT_CONFIGURATION0.PeakPowerPlanName00
from
vSMS_R_System AS SMS_R_System
INNER JOIN POWER_MANAGEMENT_CONFIGURATION_DATA AS __R_MANAGEMENT_CONFIGURATION0 ON __R_MANAGEMENT_CONFIGURATION0.MachineID = SMS_R_System.ItemKey
Inner JOIN V_Collection on V_Collection.CollectionID = __R_MANAGEMENT_CONFIGURATION0.PowerConfigID00
Group by
v_Collection.Name,
__R_MANAGEMENT_CONFIGURATION0.NonPeakPowerPlanName00,
__R_MANAGEMENT_CONFIGURATION0.PeakPowerPlanName00,
__R_MANAGEMENT_CONFIGURATION0.PowerConfigID00
In a future blog post I’ll drop a massive amount of sql queries you should find helpful in any environment.
Add comment