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