c# – Parse SCCM Schedules from SQL

Ever ran a report against the vSMS_ServiceWindowtable of SCCM and realized the Schedules object was…. well, not very readable?

Have no fear! Here is a c# solution.

My solution is based off of this pseudocode: How to Configure Hardware Inventory Settings

Here is the SQL Query I use:

select
    c.CollectionID
    ,c.SiteID
    ,c.CollectionName
    ,c.CollectionType
    ,c.ServiceWindowsCount
    ,c.LimitToCollectionID
    ,c.LimitToCollectionName
    ,c.ObjectPath
    ,sw.Name
    ,sw.ServiceWindowType
    ,sw.Description
    ,sw.Enabled
    ,sw.Schedules
from v_Collections c
JOIN vSMS_ServiceWindow sw on sw.SiteID = c.SiteID

I left out the piece which queries a database, and returns data rows. If you are reading this, I assume you can manage to figure that piece out.

using System;
using System.Data;
using System.Diagnostics;

namespace LIB.SCCM.Models
{
    /// <summary>
    /// SCCM Table - vSMS_ServiceWindow
    /// </summary>
    /// 
    [DebuggerDisplay("{Name} - {Description}")]
    public class ServiceWindow
    {
        public ServiceWindow(DataRow row)
        {
            this.Name = row.Field<string>("Name");
            this.Description = row.Field<string>("Description");
            this.WindowType = (ServiceWindowType)row.Field<int>("ServiceWindowType");
            this.Enabled = row.Field<bool>("Enabled");

            //Parse out the funky schedule...
            var Schedule_HEX = row.Field<string>("Schedules");

            //Split the hex string into two pieces.
            string StartTime_HEX = Schedule_HEX.Substring(0, 8);
            string Recurrence_HEX = Schedule_HEX.Substring(8, 8);

            //Parse a long from the provided hex values, then convert that to a binary string... Lastly, Pad left, until there are 32 total bits.
            var StartTime_BIN = Convert.ToString(long.Parse(StartTime_HEX, System.Globalization.NumberStyles.HexNumber), 2).PadLeft(32, '0');
            var Recurrence_BIN = Convert.ToString(long.Parse(Recurrence_HEX, System.Globalization.NumberStyles.HexNumber), 2).PadLeft(32, '0');

            //Parse out the start date.
            int Min = Convert.ToInt32(StartTime_BIN.Substring(0, 6), 2);
            int Hour = Convert.ToInt32(StartTime_BIN.Substring(6, 5), 2);
            int Day = Convert.ToInt32(StartTime_BIN.Substring(11, 5), 2);
            int Month = Convert.ToInt32(StartTime_BIN.Substring(16, 4), 2);
            int Year = Convert.ToInt32(StartTime_BIN.Substring(20, 6), 2) + 1970;
            int Duration = Convert.ToInt32(StartTime_BIN.Substring(26, 6), 2);

            //Convert those values into a valid date time. Seconds are not supported in sccm schedules.
            this.StartTime = new DateTime(Year, Month, Day, Hour, Min, 0);

            //Parse out the duration.
            int Duration_Hours = Convert.ToInt32(Recurrence_BIN.Substring(0, 5), 2);
            int Duration_Mins = Convert.ToInt32(Recurrence_BIN.Substring(5, 5), 2);

            //Store those values as the duration Timespan. SCCM does not support seconds in schedules.
            this.Duration = new TimeSpan(Duration_Hours, Duration_Mins, 0);

            //Is GMT Flag is the last digit. 
            bool isGMT = Convert.ToBoolean(Convert.ToInt32(Recurrence_BIN.Substring(31, 1)));

            //Parse out the flags...
            int Flags = Convert.ToInt32(Recurrence_BIN.Substring(10, 3), 2);
            switch (Flags)
            {
                case 1: //NonRecurring Schedule
                    this.RecurrenceType = RecurrenceType.NONE;
                    break;
                case 2: //Recurring on predefined interval.
                    {
                        this.RecurrenceType = RecurrenceType.DAILY;
                        //this.RecurrenceType = RecurrenceType.
                        int rec_Mins = Convert.ToInt32(Recurrence_BIN.Substring(13, 6), 2);
                        int rec_Hours = Convert.ToInt32(Recurrence_BIN.Substring(19, 5), 2);
                        int rec_Days = Convert.ToInt32(Recurrence_BIN.Substring(24, 5), 2);

                        TimeSpan RecurEvery = new TimeSpan(rec_Days, rec_Hours, rec_Mins);
                    }
                    break;
                case 3: //Recurring on weekly basis.
                    {
                        this.RecurrenceType = RecurrenceType.WEEKLY;
                        int rec_wDay_int = Convert.ToInt32(Recurrence_BIN.Substring(13, 3), 2);
                        int rec_num_weeks = Convert.ToInt32(Recurrence_BIN.Substring(16, 3), 2);

                        //The SCCM value for day of week starts at 1. .Net value starts at 0.
                        DayOfWeek rec_DayOfWeek = (DayOfWeek)(rec_wDay_int - 1);
                    }
                    break;
                case 4: //Recur monthly, by weekday.
                    {
                        this.RecurrenceType = RecurrenceType.MONTHLYBYWEEKDAY;
                        int WeekDay = Convert.ToInt32(Recurrence_BIN.Substring(13, 3), 2);
                        //What day of the week this will trigger on.
                        DayOfWeek rec_DayOfWeek = (DayOfWeek)(WeekDay - 1);

                        //How many months between reoccurences
                        int rec_Every_N_Months = Convert.ToInt32(Recurrence_BIN.Substring(16, 4), 2);

                        //Which Week, ie, First, second, last, etc...
                        // 0 = Last, 1 = First, 2 = 2nd, 3 = 3rd, 4 = 4th.
                        int WeekOrder = Convert.ToInt32(Recurrence_BIN.Substring(20, 3), 2);
                    }
                    break;
                case 5: //Monthly By Date - Custom schedule only!
                    {
                        //I didn't finish parsing this piece out... becase, it is not used in my environment.
                        this.RecurrenceType = RecurrenceType.MONTHLYBYDATE;

                        //strDate = Next 5 chars of strBinRecurrence convert bin2dec
                        //strNumMonths = Next 4 chars of strBinRecurrence convert bin2dec
                        //strUnused = Next 9 chars of strBinRecurrence convert bin2dec
                        //'Should be 0 (not used by HINV schedules)
                        //bIsGMT = Last char of strBinRecurrence convert bin2bool
                        //'0=False,1=True (should be 0, not used by HINV schedules)
                        //Select Case strDate
                        //   Case "0"
                        //      strFriendlyRecurrence = "Occurs the last day of every "
                        //   Case Else
                        //      strFriendlyRecurrence = "Occurs day " & strDate &_
                        //                              " of every "
                        //End Case
                        //strFriendlyRecurrence = strFriendlyRecurrence & strNumMonths &_
                        //                        " month(s)"
                    }
                    break;
            }
        }


        public string Name { get; set; }
        public string Description { get; set; }
        public TimeSpan Duration { get; set; }
        public bool Enabled { get; set; }
        public RecurrenceType RecurrenceType { get; set; }
        public ServiceWindowType WindowType { get; set; }
        public DateTime StartTime { get; set; }
    }
}