Answers ( 6 )

    0
    2020-07-24T13:20:08+05:30

    Ankit – Good one to have as a post …. feel free to create one 🙂

    1
    2020-07-24T13:11:51+05:30

    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)

    0
    2020-07-24T13:11:15+05:30
    This answer was edited.

    I got this SCCM ConfigMgr CMPivot query for restart details of Windows 10 device – https://snipboard.io/7OHrBR.jpg

     

    OperatingSystem | where LastBootUpTime <= ago(7d) | summarize count() by bin(LastBootUpTime,1d)

    Best answer

Leave an answer

Sorry, you do not have permission to answer to this question .