visualize json data in table in grafana for zabbix data source

[Neelam-zanvar]

Describe the bug I have a zabbix item which returns data in following format [ { "QUERY": "select from mxDrvInfo where mxType=:va and mxKind=:vb and mxTenant=:vc", "CPU_TIME": "2703125", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" }, { "QUERY": "select from mxVer6 where mxOid=:va", "CPU_TIME": "2640625", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" }, { "QUERY": "SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE '/' AND o.object_type IN ('TABLE', 'SYNONYM', 'VIEW') ORDER BY table_type, table_schem, table_name ", "CPU_TIME": "1875000", "SCHEMA": "X3DPASSADMINUSER\r\n" }, { "QUERY": "select * from ( select persistent0.id as id138, persistent0_.completed_date as completed_date238, persistent0_.creation_date as creation_date338, persistent0_.max_duration as max_duration438, persistent0_.remaining_tries as remaining_tries538, persistent0_.serialized_event as serialized_event638, persistent0_.started_date as started_date738, persistent0_.status as status838, persistent0_.tenant_id as tenant_id1338, persistent0_.timeout_date as timeout_date938, persistent0_.trigger_date as trigger_date1038, persistent0_.type as type1138, persistent0_.version as version1238 from social_persistentevent persistent0 where (persistent0_.starteddate is null) and persistent0.remainingtries>0 and (persistent0.triggerdate is null or persistent0.triggerdate<:1 ) order by persistent0.creation_date asc ) where rownum <= :2 ", "CPU_TIME": "1375000", "SCHEMA": "X3DCOMMENT_DBUSER\r\n" }, { "QUERY": "select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'", "CPU_TIME": "937500", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" }, { "QUERY": "select from mxField where mxParent=:va and mxTenant=:vb", "CPU_TIME": "937500", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" }, { "QUERY": "select from mxDesc where mxOid=:va and mxKind=:vb and mxTenant=:vc", "CPU_TIME": "875000", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" }, { "QUERY": "SELECT NULL AS TABLE_CAT, T.OWNER AS TABLE_SCHEM, T.TABLE_NAME AS TABLE_NAME, T.COLUMN_NAME AS COLUMN_NAME, DECODE(SUBSTR(T.DATA_TYPE, 1, 9), 'TIMESTAMP', DECODE(SUBSTR(T.DATA_TYPE, 10, 1), '(', DECODE(SUBSTR(T.DATA_TYPE, 19, 5), 'LOCAL', -102, 'TIME ', -101, 93), DECODE(SUBSTR(T.DATA_TYPE, 16, 5), 'LOCAL', -102, 'TIME ', -101, 93)), 'INTERVAL ', DECODE(SUBSTR(T.DATA_TYPE, 10, 3), 'DAY', -104, 'YEA', -103), DECODE(T.DATA_TYPE, 'BINARY_DOUBLE', 101, 'BINARY_FLOAT', 100, 'BFILE', -13, 'BLOB', 2004, 'CHAR', 1, 'CLOB', 2005, 'COLLECTION', 2003, 'DATE', 93, 'FLOAT', 6, 'LONG', -1, 'LONG RAW', -4, 'NCHAR', -15, 'NCLOB', 2011, 'NUMBER', 2, 'NVARCHAR', -9, 'NVARCHAR2', -9, 'OBJECT', 2002, 'OPAQUE/XMLTYPE', 2009, 'RAW', -3, 'REF', 2006, 'ROWID', -8, 'SQLXML', 2009, 'UROWID', -8, 'VARCHAR2', 12, 'VARRAY', 2003, 'XMLTYPE', 2009, DECODE((SELECT A.TYPECODE FROM ALL_TYPES A WHERE A.TYPE_NAME = T.DATA_TYPE AND ((A.OWNER IS NULL AND T.DATA_TYPE_OWNER IS NULL) OR (A.OWNER = T.DATA_TYPE_OWNER)) ), 'OBJE", "CPU_TIME": "812500", "SCHEMA": "X3DPASS_TOKENUSER\r\n" }, { "QUERY": "insert into logs (ds_actionId, ds_class, ds_DATE, ds_LEVEL, ds_line, ds_MESSAGE, ds_method, ds_ip, ds_sessionId, ds_ssoId, ds_TYPE, ds_USER, ds_id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 )", "CPU_TIME": "718750", "SCHEMA": "X3DPASS_ADMINUSER\r\n" }, { "QUERY": "declare in_owner varchar2(256) := null; in_name varchar2(256) := null; in_column varchar2(256) := null; xyzzy SYS_REFCURSOR; begin in_owner := :1 ; in_name := :2 ; in_column := :3 ; open xyzzy for SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, DECODE(substr(t.data_type, 1, 9), 'TIMESTAMP', DECODE(substr(t.data_type, 10, 1), '(', DECODE(substr(t.data_type, 19, 5), 'LOCAL', -102, 'TIME ', -101, 93), DECODE(substr(t.data_type, 16, 5), 'LOCAL', -102, 'TIME ', -101, 93)), 'INTERVAL ', DECODE(substr(t.data_type, 10, 3), 'DAY', -104, 'YEA', -103), DECODE(t.data_type, 'BINARY_DOUBLE', 101, 'BINARY_FLOAT', 100, 'BFILE', -13, 'BLOB', 2004, 'CHAR', 1, 'CLOB', 2005, 'COLLECTION', 2003, 'DATE', 93, 'FLOAT', 6, 'LONG', -1, 'LONG RAW', -4, ", "CPU_TIME": "625000", "SCHEMA": "X3DPASS_TOKENUSER" } ]

I want to create a table in grafana with 3 columns query, cpu time and schema

Expected behavior The latest data from zabbix item has to be loaded in tabular form in grafana

Screenshots with the above configurations i am able to only extract one row, i need 10 such rows. Without the json transformation the data comes as below I am interested in only the first row as that refers to the LATEST zabbix item. And split 0{} as the first row of table { "QUERY": "select * from mxDrvInfo where mxType=:va and mxKind=:vb and mxTenant=:vc", "CPU_TIME": "2593750", "SCHEMA": "X3DSPACE_ORACLEUSER\r\n" } 1{} as 2nd row and so on upto 9{} to display the top the queries

Network data If it's related to metric data visualization would be great to get the raw query and response for the network request (check this in browser dev tools network tab, there you can see metric requests, please include the request body and request response)

Software versions

Grafana	Zabbix	Grafana-Zabbix Plugin
10.0.3	6.4.4

[zoltanbedi]

Hi @Neelam-zanvar, Let me understand this better. What is stopping you from changing the limit to 10 in the first screenshot?

[Neelam-zanvar]

if i change the limit to 10, it is displaying the 0'th entry for the last 10 results from zabbix

And not the latest data

[Neelam-zanvar]

i even tried using grafana variables, where in i am saving the zabbix query result in variable and then using it in text widget in the dashboard. But even that is not showing me the latest data. It is randomly showing the previous values

Here as you see the CPU time in both the tables in not matching. The first table is using zabbix json transformation and the second one is using variables.

In the second case i have transformed json to html table on zabbix side itself and then sent it to grafana variable

[zoltanbedi]

can you show the query you are using?

[Neelam-zanvar]

Which Query? the zabbix one?

[zoltanbedi]

Which Query? the zabbix one?

yes

[Neelam-zanvar]

SELECT sql_text as Query, CPU_TIME ,PARSING_SCHEMA_NAME as Schema from v$sql WHERE PARSING_SCHEMA_NAME IN ('SOME NAMES HERE') ORDER by(CPU_TIME) DESC FETCH FIRST 10 ROWS ONLY;

[Neelam-zanvar]

Here is the javascript to convert json to html

// Parse the JSON data var jsonData = JSON.parse(value);

// Initialize the table with CSS styles for borders var table = '

'; table += ''; table += ''; table += '';

// Iterate through the JSON data for (var i = 0; i < jsonData.length; i++) { var query = jsonData[i].QUERY; var cpuTime = jsonData[i].CPU_TIME; var owner = jsonData[i].SCHEMA;

// Create a new row in the table with border styles
table += '<tr>';
table += '<td style="border: 1px solid #000; padding: 8px;">' + query + '</td>';
table += '<td style="border: 1px solid #000; padding: 8px;">' + cpuTime + '</td>';
table += '<td style="border: 1px solid #000; padding: 8px;">' + owner + '</td>';
table += '</tr>';

}

// Close the table table += '

Query	CPU Time	Owner

';

// Set the processed value as the HTML table return table;

[pornpotp]

Hi @Neelam-zanvar , Now I used javascript (above) to convert json to html. But I don't know How to t data from zabbix item has to be loaded in tabular form in grafana

[shubhamkhaiwal]

Is there any resolution for this as i am also stuck at the same issue. thanks