Reference

Endpoint Read Worklogs modified after a specified date

Name

get_worklogs_after

Description

No description available

Parameters

Parameter

Required

Options

Name: UpdatedAfter

Label: Worklog Modified After Date/Time

You can supply UpdatedAfter or CreatedAfter parameter but not both. If you supply both, CreatedAfter will be ignored. Client side filter to fetch worklogs modified after a specified date and time. Supply this date as server timezone. You can supply date as static value or function. For static value, you can supply date as yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss.fff. For function, you can supply today, today-2d, today-24h, today-1month, today-1year, yesterday, monthstart, monthend, yearstart, yearend, weekstart, weekend, now, now-2h, now-2m.

YES

Option	Value
Date Example	2026-02-04
Date and Time Example	2026-02-04T12:59:00
Function	today

Name: JQL

Label: JQL Expression (For Issue Search)

Use JQL to use an advanced mode of searching. Few links are https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14 and als check https://wac-cdn.atlassian.com/dam/jcr:241d9c9b-475f-411f-90a2-97ca344442b3/atlassian-jql-cheat-sheet-2.pdf?cdnVersion=2169

Option	Value
Search Ids (IN)	key IN(10001, 10002, 10003)
Search Keys (IN)	key IN(CS-1, CS-2, CS-3)
Search Projects (IN)	project IN(PROJ1, PROJ2, PROJ3)
Search Status (EQUAL)	status='Done'
Search Date (Expression 1)	created >=-5d
Search Date (Expression 2)	created >=startOfMonth() AND created <=now()
Search Date (Expression 3)	created >=startOfYear() AND created <=startOfDay()
Search Date (Expression 4 - Site Timezone)	created >= '2008-12-31 23:59'
Search Date (Issue Created After Date - Quote1)	created >= '2008-12-31'
Search Date (Issue Created After Date - Quote2)	created >= "2008-12-31"
Search Date (Issue Created After Date+Time - Site Timezone)	created >= '2008-12-31 23:59'
Search Date (Issue Updated After Date+Time - Site Timezone)	updated >= "2008-12-31 23:59"
Search Date (Issue Updated In Last 5 Days)	updated >=-5d
Search Date (Issue Updated In Last 24 Hours)	updated >=-24h
Search Date (Worklog Created or Updated After Date)	worklogDate >= '2008-12-31'
Search Date (Worklog Created or Updated In Last 2 Days)	worklogDate >= -2d
Search Date (Worklog Created or Updated After for Specified Projects)	project IN (PROJ1, PROJ2) AND worklogDate >= '2008-12-31'
Search Project and Status (AND + IN)	project=CS AND status NOT IN ('Done', 'Open', 'Closed')
Search Assignee and Created Date	assignee is NOT EMPTY and created < -1d
Search Text (Contains - Fuzzy)	Summary ~ 'some words' OR description ~ 'some words'
Search Text (Contains - Fuzzy Wildcard)	Summary ~ 'some' OR description ~ 'some'
Search Text (Contains - Exact)	Summary ~ '\"exact words\"' OR description ~ '\"exact words\"'
Search Text (Does Not Contain - Fuzzy)	Summary !~ 'some words' OR description !~ 'some words'
Search Empty OR Null	fixVersion is empty OR fixVersion is null
Search Is Not Empty OR Is Not Null	fixVersion is not empty OR fixVersion is not null
Search WAS Operator (previous value)	status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02"
Search WAS IN Operator	status WAS IN ("Resolved", "In Progress")
Search WAS NOT IN Operator	status WAS NOT IN ("Resolved", "In Progress")
Search WAS + BY + DURING (date range)	status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02")
Search CHANGED operator	assignee CHANGED
Search CHANGED operator (multiple)	status CHANGED FROM "In Progress" TO "Open"

Name: Project

Label: Project Key(s) - Comma Separated (CBS,BRK)

Specify a project to search issues for

Name: Key

Label: Issue Key(s) or Ids - Comma Separated (CBS-10,PRA-13)

Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.

Name: CustomColumnsRegex

Label: CustomColumnsRegex

Enter regular expression for custom columns which you like to output. Use Pipe to type multiple column names (must use key - internal names for columns e.g. customfield_10004). You can use Regex too (e.g. field_100\d+ ). This filter is only applied for those fields which has flag custom=true.

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
IssueId	`DT_WSTR`	`nvarchar(20)`	20
IssueKey	`DT_WSTR`	`nvarchar(50)`	50
Id	`DT_WSTR`	`nvarchar(20)`	20
Self	`DT_WSTR`	`nvarchar(150)`	150
StartAt	`DT_I4`	`int`
MaxResults	`DT_I4`	`int`
Total	`DT_I8`	`bigint`
AuthorSelf	`DT_WSTR`	`nvarchar(200)`	200
AuthorAccountId	`DT_WSTR`	`nvarchar(150)`	150
AuthorEmailAddress	`DT_WSTR`	`nvarchar(200)`	200
AuthorDisplayName	`DT_WSTR`	`nvarchar(200)`	200
AuthorIsActive	`DT_BOOL`	`bit`
AuthorTimeZone	`DT_WSTR`	`nvarchar(100)`	100
AuthorAccountType	`DT_WSTR`	`nvarchar(50)`	50
CommentFormatted	`DT_NTEXT`	`nvarchar(MAX)`
Comment	`DT_NTEXT`	`nvarchar(MAX)`		Join formatted text blocks using space separator (new lines will be lost)
UpdateAuthorSelf	`DT_WSTR`	`nvarchar(500)`	500
UpdateAuthorAccountId	`DT_WSTR`	`nvarchar(200)`	200
UpdateAuthorEmailAddress	`DT_WSTR`	`nvarchar(200)`	200
UpdateAuthorDisplayName	`DT_WSTR`	`nvarchar(200)`	200
UpdateAuthorActive	`DT_BOOL`	`bit`
UpdateAuthorTimeZone	`DT_WSTR`	`nvarchar(80)`	80
UpdateAuthorAccountType	`DT_WSTR`	`nvarchar(50)`	50
Created	`DT_DBTIMESTAMP`	`datetime`
Updated	`DT_DBTIMESTAMP`	`datetime`
Started	`DT_DBTIMESTAMP`	`datetime`
TimeSpent	`DT_WSTR`	`nvarchar(50)`	50
TimeSpentInSeconds	`DT_I8`	`bigint`
AuthorAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
AuthorAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
AuthorAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
AuthorAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500
UpdateAuthorAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
UpdateAuthorAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
UpdateAuthorAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
UpdateAuthorAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500

If the column you are looking for is missing, consider customizing Jira Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Jira Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Read Worklogs modified after a specified date

Required Parameters
Worklog Modified After Date/Time	Fill-in the parameter...
Optional Parameters
JQL Expression (For Issue Search)	created >= 1970-01-01
Project Key(s) - Comma Separated (CBS,BRK)
Issue Key(s) or Ids - Comma Separated (CBS-10,PRA-13)

SSIS API Source - Read from table or endpoint

API Destination

API Destination - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Read Worklogs modified after a specified date

Required Parameters
Worklog Modified After Date/Time	Fill-in the parameter...
Optional Parameters
JQL Expression (For Issue Search)	created >= 1970-01-01
Project Key(s) - Comma Separated (CBS,BRK)
Issue Key(s) or Ids - Comma Separated (CBS-10,PRA-13)

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

List worklogs modified after a specific date

List all worklog entries modified after a specified date/time (Note: DateTime is in Site Timezone)

SELECT * FROM get_worklogs_after
WITH(
	--#### Worklog Filter #####
	  UpdatedAfter='2024-08-23' --only fetch work logs modified after this date/time is Site Timezone
	--OR--
	--UpdatedAfter='2024-08-23T17:03:26.079' --with time part
	--OR--
	--UpdatedAfter='today-1d' --with function. For more information about functions, see https://zappysys.com/links/?id=10014 (Look for FUN_TO_DATETIME)
    --UpdatedAfter='monthstart+7d' 
	--UpdatedAfter='now-24h' 
    
	--#### Issue Filter (Optional) #####
    --Search issues by Project / JQL expression / Issue Keys. If not specified all issues returned
	--,JQL='key IN(CS-1, CS-2, CS-3)' 
	--,Project='PRJ1,PRJ2' 
	--,Key='CS-1,CS-2,CS-3' 
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List worklogs modified after a specific date

List all worklog entries modified after a specified date/time (Note: DateTime is in Site Timezone)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_worklogs_after
WITH(
	--#### Worklog Filter #####
	  UpdatedAfter=''2024-08-23'' --only fetch work logs modified after this date/time is Site Timezone
	--OR--
	--UpdatedAfter=''2024-08-23T17:03:26.079'' --with time part
	--OR--
	--UpdatedAfter=''today-1d'' --with function. For more information about functions, see https://zappysys.com/links/?id=10014 (Look for FUN_TO_DATETIME)
    --UpdatedAfter=''monthstart+7d'' 
	--UpdatedAfter=''now-24h'' 
    
	--#### Issue Filter (Optional) #####
    --Search issues by Project / JQL expression / Issue Keys. If not specified all issues returned
	--,JQL=''key IN(CS-1, CS-2, CS-3)'' 
	--,Project=''PRJ1,PRJ2'' 
	--,Key=''CS-1,CS-2,CS-3'' 
)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];