Show context for logs not working properly, query not customizable

[alexrosenfeld10]

Hi, I'm looking to report a bug and figure out if the query behind the "Show Context" button when looking at logs is customizable.

Looking at the source code, I don't think it is, but would like to ask in case it's on the roadmap. It'd be very useful. The methods in question are generateQueryForTimestampBackward and generateQueryForTimestampForward.

Specifically, the button I'm asking about is:

When I click this, the queries fail:

Code: 47. DB::Exception: Unknown expression identifier 'undefined' in scope SELECT undefined, FIRST_VALUE(undefined) OVER (ORDER BY undefined ASC ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS timestamp FROM fluent.distributed_idLogs WHERE message LIKE '%Thread%' ORDER BY undefined ASC. (UNKNOWN_IDENTIFIER) (version 24.8.6.70 (official build))

The query being run is:

SELECT timestamp

FROM
(
    SELECT
        undefined,
        FIRST_VALUE(undefined) OVER(ORDER BY undefined ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS timestamp
    FROM fluent.distributed_idLogs

    WHERE message LIKE '%Thread%'

    ORDER BY undefined

)
WHERE undefined = toDateTime64(1736292096141 / 1000, 3)
 FORMAT JSON

Ideally I'd like to figure out two things:

1. How to make this not fail. I'm not sure where the undefined is coming from. The query behind my panel is

SELECT level || + ' ' || message, time, level, facility, node, facility || '-' || node as alias, id as traceId, class, level as logLevel

FROM fluent.distributed_idLogs

WHERE id = '$locator'

2. Secondly, I'd like to be able to customize this query. Specifically, I need to add the id = '$locator' clause to the query, as without it, even if the query worked syntactically, it would be incredibly poor performance without filtering by id.

Thanks in advance for any help!

[Slach]

Could you provide your original query and press copy generated SQL ? (press Show generated sql)

[alexrosenfeld10]

@Slach Sure - the original query is above, repeated here for convenience:

SELECT level || + ' ' || message, time, level, facility, node, facility || '-' || node as alias, id as traceId, class, level as logLevel

FROM fluent.distributed_idLogs

WHERE id = '$locator'

Generated query:

/* grafana dashboard=Locator Logs, user=397 */
SELECT level || + ' ' || message, time, level, facility, node, facility || '-' || node as alias, id as traceId, class, level as logLevel

FROM fluent.distributed_idLogs

WHERE id = ''

[alexrosenfeld10]

Clarifying a few things:

• I'm concatenating level because when downloading the logs users need the level in each line
• I add the other details (facility, node, alias, traceId) because they're useful for my users
• I add the level as logLevel for the same reason (Grafana shows the level as a color on the right, but doesn't make it a one-click option to filter by level)

[lunaticusgreen]

Hey @alexrosenfeld10 Thanks for reporting this issue!

undefined appears there in case of absent value for the Timestamp Column field in Query Settings, could you please confirm if it's empty in your case?

[alexrosenfeld10]

@lunaticusgreen thanks - I did not. This now correctly fires off the request, but I still need to modify the query it generates, as the query is not performant

/* grafana dashboard=Locator Logs, user=397 */

SELECT timestamp

FROM
(
    SELECT
        time,
        LAST_VALUE(time) OVER(ORDER BY time ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) AS timestamp
    FROM fluent.distributed_idLogs

    WHERE message LIKE '%Thread%'

    ORDER BY time

)
WHERE time = toDateTime64(1736292096141 / 1000, 3)
 FORMAT JSON

In order for this to perform I need to add an AND to the WHERE clause. Is that possible?

[Slach]

@alexrosenfeld10 query is not performant, cause original query also not performant

FROM fluent.distributed_idLogs
WHERE message LIKE '%Thread%'

without $timeFilter macros will scan full table

[alexrosenfeld10]

@Slach no, the original query performance is fine, thanks to WHERE id = '$locator'

[Slach]

hm, @lunaticusgreen
should in this case WHERE id = '' pass into timing query?

[alexrosenfeld10]

I would suggest that either the original query is used unmodified except for the time and row adjustments, or a way to customize the clauses is exposed

[alexrosenfeld10]

Hi @Slach @lunaticusgreen wondering if there was any update here? Thanks

[lunaticusgreen]

@Slach I added a simple improvement to add a where clause to the context request, it should do the trick here