Hello All,

 

Few days back, I saw one of my colleague was assigned with a task where she has to take screenshots of Maintenance Task present in the Primary Site, I thought of helping her with SQL, but it was not fetching all the details what I was looking for, so I thought if digging into WMI which is the backbone of all windows application. At last I found a WMI class which provides all the relevant information. This class "SMS_SCI_SQLTask" contains all the data about the jobs configured under Site Maintenance. I wrote a set of PowerShell query to fetch the details into a notepad file. Please note for saving results in file, please run PowerShell/PowerShell ISE in Administrator Mode.

 

PowerShell
Edit|Remove
Clear-Host 
Clear-Content MaintenanceTask.txt -Force -Verbose -ErrorAction Stop 
#Getting task details from WMI 
$tasks = gwmi -ComputerName "<CAS/Primary SERVER NAME>" -Namespace "root/sms/site_<SITE CODE>" -query "SELECT * FROM SMS_SCI_SQLTask" 
"TaskName;Enabled;SiteCode;StartTime;LatestStartTime;TaskType;DeleteOlderThan;DaysOfWeek" | Out-File MaintenanceTask.txt -Append 
foreach ($task in $tasks) 
{ 
    #Getting time data to split into readable format 
    $begin = $task.BeginTime 
    $start = $begin.Substring(8,2)+':'+$begin.Substring(10,2)+':'+$begin.Substring(12,2) 
    $latest = $task.LatestBeginTime 
    $startend = $latest.Substring(8,2)+':'+$latest.Substring(10,2)+':'+$latest.Substring(12,2) 
    #Using switch to convert task type values into readable format 
    $type = switch ($task.TaskType) 
    { 
        1{'BackUp'} 
        2{'Period'} 
        3{'Delete'} 
    } 
    #Using switch to convert Mask Values into readable format 
    $days = switch ($task.DaysOfWeek) 
    { 
        1{'Sunday'} 
        2{'Monday'} 
        3{'Sunday, Monday'} 
        4{'Tuesday'} 
        5{'Sunday, Tuesday'} 
        6{'Monday, Tuesday'} 
        7{'Sunday, Monday, Tuesday'} 
        8{'Wednesday'} 
        9{'Sunday, Wednesday'} 
        10{'Monday, Wednesday'} 
        11{'Sunday, Monday, Wednesday'} 
        12{'Tuesday, Wednesday'} 
        13{'Sunday, Tuesday, Wednesday'} 
        14{'Monday, Tuesday, Wednesday'} 
        15{'Sunday, Monday, Tuesday, Wednesday'} 
        16{'Thursday'} 
        17{'Sunday, Thursday'} 
        18{'Monday, Thursday'} 
        19{'Sunday, Monday, Thursday'} 
        20{'Tuesday, Thursday'} 
        21{'Sunday, Tuesday, Thursday'} 
        22{'Monday, Tuesday, Thursday'} 
        23{'Sunday, Monday, Tuesday, Thursday'} 
        24{'Wednesday, Thursday'} 
        25{'Sunday, Wednesday, Thursday'} 
        26{'Monday, Wednesday, Thursday'} 
        27{'Sunday, Monday, Wednesday, Thursday'} 
        28{'Tuesday, Wednesday, Thursday'} 
        29{'Sunday, Tuesday, Wednesday, Thursday'} 
        30{'Monday, Tuesday, Wednesday, Thursday'} 
        31{'Sunday, Monday, Tuesday, Wednesday, Thursday'} 
        32{'Friday'} 
        33{'Sunday, Friday'} 
        34{'Monday, Friday'} 
        35{'Sunday, Monday, Friday'} 
        36{'Tuesday, Friday'} 
        37{'Sunday, Tuesday, Friday'} 
        38{'Monday, Tuesday, Friday'} 
        39{'Sunday, Monday, Tuesday, Friday'} 
        40{'Wednesday, Friday'} 
        41{'Sunday, Wednesday, Friday'} 
        42{'Monday, Wednesday, Friday'} 
        43{'Sunday, Monday, Wednesday, Friday'} 
        44{'Tuesday, Wednesday, Friday'} 
        45{'Sunday, Tuesday, Wednesday, Friday'} 
        46{'Monday, Tuesday, Wednesday, Friday'} 
        47{'Sunday, Monday, Tuesday, Wednesday, Friday'} 
        48{'Thursday, Friday'} 
        49{'Sunday, Thursday, Friday'} 
        50{'Monday, Thursday, Friday'} 
        51{'Sunday, Monday, Thursday, Friday'} 
        52{'Tuesday, Thursday, Friday'} 
        53{'Sunday, Tuesday, Thursday, Friday'} 
        54{'Monday, Tuesday, Thursday, Friday'} 
        55{'Sunday, Monday, Tuesday, Thursday, Friday'} 
        56{'Wednesday, Thursday, Friday'} 
        57{'Sunday, Wednesday, Thursday, Friday'} 
        58{'Monday, Wednesday, Thursday, Friday'} 
        59{'Sunday, Monday, Wednesday, Thursday, Friday'} 
        60{'Tuesday, Wednesday, Thursday, Friday'} 
        61{'Sunday, Tuesday, Wednesday, Thursday, Friday'} 
        62{'Monday, Tuesday, Wednesday, Thursday, Friday'} 
        63{'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday'} 
        64{'Saturday'} 
        65{'Sunday, Saturday'} 
        66{'Monday, Saturday'} 
        67{'Sunday, Monday, Saturday'} 
        68{'Tuesday, Saturday'} 
        69{'Sunday, Tuesday, Saturday'} 
        70{'Monday, Tuesday, Saturday'} 
        71{'Sunday, Monday, Tuesday, Saturday'} 
        72{'Wednesday, Saturday'} 
        73{'Sunday, Wednesday, Saturday'} 
        74{'Monday, Wednesday, Saturday'} 
        75{'Sunday, Monday, Wednesday, Saturday'} 
        76{'Tuesday, Wednesday, Saturday'} 
        77{'Sunday, Tuesday, Wednesday, Saturday'} 
        78{'Monday, Tuesday, Wednesday, Saturday'} 
        79{'Sunday, Monday, Tuesday, Wednesday, Saturday'} 
        80{'Thursday, Saturday'} 
        81{'Sunday, Thursday, Saturday'} 
        82{'Monday, Thursday, Saturday'} 
        83{'Sunday, Monday, Thursday, Saturday'} 
        84{'Tuesday, Thursday, Saturday'} 
        85{'Sunday, Tuesday, Thursday, Saturday'} 
        86{'Monday, Tuesday, Thursday, Saturday'} 
        87{'Sunday, Monday, Tuesday, Thursday, Saturday'} 
        88{'Wednesday, Thursday, Saturday'} 
        89{'Sunday, Wednesday, Thursday, Saturday'} 
        90{'Monday, Wednesday, Thursday, Saturday'} 
        91{'Sunday, Monday, Wednesday, Thursday, Saturday'} 
        92{'Tuesday, Wednesday, Thursday, Saturday'} 
        93{'Sunday, Tuesday, Wednesday, Thursday, Saturday'} 
        94{'Monday, Tuesday, Wednesday, Thursday, Saturday'} 
        95{'Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday'} 
        96{'Friday, Saturday'} 
        97{'Sunday, Friday, Saturday'} 
        98{'Monday, Friday, Saturday'} 
        99{'Sunday, Monday, Friday, Saturday'} 
        100{'Tuesday, Friday, Saturday'} 
        101{'Sunday, Tuesday, Friday, Saturday'} 
        102{'Monday, Tuesday, Friday, Saturday'} 
        103{'Sunday, Monday, Tuesday, Friday, Saturday'} 
        104{'Wednesday, Friday, Saturday'} 
        105{'Sunday, Wednesday, Friday, Saturday'} 
        106{'Monday, Wednesday, Friday, Saturday'} 
        107{'Sunday, Monday, Wednesday, Friday, Saturday'} 
        108{'Tuesday, Wednesday, Friday, Saturday'} 
        109{'Sunday, Tuesday, Wednesday, Friday, Saturday'} 
        110{'Monday, Tuesday, Wednesday, Friday, Saturday'} 
        111{'Sunday, Monday, Tuesday, Wednesday, Friday, Saturday'} 
        112{'Thursday, Friday, Saturday'} 
        113{'Sunday, Thursday, Friday, Saturday'} 
        114{'Monday, Thursday, Friday, Saturday'} 
        115{'Sunday, Monday, Thursday, Friday, Saturday'} 
        116{'Tuesday, Thursday, Friday, Saturday'} 
        117{'Sunday, Tuesday, Thursday, Friday, Saturday'} 
        118{'Monday, Tuesday, Thursday, Friday, Saturday'} 
        119{'Sunday, Monday, Tuesday, Thursday, Friday, Saturday'} 
        120{'Wednesday, Thursday, Friday, Saturday'} 
        121{'Sunday, Wednesday, Thursday, Friday, Saturday'} 
        122{'Monday, Wednesday, Thursday, Friday, Saturday'} 
        123{'Sunday, Monday, Wednesday, Thursday, Friday, Saturday'} 
        124{'Tuesday, Wednesday, Thursday, Friday, Saturday'} 
        125{'Sunday, Tuesday, Wednesday, Thursday, Friday, Saturday'} 
        126{'Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'} 
        127{'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'} 
    } 
    #Input values into a file 
    $task.TaskName+';'+$task.Enabled+';'+$task.SiteCode+';'+$start+';'+$startend+';'+$type+';'+$task.DeleteOlderThan+';'+$days | Out-File MaintenanceTask.txt -Append 
    $task.TaskName+';'+$task.Enabled+';'+$task.SiteCode+';'+$start+';'+$startend+';'+$type+';'+$task.DeleteOlderThan+';'+$days 
}
 

 

In the above given code snippet before executing we have to modify two words as per the hierarchy.

  1. <CAS/Primary SERVER NAME> this has to be replaced with the CAS / Primary Server Name whichever available on your hierarchy.
  2. <SITE CODE> this has to be replaced with relevant site code.

 

Please feel free to comment your queries in Q&A section.

 

Reference Link: https://docs.microsoft.com/en-us/sccm/develop/reference/core/servers/configure/sms_sci_sqltask-server-wmi-class

DaysofWeek Calculation Reference: https://technet.microsoft.com/en-us/library/ee808920.aspx

Cheers!

Praveen