Use case: Comments and worklogs on one row

A flat export puts all of a work item’s comments in one column, with internal notes marked [internal]. This SQL export keeps one row per work item but splits them: internal notes in one column, replies to the customer in another, and every worklog entry in a third, with the total hours beside it.

It suits a Jira Service Management audit or clean-up: run it once a year for the year just finished, and each ticket’s full story arrives as one Excel row.

Set up the export

1. Create an export and choose its work items, for example a service project’s tickets created last year:

   project = HELP AND created >= startOfYear(-1) AND created < startOfYear() ORDER BY key

   startOfYear(-1) always means the start of last year, so the same export covers the year just finished every time it runs. For an earlier year, use dates: created >= 2024-01-01 AND created < 2025-01-01.

2. Add the Key, Summary, Description, Assignee, Reporter, Comment and Log Work fields. Comment fills the comments table and Log Work the worklogs table: without them those columns are empty.

3. Under Output, choose SQL query and paste the query below.

4. Choose Run on sample to check the result, then Save.

You do not need Export historical changes for this report.

The query

WITH comment_cols AS (
    SELECT
        c.work_item_id,
        string_agg(
            coalesce(u.displayName, 'Unknown') || ', ' || strftime(c.created, '%Y-%m-%d %H:%M') || ': ' || c.body,
            chr(10) ORDER BY c.created
        ) FILTER (WHERE c.jsd_public = false) AS internal_comments,
        string_agg(
            coalesce(u.displayName, 'Unknown') || ', ' || strftime(c.created, '%Y-%m-%d %H:%M') || ': ' || c.body,
            chr(10) ORDER BY c.created
        ) FILTER (WHERE c.jsd_public) AS public_comments
    FROM
        comments c
        LEFT JOIN users u ON u.id = c.author_id
    GROUP BY
        c.work_item_id
),
worklog_cols AS (
    SELECT
        w.work_item_id,
        string_agg(
            coalesce(u.displayName, 'Unknown') || ', ' || strftime(w.started, '%Y-%m-%d') || ', '
                || round(w.time_spent_seconds / 3600.0, 2) || 'h'
                || coalesce(': ' || w.comment, ''),
            chr(10) ORDER BY w.started
        ) AS worklogs,
        round(sum(w.time_spent_seconds) / 3600.0, 2) AS hours_logged
    FROM
        worklogs w
        LEFT JOIN users u ON u.id = w.author_id
    GROUP BY
        w.work_item_id
)
SELECT
    wi.key AS "Key",
    wi.summary AS "Summary",
    wi.description AS "Description",
    wi.assignee AS "Assignee",
    wi.reporter AS "Reporter",
    c.internal_comments AS "Internal comments",
    c.public_comments AS "Public comments",
    wl.worklogs AS "Work logged",
    wl.hours_logged AS "Total hours"
FROM
    work_items_display wi
    LEFT JOIN comment_cols c ON c.work_item_id = wi.id
    LEFT JOIN worklog_cols wl ON wl.work_item_id = wi.id
ORDER BY
    wi.key

How it works:

• comment_cols turns the comments table, one row per comment, into one row per work item. string_agg joins its comments into a single text, oldest first and one per line, each with its author and time. FILTER picks which comments go into each column: jsd_public is false for an internal note and true for a reply the customer can see.
• worklog_cols does the same for worklogs: one line per entry with who logged it, the day, the hours and the worklog comment, plus the total in hours. An entry without a comment has no trailing colon.
• The final SELECT takes each work item from work_items_display, which has names rather than ids (an assignee’s name, not their account id), and joins the two summaries to it.

Summarise first, then join

Joining comments and worklogs to the work items in the same FROM and grouping once gives wrong results: every comment is paired with every worklog of its work item, so a ticket with 3 comments and 4 worklogs repeats each comment 4 times. Summarising each table to one row per work item first, as comment_cols and worklog_cols do, avoids that.

Example result

The names and text below are made up, to show the shape of the result. Each line inside a cell is a separate line in Excel.

Key	Summary	Assignee	Reporter	Internal comments	Public comments	Work logged	Total hours
HELP-101	Printer offline on floor 3	Alex Morgan	Jamie Fox	Alex Morgan, 2025-03-03 10:15: Driver needs updating	Alex Morgan, 2025-03-03 09:40: Thanks, looking into it Alex Morgan, 2025-03-04 14:05: Fixed, can you confirm? Jamie Fox, 2025-03-04 15:00: Works now, thanks	Alex Morgan, 2025-03-03, 1.5h: Updated the driver Alex Morgan, 2025-03-04, 0.5h	2
HELP-102	VPN drops every hour	Sam Lee	Riley Chen	Sam Lee, 2025-04-01 10:30: Escalated to the network team		Sam Lee, 2025-04-01, 1.0h: Call with the user	1
HELP-103	New laptop request		Jamie Fox

A work item with no comments or no time logged still has its row, with those cells empty.

Adjusting the query

• More fields. Any field you add to the export is a column of work_items_display, named by its field id, such as wi.priority or wi.customfield_10010. Service management fields such as Urgency, Impact or Organizations are custom fields: the field picker shows each one’s id beside its name. Add them to the final SELECT.
• Projects other than service management. Outside Jira Service Management, jsd_public is empty (NULL) and both comment columns stay empty. For all comments in one column, replace the two string_agg(...) FILTER (...) expressions with one, without the FILTER.
• Comment text only. To leave out the author and time, use string_agg(c.body, chr(10) ORDER BY c.created).
• A different separator. Replace chr(10) (a new line) with, for example, ' | ' to keep each cell on one line.
• Hours and minutes as Jira shows them. In worklog_cols, replace round(w.time_spent_seconds / 3600.0, 2) || 'h' with format('{}h {}m', w.time_spent_seconds // 3600, (w.time_spent_seconds % 3600) // 60).

An Excel cell holds at most 32,767 characters, so a ticket with a very long comment thread may not fit in one cell. If that happens, use the comment text only, or CSV.

Times are in the export owner’s time zone; see Dates and times.

Schedule it

Save the export and schedule it to run yearly in January as Excel. With the startOfYear(-1) JQL, each run reads fresh data from Jira for the year just finished.

Or ask an AI agent

You can skip the steps above and ask an agent instead: the Rovo agent, or an AI assistant such as Claude connected through MCP. It uses the app’s tools to create the export, write and run the query, and schedule it. You see the result before anything is saved.

Copy this prompt, and change the project key HELP to yours:

Create a report called "Tickets with comments and worklogs" for project HELP, with
one row per work item. Read work items matching the JQL
"project = HELP AND created >= startOfYear(-1) AND created < startOfYear() ORDER BY key",
with the Key, Summary, Description, Assignee, Reporter, Comment and Log Work fields.
For each work item show the key, summary, description, assignee and reporter names,
then three columns: internal comments, public comments (replies the customer can see),
and work logged. In each, list that work item's entries oldest first, one per line,
with the author's name and date. For work logged also show the hours and the worklog
comment, and add a column with the total hours logged.

Show me the result on my data first.

Once I confirm, save it as a SQL export and schedule it to run every year on 2 January
at 8:00, emailing it to me as an Excel (XLSX) file.

The agent works through it in steps and asks you to confirm each one that saves something: the export, the SQL query and the schedule. Its query may differ from the one above, so check it shows the columns you expect before you confirm. For screenshots of each step, see the same flow in Last week’s worklogs.