Hello, how can I select 2 fields and group them wi...
# supportr
Romario Lopez C
05/30/2023, 8:00 PMHello, how can I select 2 fields and group them with a clickhouse query?
I'm trying this but the status is not shown in the graph
Copy code
SELECT
toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
stringTagMap['net.host.name'] AS host,
numberTagMap['http.status_code'] as http_status,
count() AS value
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, intervalhttp_statusRomario Lopez C
05/30/2023, 8:07 PMI'm trying to use this field from the trace:
Romario Lopez C
05/30/2023, 8:09 PMSeems that the http_status is not a tag? 🤔 not sure
s
Srikanth Chekuri
05/31/2023, 1:55 AM Copy code
SELECT
count() AS value,
toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
stringTagMap['net.host.name'] AS host,
numberTagMap['http.status_code'] as http_status
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, intervalr
Romario Lopez C
05/31/2023, 5:46 PMWhat's the difference? Just the order of the selecte fields?
Romario Lopez C
05/31/2023, 5:48 PMDidn't work
s
Srikanth Chekuri
06/01/2023, 1:32 AMI think there is conflict between using the value vs http status code for the chart value since both are numbers. The reorder should have worked. I will check. You can also try
toStringr
Romario Lopez C
06/01/2023, 1:33 AMThe reorder left me with the graph I shared. How do I use the
toStrings
Srikanth Chekuri
06/01/2023, 1:34 AM Copy code
SELECT
count() AS value,
toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
stringTagMap['net.host.name'] AS host,
toString(numberTagMap['http.status_code']) as http_status
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, intervalr
Romario Lopez C
06/01/2023, 1:42 AMYes! That worked