Recipe - Job Administration - Next Run Date

Skill level: Intermediate

Organization: Second Baptist Church (Houston,TX)

Requires Rock: 17.0

{# strip images & classes from the HTML but otherwise leave structure #}

Jobs Administration – Next Run Date


Prerequisites:

- Familiarity with Page Parameter Filter blocks

- Familiarity with Dynamic Data blocks

- Familiarity with HTML Content blocks and Lava

- Comfort with T-SQL

The Summary

Provides a comprehensive view of one or more service jobs, extending the standard Jobs Administration Service Job List with a calculated next run date. Results can be filtered by one or more Job IDs, Active/Inactive status, and a configurable lookahead window expressed in future days.


The Why

Rock's core Jobs Administration page shows Last Successful Run Date and Last Status, but does not display a calculated next run date. For organizations that need to monitor when jobs are scheduled to run next — especially for troubleshooting, auditing, or planning maintenance windows — this recipe fills that gap.

This solution supports all 7-part Quartz cron expressions used by Rock, including all special characters:

| Character | Purpose |

|-----------|---------|

| `*` | Any value |

| `/` | Step — e.g. `*/10` or `1/10` |

| `-` | Range — e.g. `MON-FRI` or `6-16` |

| `,` | List — e.g. `12,20` or `MON,TUE` |

| `?` | No specific value (Day of Month or Day of Week) |

| `L` | Last day of month or last xxx day of month |

| `W` | Nearest weekday to a given day |

| `#` | Nth occurrence — e.g. `MON#1`, `THU#5` |

> Note: Per the cron specification, if an Nth occurrence does not exist in the current month (e.g. `THU#5` when May only has 4 Thursdays), no firing occurs that month and the Next Run Date will be NULL for that job.

---

The How

This recipe uses three pages and four blocks:

  • Page 1 — Criteria Page: Page Parameter Filter block + HTML Content block
  • Page 2 — Results Page: Dynamic Data block


Step 1: Create the Criteria Page

Create a new internal page with a **Full Width** layout. This page will host the filter criteria.

Add the following blocks to the page in order:

  1. Page Parameter Filter block
  2. HTML Content block


Step 2: Configure the Page Parameter Filter Block

Add three filters to the Page Parameter Filter block:


Filter 1 — Job Ids (Multi-Select)

*Job Ids filter: Multi-Select field type, populated via SQL query from ServiceJob table.*

| Setting | Value |

|---------|-------|

| Name | Job Ids |

| Abbreviated Name | Job Ids |

| Key | JobIds |

| Field Type | Multi-Select |

| Enhance For Long Lists | ✅ Checked |

Values (SQL):

SELECT [Id] AS [Value], [Name] + ' (' + CASE WHEN [IsActive] = 1 THEN 'Active' ELSE 'Inactive' END + ')' AS [TEXT]

FROM [dbo].[ServiceJob]

ORDER BY [Name]


Filter 2 — Next Run Within (Single-Select Drop Down)

*Next Run Within filter: Single-Select Drop Down with minute values mapped to friendly day labels.*

| Setting | Value |

|---------|-------|

| Name | Next Run Within |

| Abbreviated Name | Next Run Within |

| Key | NextRunWithin |

| Field Type | Single-Select |

| Control Type | Drop Down List |

Values:


1440^24 Hrs, 10080^7 Days, 20160^14 Days, 44640^31 Days, 86400^60 Days, 129600^90 Days, 259200^180 Days, 527040^366 Days


Note: Values are in minutes (the unit used by the query). Labels are the friendly display shown to the user.

Filter 3 — Active Jobs (Boolean Drop Down)

*Active Jobs filter: Boolean field type rendered as a Drop Down with True/False options.*

| Setting | Value |

|---------|-------|

| Name | Active Jobs |

| Abbreviated Name | Active Jobs |

| Key | ActiveJobs |

| Field Type | Boolean |

| Control Type | Drop Down |

| True Text | True |

| False Text | False |

---

Step 3: Configure the HTML Content Block

The HTML Content block reads the selected filter values from the page parameters, validates that all required fields have been selected, and generates a URL that passes those parameters to the Dynamic Data results page.

Add the following Lava to the HTML Content block:

{{ Lava }}

{% assign jobIds = 'Global' | PageParameter:'JobIds' %}

{% assign activeJobs = 'Global' | PageParameter:'ActiveJobs' | SanitizeSql%}

{% assign nextRunWithin = 'Global' | PageParameter:'NextRunWithin' | SanitizeSql %}



{% assign urlRedirect = 'Global' | Attribute:'InternalApplicationRoot' | Append:'/page/2381?' | Append:'jobIds=' %}


{% if jobIds != null or jobIds != empty %}

    {% assign urlRedirect = urlRedirect | Append:jobIds %}

{% endif %}

{% if activeJobs != null or activeJobs != empty %}

    {% assign urlRedirect = urlRedirect | Append:'&activeJobs=' | Append:activeJobs %}

{% endif %}

{% if nextRunWithin != null or nextRunWithin != empty %}

    {% assign urlRedirect = urlRedirect | Append:'&nextRunWithin=' | Append:nextRunWithin %}

{% endif %}


{% if activeJobs != null and activeJobs != empty and nextRunWithin != null and nextRunWithin != empty and jobIds != null and jobIds != empty %}

    //- {% if CurrentPersonCanEdit %}

   <a class="btn btn-primary" href="{{ urlRedirect }}">Generate</a>

   /- {{ urlRedirect | PageRedirect }}

    {% endif %} -/

{% else %}

<div class="alert alert-info">

   Please select an option for all parameters to generate the export

</div>

{% endif %}


Step 4: Create the Results Page

Create a second internal page with a Full Width layout. Add a single Dynamic Data block to this page.


Step 5: Configure the Dynamic Data Block

Paste the following SQL query into the Dynamic Data block's Query field.

exec [_org_second_sp_Cron_Next_Run_select] @JobIds='{{jobIds}}', @varIsActive='{{activeJobs}}', @varMinutes='{{nextRunWithin}}'

Block Settings:

  • Query Parameters: @JobIds='{{jobIds}}', @varIsActive='{{activeJobs}}', @varMinutes='{{nextRunWithin}}'
  • Enable Export: ✅ Checked
  • Timeout: 300 seconds (increase if needed for large lookahead windows)
  • Selection URL: /admin/system/jobs/{JobId}

Notes & Limitations

  • Timezone: Next run times are calculated in Central Standard Time (CST). To change the timezone, replace `'Central Standard Time'` in the `CronBase` CTE with your desired timezone name (e.g. `'Eastern Standard Time'`).
  • Nth Occurrence (# character): If the Nth occurrence of a day does not exist in the current month (e.g. `THU#5` when there are only 4 Thursdays), the Next Run column will be NULL for that job. This is correct per the cron specification.
  • SQL Server Version: Tested on SQL Server 2014. Uses `EOMONTH()` which requires SQL Server 2012 or later.
  • Performance: Larger lookahead windows generate more candidate rows. For 366 days across all jobs, query time will increase. Use the Job Ids filter to narrow results when using large windows.
  • sys.objects row count: See the Numbers CTE note in the query header if you encounter row count issues with large lookahead windows.

Dependencies

This recipe requires custom string-splitting UDF (user defined function) to parse cron expression fields by index and multi select job parameter. The query references helper functions:


  • [dbo].[ufn_org_second_fn_SplitsByIndex](delimiter, string, index) - This function takes a delimiter, a string, and a 1-based index and returns the Nth delimited segment.
  • [dbo].[ufn_org_second_fn_Split](string, delimiter) -- returns [Items] - This function takes a string and a delimiter and returns [Items] in a table to select from. 


ufn_org_second_fn_Split

/****** Object: UserDefinedFunction [dbo].[ufn_org_second_fn_Split] Script Date: 5/27/2026 6:02:54 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_org_second_fn_Split](@String varchar(max), @Delimiter varchar(50)) returns @temptable TABLE (Items varchar(1700) INDEX IX2 NONCLUSTERED) as begin declare @idx int declare @slice varchar(max) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end GO

ufn_org_second_fn_SplitsByIndex

/****** Object: UserDefinedFunction [dbo].[ufn_org_second_fn_SplitsByIndex] Script Date: 5/27/2026 6:01:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_org_second_fn_SplitsByIndex] (@separator VARCHAR(20) = ' ', @string VARCHAR(MAX), @position INT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @results TABLE (id INT IDENTITY(1, 1), chrs VARCHAR(8000) ); DECLARE @outResult VARCHAR(8000); WITH X(N) AS (SELECT 'Table1' FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)), Y(N) AS (SELECT 'Table2' FROM X A1, X A2, X A3, X A4, X A5, X A6, X A7, X A8), -- Up to 16^8 = 4 billion T(N) AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER( ORDER BY ( SELECT NULL )) - 1 N FROM Y), Delim(Pos) AS (SELECT t.N FROM T WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator OR t.N = 0)), Separated(value) AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER( ORDER BY ( SELECT NULL ))-d.Pos - LEN(@separator)) FROM Delim d WHERE @string IS NOT NULL) INSERT INTO @results(chrs) SELECT s.value FROM Separated s WHERE s.value <> @separator; SELECT @outResult = ( SELECT chrs FROM @results WHERE id = @position ); RETURN @outResult; END; GO

Download related file (_org_second_sp_Cron_Next_Run_select.sql)

Screenshots

  • /GetImage.ashx?guid=15215484-ce20-48ba-8f9b-bb7aeb8d7fe4
  • /GetImage.ashx?guid=917a4036-1ed2-4228-85de-8b668dad36db
  • /GetImage.ashx?guid=9524a910-3255-485d-bfe4-89498443cd02
  • /GetImage.ashx?guid=d1f49b26-2eff-494a-91cd-2f103d6a4ee1