I dont think that information can be pulled via CMPivot or if yes i have not tried.You can use below SQL query to get the data.
SELECT Name AS [Hostname],ClientState, (SELECT CASE [ClientState] WHEN ‘1’ THEN ‘Configuration Manager’ WHEN ‘2’ THEN ‘File Rename’ WHEN ‘3’ THEN ‘Configuration Manager, File Rename’ WHEN ‘4’ THEN ‘Windows Update’ WHEN ‘5’ THEN ‘Configuration Manager, Windows Update’ WHEN ‘6’ THEN ‘File Rename, Windows Update’ WHEN ‘7’ THEN ‘Configuration Manager, File Rename, Windows Update’ WHEN ‘8’ THEN ‘Add or Remove Feature’ WHEN ‘9’ THEN ‘Configuration Manager, Add or Remove Feature’ WHEN ’10’ THEN ‘File Rename, Add or Remove Feature’ WHEN ’11’ THEN ‘Configuration Manager, File Rename, Add or Remove Feature’ WHEN ’12’ THEN ‘Windows Update, Add or Remove Feature’ WHEN ’13’ THEN ‘Configuration Manager, Windows Update, Add or Remove Feature’ WHEN ’14’ THEN ‘File Rename, Windows Update, Add or Remove Feature’ WHEN ’15’ THEN ‘Configuration Manager, File Rename, Windows Update, Add or Remove Feature’ ELSE ‘Unknown’ END AS Expr1) AS [Pending Restart Reason], (SELECT CASE WHEN DeviceOS LIKE ‘%Workstation 5.0%’ THEN ‘Microsoft Windows 2000’ WHEN DeviceOS LIKE ‘%Workstation 5.1%’ THEN ‘Microsoft Windows XP’ WHEN DeviceOS LIKE ‘%Workstation 5.2%’ THEN ‘Microsoft Windows XP 64bit’ WHEN DeviceOS LIKE ‘%Server 5.2%’ THEN ‘Microsoft Server Windows Server 2003’ WHEN DeviceOS LIKE ‘%Workstation 6.0%’ THEN ‘Microsoft Windows Vista’ WHEN DeviceOS LIKE ‘%Server 6.0%’ THEN ‘Microsoft Server Windows Server 2008 R2’ WHEN DeviceOS LIKE ‘%Server 6.1%’ THEN ‘Microsoft Server Windows Server 2008’ WHEN DeviceOS LIKE ‘%Workstation 6.1%’ THEN ‘Microsoft Windows 7’ WHEN DeviceOS LIKE ‘%server 6.3%’ THEN ‘Microsoft Server Windows Server 2012 R2’ WHEN DeviceOS LIKE ‘%server 6.2%’ THEN ‘Microsoft Server Windows Server 2012’ WHEN DeviceOS LIKE ‘%Workstation 6.2%’ THEN ‘Microsoft Windows 8’ WHEN DeviceOS LIKE ‘%Workstation 6.3%’ THEN ‘Microsoft Windows 8.1’ WHEN DeviceOS LIKE ‘%Workstation 10%’ THEN ‘Microsoft Windows 10’ WHEN DeviceOS LIKE ‘%server 10%’ THEN ‘Microsoft Windows Server 2016’ ELSE ‘N/A’ END AS Expr1) AS [Operating System], LastLogonUser FROM dbo.vSMS_CombinedDeviceResources WHERE (ClientState > 0) AND (ClientActiveStatus = 1)
sorry for saying I have an idea about SQL query, but looking CMPivot report. which realtime information about pending reboot server details. It will be useful while deploying monthly patching to servers, to get the which are reboot pending state.
Answers ( 6 )
Ankit – Good one to have as a post …. feel free to create one 🙂
Sure will create sorry i missed the CMPivot part.
I dont think that information can be pulled via CMPivot or if yes i have not tried.You can use below SQL query to get the data.
SELECT Name AS [Hostname],ClientState,
(SELECT CASE [ClientState]
WHEN ‘1’ THEN ‘Configuration Manager’ WHEN ‘2’ THEN ‘File Rename’ WHEN ‘3’ THEN ‘Configuration Manager, File Rename’ WHEN ‘4’ THEN ‘Windows Update’
WHEN ‘5’ THEN ‘Configuration Manager, Windows Update’ WHEN ‘6’ THEN ‘File Rename, Windows Update’ WHEN ‘7’ THEN ‘Configuration Manager, File Rename, Windows Update’
WHEN ‘8’ THEN ‘Add or Remove Feature’ WHEN ‘9’ THEN ‘Configuration Manager, Add or Remove Feature’ WHEN ’10’ THEN ‘File Rename, Add or Remove Feature’
WHEN ’11’ THEN ‘Configuration Manager, File Rename, Add or Remove Feature’ WHEN ’12’ THEN ‘Windows Update, Add or Remove Feature’
WHEN ’13’ THEN ‘Configuration Manager, Windows Update, Add or Remove Feature’ WHEN ’14’ THEN ‘File Rename, Windows Update, Add or Remove Feature’
WHEN ’15’ THEN ‘Configuration Manager, File Rename, Windows Update, Add or Remove Feature’ ELSE ‘Unknown’ END AS Expr1) AS [Pending Restart Reason],
(SELECT CASE WHEN DeviceOS LIKE ‘%Workstation 5.0%’ THEN ‘Microsoft Windows 2000’ WHEN DeviceOS LIKE ‘%Workstation 5.1%’ THEN ‘Microsoft Windows XP’
WHEN DeviceOS LIKE ‘%Workstation 5.2%’ THEN ‘Microsoft Windows XP 64bit’ WHEN DeviceOS LIKE ‘%Server 5.2%’ THEN ‘Microsoft Server Windows Server 2003’
WHEN DeviceOS LIKE ‘%Workstation 6.0%’ THEN ‘Microsoft Windows Vista’ WHEN DeviceOS LIKE ‘%Server 6.0%’ THEN ‘Microsoft Server Windows Server 2008 R2’
WHEN DeviceOS LIKE ‘%Server 6.1%’ THEN ‘Microsoft Server Windows Server 2008’ WHEN DeviceOS LIKE ‘%Workstation 6.1%’ THEN ‘Microsoft Windows 7’
WHEN DeviceOS LIKE ‘%server 6.3%’ THEN ‘Microsoft Server Windows Server 2012 R2’ WHEN DeviceOS LIKE ‘%server 6.2%’ THEN ‘Microsoft Server Windows Server 2012’
WHEN DeviceOS LIKE ‘%Workstation 6.2%’ THEN ‘Microsoft Windows 8’ WHEN DeviceOS LIKE ‘%Workstation 6.3%’ THEN ‘Microsoft Windows 8.1’
WHEN DeviceOS LIKE ‘%Workstation 10%’ THEN ‘Microsoft Windows 10’ WHEN DeviceOS LIKE ‘%server 10%’ THEN ‘Microsoft Windows Server 2016’
ELSE ‘N/A’ END AS Expr1) AS [Operating System], LastLogonUser
FROM dbo.vSMS_CombinedDeviceResources
WHERE (ClientState > 0) AND (ClientActiveStatus = 1)
sorry for saying
I have an idea about SQL query, but looking CMPivot report. which realtime information about pending reboot server details. It will be useful while deploying monthly patching to servers, to get the which are reboot pending state.
but i already shared the CMpivot query 😀
I got this SCCM ConfigMgr CMPivot query for restart details of Windows 10 device – https://snipboard.io/7OHrBR.jpg