Table visualization is not displaying all the columns returned by the SQL query

Grafana Table Panel
1

I have created a Table panel and set up the Non-Time Series, Custom (Repository) query as follows:

SELECT
a.event_id,
b.target_type,
b.target_name,
a.severity,
a.creation_date,
a.last_updated_date,
a.reported_state
FROM
sysman.mgmt$events_latest a,
sysman.mgmt$agents_monitoring_targets b
WHERE
a.target_guid = b.target_guid
AND a.open_status = 1
AND a.closed_date > sysdate
AND a.severity = ‘Fatal’
order by b.target_type, b.target_name;

The visualization is set to Table.

Issue: All the table columns are created but the EVENT_ID column has no data in it. The same SQL run manually shows that the Event_ID is in fact pupulated with data.

Why would this one column not display data?

2

what does the data in Table View show

image
image1797×616 46.9 KB

3

Table view also shows an empty column.
This is an Oracle Database. The Event_ID field has a data_type of “RAW”. Not sure what that means.

4

maybe not a data type supported in the oracle datasource? maybe convert it using CAST_TO_VARCHAR2 ?

Also do you need to see that field in table? not sure how much meaning it has in case it is internal identity like guid

5

I need to pass it as a query string parm in a data link to another dashboard

1 Like
6

most probably it looks something like this when looked at from within Oracle?

8135869AECF44FB280A04033888FD518
7

Yes it does.

Using rawtohex seems to fix the display issue

SELECT
rawtohex(a.event_id) EVENT_ID,
b.target_type,
b.target_name,
a.severity,
a.creation_date,
a.last_updated_date,
a.reported_state
FROM
sysman.mgmt$events_latest a,
sysman.mgmt$agents_monitoring_targets b
WHERE
a.target_guid = b.target_guid
AND a.open_status = 1
AND a.closed_date > sysdate
AND a.severity = ‘Fatal’
order by b.target_type, b.target_name;

I can now pass it as a query parm

/d/f15q_vpNk/event-detail?orgId=1&var-event_id=2EAAAAEA27DBD648E0630C414E0A0B0C

Now I need to convert it back to raw to use it in the detail query

Select
b.target_type,
b.target_name,
a.msg
From
sysman.mgmt$events a,
sysman.mgmt$agents_monitoring_targets b
where
a.target_guid = b.target_guid
AND event_id = $event_id;

8

so it does not work as is when used in the query?

9

It works when used explicitly but not as a variable. I am attempting to determine that the variable actually has the value in it.

Select
b.target_type,
b.target_name,
a.msg
From
sysman.mgmt$events a,
sysman.mgmt$agents_monitoring_targets b
where
a.target_guid = b.target_guid
AND event_id = hextoraw($event_id);

is not working but
Select
b.target_type,
b.target_name,
a.msg
From
sysman.mgmt$events a,
sysman.mgmt$agents_monitoring_targets b
where
a.target_guid = b.target_guid
AND event_id = hextoraw(‘2EAAAAEA27DBD648E0630C414E0A0B0C’);

does work

10

Got it

Select
b.target_type,
b.target_name,
a.msg
From
sysman.mgmt$events a,
sysman.mgmt$agents_monitoring_targets b
where
a.target_guid = b.target_guid
AND event_id = hextoraw(‘$event_id’);

I had to enclose the variable in ’ ’
hextoraw('$event_id');

1 Like