cancel
Showing results for 
Search instead for 
Did you mean: 

CronExpression decoded in SWQL

CronExpression decoded in SWQL

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

Version history
Revision #:
1 of 1
Last update:
‎07-20-2017 03:13 PM
Updated by:
 
Contributors