How do I investigate a support ticket and keep the evidence?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A ticket arrives: "The country report shows no cities for some countries. Codes: ROU, TLS, MDA, HUN." You need to find out which countries are affected and why, check whether production behaves the same, and answer with something more solid than "I looked and it seemed fine". Afterwards, someone may ask what exactly you ran, where and when.

You will work the ticket from start to finish on WORLD: switch on the activity log first, look the codes up straight from the clipboard, feed each result into the next query with <@last:...> instead of retyping keys, find the cause, confirm it on production, and attach the decisive result to the ticket as a Markdown table. Every command of this page only reads.

Short answer

SET TRACE; (or IsLogActivated=TRUE), then WHERE CODE IN <@clipboard>, then WHERE ... IN <@last:<column>> for each next step, / PROD to compare with production, and DUMP / TO <name> AS MD; for the ticket.

Before you start

The investigation runs on WORLD. Step 6 compares with production and needs the practice connections of Set up QA and production copies of WORLD, which do not exist by default; without them, skip that step. The activity log writes to the logs folder of the installation, which is part of it.

1. Switch on the record first

Do this before the first query, since the log only records what happens after it is on. Connected to the sample, SET TRACE switches the activity log on for the current connection:

CONNECT WORLD;
SET TRACE;

From now on, every command you type and everything BroadSQL prints is written to logs/broadsql_WORLD_<yyyymmdd>.log, one file per connection and per day. SET TRACE switches the log on or off, so if the log is already on (IsLogActivated=TRUE in BroadSQL.ini), do not type it: it would switch it off. To log every session, set it once in the [Log] section of conf/BroadSQL.ini (broadsqlux.ini on Linux):

[Log]
IsLogActivated=TRUE
LogFolderName=logs
LogFileNamePattern=broadsql

2. Look the codes up from the ticket

Select the four codes in the ticket, one per line, and copy them:

ROU
TLS
MDA
HUN

Then list the countries, with the ID of their capital city, which will be useful later. <@clipboard> becomes the quoted list of the copied codes before the query runs:

SELECT CODE, TRIM(NAME) AS NAME, CAPITAL FROM COUNTRY WHERE CODE IN <@clipboard> ORDER BY CODE;

The four countries exist:

WORLD> SELECT CODE, TRIM(NAME) AS NAME, CAPITAL FROM COUNTRY WHERE CODE IN <@clipboard> ORDER BY CODE;
|----|----------------------------------------------------|-----------|
|CODE|NAME                                                |CAPITAL    |
|----|----------------------------------------------------|-----------|
|HUN |Hungary                                             |3483       |
|MDA |Moldova                                             |2690       |
|ROU |Romania                                             |3018       |
|TLS |East Timor                                          |1522       |
|----|----------------------------------------------------|-----------|

4 rows fetched in 5 ms.

3. Count their cities, without retyping the codes

<@last:CODE> stands for the values of the CODE column of the result on screen. The next query counts the cities of exactly these four countries:

SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE IN <@last:CODE> GROUP BY COUNTRYCODE ORDER BY COUNTRYCODE;

Only Hungary and Moldova come back: the ticket is confirmed for Romania and East Timor.

WORLD> SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE IN <@last:CODE> GROUP BY COUNTRYCODE ORDER BY COUNTRYCODE;
|-----------|--------------------|
|COUNTRYCODE|CITIES              |
|-----------|--------------------|
|HUN        |9                   |
|MDA        |4                   |
|-----------|--------------------|

2 rows fetched in 4 ms.

How <@last:...> reads the result:

• It always reads the most recent result displayed, whatever its query: run the queries in order.
• The column is found by the label you saw, so after SELECT CODE AS C ..., write <@last:C>. A label that matches more than one column is refused as ambiguous.
• NULL values of the column are skipped. When the result exceeded the safety row limit, <@last:...> refuses to resolve rather than use a partial list.

4. Find the cause

The capitals of step 2 can tell where the cities went, but they are no longer the last result. Run the step 2 query again (the Up arrow brings it back from the history), then look at the capital cities themselves:

SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE FROM CITY WHERE ID IN <@last:CAPITAL> ORDER BY ID;

The capitals exist, but two of them carry a country code that is not their country's:

WORLD> SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE FROM CITY WHERE ID IN <@last:CAPITAL> ORDER BY ID;
|-----------|-----------------------------------|-----------|
|ID         |NAME                               |COUNTRYCODE|
|-----------|-----------------------------------|-----------|
|1522       |Dili                               |TMP        |
|2690       |Chisinau                           |MDA        |
|3018       |Bucuresti                          |ROM        |
|3483       |Budapest                           |HUN        |
|-----------|-----------------------------------|-----------|

4 rows fetched in 3 ms.

That is the cause: the cities of Romania are stored under ROM and Dili under TMP, the old codes of Romania and East Timor, while COUNTRY uses ROU and TLS. A report that joins cities to countries finds none for them. The values of <@last:CAPITAL> are quoted like every list source, and the database compares them with the numeric ID.

5. Measure the extent

The ticket names four countries; the problem may be wider. Count the cities under the two old codes:

SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE IN ('ROM', 'TMP') GROUP BY COUNTRYCODE ORDER BY COUNTRYCODE;

29 cities under ROM and 1 under TMP. Find orphan rows, duplicates and missing values runs the same check on every country code at once, and finds no other.

6. Is production the same?

A ticket from production may describe something QA does not show, and the other way round. With the practice set, open the QA copy, switch its log on, run the count there, then run it once on production with / PROD:

CONNECT WORLD-QA;
SET TRACE;
SELECT COUNTRYCODE, COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE IN ('ROM', 'TMP') GROUP BY COUNTRYCODE ORDER BY COUNTRYCODE;
/ PROD

Both show 29 and 1: the copies start identical. SET TRACE is needed again because each CONNECT goes back to the IsLogActivated setting. / PROD leaves you on WORLD-QA; the run on WORLD is printed, and logged, on this connection.

7. Attach the result to the ticket

The count of step 5 or 6 is the decisive result. DUMP / writes the result you are looking at, without running the query again; AS MD makes it a Markdown table, which ticket systems, wikis and pull requests display as a table:

DUMP / TO ticket_4711_cities AS MD;

The file ticket_4711_cities.md is written in the export folder (the DefaultFolder setting): attach it, or paste its content into the ticket. Two alternatives:

• DUMP / TO ticket_4711_cities AS HTML; writes a table to paste into an email.
• COPY RESULT; copies the result to the clipboard as tab separated text, ready to paste into a chat or a spreadsheet.

8. Find the record afterwards

The log files of the session are in the logs folder: broadsql_WORLD_<yyyymmdd>.log and, for step 6, broadsql_WORLD-QA_<yyyymmdd>.log. Each command you typed is on a line of its own after the prompt, with a timestamp, followed by what BroadSQL printed, results and errors included. A search for the prompt (WORLD>) lists what you ran.

BroadSQL also keeps other records on its own:

• every DUMP ... AS H2 adds a row to BROADSQL_PULL_AUDIT in that local database: source, query, connection, time and row count;
• every DUMP ... AS XLSX or AS ODS maintains a QUERIES worksheet with the source and date of each tab;
• Up and Ctrl+R recall your last 1,000 commands, across sessions.

9. Close the analysis

The ticket now holds the cause (cities stored under old country codes), the extent (30 cities), the comparison with production and the result as a table. The correction is a separate, controlled change: Verify that a data fix changed only the intended rows applies it with a before and after comparison.

Variations

• A value that changes during the incident: REPEAT ... TO <file> AS CSV keeps a timeline of each run (see Watch a value every 30 seconds).
• The list is in a spreadsheet: IN <@excel:<file>:<sheet>:<column>> (see Check a colleague's spreadsheet list against the database).
• Several steps: each query can feed the next, since <@last:...> always reads the most recent result.
• Keep the list for later: DUMP / TO ticket_4711_codes AS CSV; after step 2.

Watch out

• The activity log is a convenience for your own records, not an audit trail: the files are plain text that anyone with access to the folder can read, change or delete. Use it to remember and to explain what you did, not as proof. The log also holds your results: keep it as carefully as the data itself.
• SET TRACE lasts until the next CONNECT. Nothing is logged at the BroadSQL> prompt or in CDF maintenance mode.
• A missing log folder stops logging for the session, with the message "Unable to write to log file".
• <@clipboard> reads one column: two spreadsheet columns copied together are refused.

Clean up

Delete ticket_4711_cities.md from the export folder when the ticket is closed. Keep or delete the log files as your own rules require; LogRetentionDays deletes old ones for you.

Related

• Command activity log
• Patterns and list sources
• Use a list of values from the clipboard
• Check a figure in QA, then in production

Commands used

• CONNECT
• ENV
• DUMP
• COPY RESULT

Found something wrong or unclear on this page? Tell us, with the page address.