Forum Stats

  • 3,760,170 Users
  • 2,251,658 Discussions
  • 7,871,010 Comments

Discussions

OEM-Grafana plug:How to increase the number of time-series metrics returned by restful API query ?

Kiwi712
Kiwi712 Member Posts: 7 Blue Ribbon

I love the OEM-Grafana App and the Dashboards ! I have some Custom-Repository time-series queries in panels which return datasets that are being limited to 10 metrics and they are always ordered and displayed alphabetically on metric . How can increase this array ceiling to say 20 and alter the sort order based on value rather than metric string-values A-Z order ? I have tried all sorts of things by obviously adjusting the order by in query and adjusting Application API restful property settings on OMS but nothing alters the 10 limit.

basic example:

select rollup_timestamp as time_sec, average as value , key_value as metric

from sysman.mgmt$metric_daily a, sysman.mgmt$target b

where b.target_name = $oem_gf_target_name

and b.target_guid=a.target_guid

and a.metric_column = 'spaceAllocated'

and a.metric_name='tbspAllocation'

ORDER BY 1 ASC;

If I have more than 10 tablespaces in my database then I only see the first 10 in A-Z tablespace-name order.

This the query inspector perspective :

Object

request:Object

url:"api/datasources/proxy/159/em/websvcs/restful/grafana/v1/query"

data:Object

range:Object

targets:Array[1]

isRemoteConnection:false

method:"POST"

response:Array[10] <--- How to change this and how to change order to sort on value rather than metric?

0:Object

target:"BMFDATA"

datapoints:Array[6]

1:Object

target:"EXAMPLES"

datapoints:Array[6]

2:Object

target:"IOPS"

datapoints:Array[6]

3:Object

target:"SCOTT_ARCHIVE"

datapoints:Array[2]

4:Object

target:"SDBA_DATA"

datapoints:Array[6]

5:Object

target:"SDBA_IDX"

datapoints:Array[6]

6:Object

target:"SYSAUX"

datapoints:Array[6]

7:Object

target:"SYSTEM"

datapoints:Array[6]

8:Object

target:"TEMP"

datapoints:Array[6]

9:Object

target:"TPCCTAB"

datapoints:Array[6]

( I have another few tablelspaces with tablespace-names not being included in returnset )

Many thanks for any insight/assistance you can offer. Brett

User_J35RZ
«1

Answers

  • User_J35RZ
    User_J35RZ Member Posts: 1 Green Ribbon

    Hello!

    Did you find a solution?

  • User_8YWFO
    User_8YWFO Member Posts: 4 Red Ribbon

    Hi Kiwi,

    I am also trying to create a OEM data source in grafana and i get an error . Any idea of it.I used the url as http://hostname.domain:7788 and admin username and password.

    OEM version is 13.4 and grafana is v7.3.4

    Error:

    <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <html><head> <title>403 Forbidden</title> </head><body> <h1>Forbidden</h1> <p>You don't have permission to access /em/websvcs/restful/grafana/v1/ on this server.<br /> </p> </body></html>

    Regards,

    Rahul J

  • Kiwi712
    Kiwi712 Member Posts: 7 Blue Ribbon

    @User_J35RZ : No - I have been digging, prodding & poking furiously on this annoying issue since I logged here in this forum with no further luck or inspiration. The key to the solution seems likely to be around how the restful call response is passed back from the OMS handling the RestFUL call and the only way I can see one can influence that is here : https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emadm/application-api-throttling.html . For whatever reason it is passing back ( response ) the set of time series objects and datapoints into that array of fixed size 10 element ( shown in my post above )..

  • Kiwi712
    Kiwi712 Member Posts: 7 Blue Ribbon

    @User_8YWFO : Seems like a setup/config issue at face value ( my Grafana administrator did the data source creation for me based on the field inputs the documentation said is required ). Ensure you have enabled the OEM App for Grafana ( https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emgrf/enable-oracle-enterprise-manager-app-grafana.html ) . More likely to do with your Oracle EMCC admin user which you would have created on the OMS ( note do not use SYSMAN or other user with Super Admin privileges - https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emgrf/add-enterprise-manager-data-source.html). My admin account user ( named "grafana" ) is created in each of my source OMS's with a data suorce per OMS. This user has EM_USER & PUBLIC roles in each OMS. My platform : OEM 13.4 RU5, Grafana 7.0.6, App Plugin V 1.0.2

  • User_K2JKT
    User_K2JKT Member Posts: 2 Green Ribbon

    Hi Folks,

    Did anyone find a resolution to the original posters issue? I am having the same issue where only 10 records are being returned when more then 10 are available. I have confirmed its the REST API that is returning the incorrect result sets. I have read through the API throttling documentation but I did not find anything applicable to 10 records. Any suggestions?

  • Kiwi712
    Kiwi712 Member Posts: 7 Blue Ribbon

    Nothing found yet. My best hope is that a future upgrade to oracle-emcc-app-2.0 + OMS/OMR 13.4 R9 ( latest ) shakes things up regards the 10 row limit and sort order behavior. We recently upgraded to Grafana 7.3.7 from Grafana 7.0.6 so we are primed to be able to go to the latest for the app plugin. Did notice the only change with Grafana 7.3.7 upgrade causes "Unsigned External Plugin" to appear now in various panel design pages to appear - just a benign informational issue due to the fact the Oracle plugin ( at least the older V 1.0.2 ) is not delivered with a valid signature ( https://grafana.com/docs/grafana/latest/plugins/plugin-signatures/ ) .

  • User_K2JKT
    User_K2JKT Member Posts: 2 Green Ribbon

    I had a case open with Oracle since I thought this was odd. However it appears that from Oracle's perspective is that 10 is a limit they set and is the expectation. They are going to be updating the documentation and once I have that I will let you know.

  • Kiwi712
    Kiwi712 Member Posts: 7 Blue Ribbon

    Thanks for that information. I am not so bothered by the choice of 10 as the limit and whether or not it can be altered ( I understand the returned dataset throttling reasoning there ) but the real problem is the sort order . If, say , I wanted to chart over time the growth in size of my top 10 largest tablespaces in a database with 1000 tablespaces then unless those largest 10 tablespace have names beginning with "A" then the whole panel is useless when it is not presenting the information desired. BTW ... I did not even entertain the thought of an SR as I thought Oracle Support is not supporting the plugin . Kudos for taking that step.

  • Satya Chandan-Oracle
    Satya Chandan-Oracle Member Posts: 44 Employee

    Hello All,

    Per update from Developers.

    Currently our code limits the time series query result to have max 10 distinct metrics and each metric can have 10000 datapoints(provided by the property value of oracle.sysman.db.restfulapi.grafana.throttle.timeseries.maxdatapoints).

    In other words, the query can have set of 10000 rows for upto 10 distinct values of the “metric” column in the query.

    Regards,

    Satya

  • Kiwi712
    Kiwi712 Member Posts: 7 Blue Ribbon

    Thanks . Yes that is fine and makes sense in terms of return set throttling but the problem is the sort order. It is fine that only 10 max distinct metrics are returned but how do you control the sort order and thus what ends up being returned in the top 10 distinct metrics ? Returning to the original example above - if we want to display the top 10 tablespaces by allocated size over time the sort order is alphabetic on tablespace name which is pointless with hundreds of tablespaces named A-Z. I only ever get the 10 tablespaces starting with letter "A" rather than the top 10 by ,say, current size. I am not sure how else to to ask this same question without getting an answer that dwells on the fact that only 10 metrics are returned - again I understand that 10 distinct values with 10000 datapoints per metric and I am ok with that but I am asking specifically about controlling the sort order. Thanks Brett