CronExpression decoded in SWQL

Version 1

    This is my first successful draft of a code bit that will decode the CronExpression column for alert time periods in a totally SWQL friendly way.

     

    This code is not pretty. It is not elegant. It does not cover every Crontab configuration that is ever possible ever.

     

    It DOES run in a web report. It DOES, as far as I can tell, handle all the possible configurations available with in Solarwinds.

     

     

    SELECT
    f.CronExpression
        ,CASE WHEN (f.CronExpression NOT LIKE '% % */1 %' AND f.CronExpression LIKE '% % % * *')
                   OR f.CronExpression LIKE '% % % 1,2,3,4,5,6,7,8,9,10,11,12 %' THEN 'Monthly'
            WHEN f.CronExpression LIKE '% % %-% %' THEN 'Monthly'
            WHEN f.CronExpression LIKE '% % */1 %' THEN 'Daily'
            WHEN f.CronExpression LIKE '% % % % %' AND f.CronExpression NOT LIKE '% % % % *' THEN 'Weekly'
            ELSE '??'
            END AS Frequency
        ,(CASE WHEN f.CronExpression LIKE '% % % * %'

                   OR f.CronExpression LIKE '% % % 1,2,3,4,5,6,7,8,9,10,11,12 %' THEN 'All'
            ELSE (
         (CASE WHEN f.CronExpression LIKE '% % % %1% %' THEN 'January,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %2% %' THEN 'February,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %3% %' THEN 'March,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %4% %' THEN 'April,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %5% %' THEN 'May,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %6% %' THEN 'June,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %7% %' THEN 'July,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %8% %' THEN 'August,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %9% %' THEN 'September,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %10% %' THEN 'October,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %11% %' THEN 'November,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % %12% %' THEN 'December,' ELSE '' END) 
            ) END) AS MonthOfYear
        , CASE WHEN f.CronExpression LIKE '% % */1 %' THEN ' '
            WHEN f.CronExpression LIKE '% % * %' THEN 'Every '
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ _ %1 %' THEN (SUBSTRING(f.CronExpression,5,2) + 'st')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ _ %1 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'st')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ __ %1 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'st')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ __ %1 %' THEN (SUBSTRING(f.CronExpression,7,2) + 'st')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ _ %2 %' THEN (SUBSTRING(f.CronExpression,5,2) + 'nd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ _ %2 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'nd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ __ %2 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'nd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ __ %2 %' THEN (SUBSTRING(f.CronExpression,7,2) + 'nd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ _ %3 %' THEN (SUBSTRING(f.CronExpression,5,2) + 'rd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ _ %3 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'rd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ __ %3 %' THEN (SUBSTRING(f.CronExpression,6,2) + 'rd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ __ %3 %' THEN (SUBSTRING(f.CronExpression,7,2) + 'rd')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ _ % %' AND f.CronExpression NOT LIKE '% % * %' THEN (SUBSTRING(f.CronExpression,5,2) + 'th')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ _ % %' AND f.CronExpression NOT LIKE '% % * %' THEN (SUBSTRING(f.CronExpression,6,2) + 'th')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '_ __ % %' AND f.CronExpression NOT LIKE '% % * %' THEN (SUBSTRING(f.CronExpression,6,2) + 'th')
            WHEN f.CronExpression LIKE '% % _ %' AND f.CronExpression LIKE '__ __ % %' AND f.CronExpression NOT LIKE '% % * %' THEN (SUBSTRING(f.CronExpression,7,2) + 'th')
            WHEN f.CronExpression LIKE '% % 1-7 %' THEN 'First'
            WHEN f.CronExpression LIKE '% % 8-14 %' THEN 'Second'
            WHEN f.CronExpression LIKE '% % 15-21 %' THEN 'Third'
            WHEN f.CronExpression LIKE '% % 22-28 %' THEN 'Fourth'
            WHEN f.CronExpression LIKE '% % 29-31 %' THEN 'Fifth'
            ELSE '??'
            END AS DayOfMonth
        ,((CASE WHEN f.CronExpression LIKE '% % % % %0%' THEN 'Sunday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %1%' THEN 'Monday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %2%' THEN 'Tuesday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %3%' THEN 'Wednesday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %4%' THEN 'Thursday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %5%' THEN 'Friday,' ELSE '' END) +
         (CASE WHEN f.CronExpression LIKE '% % % % %6%' THEN 'Saturday,' ELSE '' END)
            ) AS DayOfWeek
        ,(CASE WHEN f.CronExpression LIKE '0 %' AND f.CronExpression LIKE '_ __ %' THEN SUBSTRING(f.CronExpression,3,2)
                WHEN f.CronExpression LIKE '0 %' AND f.CronExpression LIKE '_ _ %' THEN ('0' + SUBSTRING(f.CronExpression,3,1))
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '__ __ %' THEN SUBSTRING(f.CronExpression,4,2)
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '_ _ %' THEN ('0' + SUBSTRING(f.CronExpression,1,1))
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '__ _ %' THEN ('0' + SUBSTRING(f.CronExpression,1,1))
            ELSE '??'
            END)  + ':' +
        (CASE WHEN f.CronExpression LIKE '0 %' AND f.CronExpression LIKE '_ __ %' THEN ('0' + SUBSTRING(f.CronExpression,1,2))
                WHEN f.CronExpression LIKE '0 %' AND f.CronExpression LIKE '_ _ %' THEN ('0' + SUBSTRING(f.CronExpression,1,1))
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '__ __ %' THEN SUBSTRING(f.CronExpression,1,2)
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '__ _ %' THEN SUBSTRING(f.CronExpression,1,2)
                WHEN f.CronExpression NOT LIKE '0 %' AND f.CronExpression LIKE '_ _ %' THEN ('0' + SUBSTRING(f.CronExpression,1,1))
            ELSE '??'
            END) AS TimeOfDay

    FROM Orion.Frequencies f