topic Re: Generic DB Query Plugin SAP HANA in Extensions

Generic DB Query Plugin SAP HANA

carlos_carreno — Mon, 08 Jan 2024 10:57:07 GMT

Good morning Dynatrace community, for this project, the 'Generic DB Query Plugin' extension was implemented to connect to a standard SAP HANA database 'VBAK', with the sole purpose of counting the number of times a record is repeated in the database.

"Example of a database taken from an internet image."

For the columns in this database, the following queries were executed:

SELECT COUNT(AUDAT) AS CANTIDAD, AUDAT FROM xxxxxx.VBAK where ERNAM='APL_USRPI' and AUART='ZB2B' and AUDAT='01/01/2024' GROUP BY AUDAT SELECT COUNT(AUDAT) AS CANTIDAD, AUDAT FROM xxxxxx.VBAK WHERE ERNAM='APL_USRPI' AND AUART='ZNAC' AND AUDAT>='01/01/2024' GROUP BY AUDAT SELECT COUNT(AUDAT) AS CANTIDAD, AUDAT FROM XXXXX.VBAK WHERE ERNAM ='CNX_ERP_CPI' AND AUART ='ZB2C' AND AUDAT>='01/01/2024' GROUP BY AUDAT

With the goal of bringing me the total number of times the condition is met and thus being able to know that total of records and maintaining control, but the result it yields is the number of times the system goes and performs the query.

With the following configuration in the query in Data Explorer.

custom.db.query:splitBy("dt.entity.custom_device",query_name,column):count:sort(value(avg,descending)):limit(20) custom.db.query.statistics.query.rows:splitBy("dt.entity.custom_device",query_name):count:sort(value(avg,descending)):limit(20)

The 'rows' query was performed in an attempt to establish a relationship between the number of columns versus the records.

Within a time period of 15 minutes, this is the result it yields for me.

I've explored all possible forms in the query, but still feel that something isn't right. I hope to find help, thank you.

"Example of expected results."

Re: Generic DB Query Plugin SAP HANA

Miguel_RinconG — Mon, 08 Jan 2024 13:16:31 GMT

Hello Carlos,

The 'Generic DB Query Plugin' extension is deprecated. The new extension 2.0: https://docs.dynatrace.com/docs/extend-dynatrace/extensions20/data-sources/sql

Please check this example: https://docs.dynatrace.com/docs/extend-dynatrace/extensions20/data-sources/sql/sap-hana-monitoring

Query example:

- subgroup: disk_size_ratio query: SELECT D.DATABASE_NAME as DATABASE_NAME, D.HOST as HOST, SUM(T.TABLE_SIZE)/(SELECT TOP 1 ALLOCATION_LIMIT from SYS.M_HOST_RESOURCE_UTILIZATION) as SizeRatio FROM SYS.M_DATABASE D, SYS.M_TABLES T GROUP BY D.DATABASE_NAME, D.HOST; dimensions: - key: database value: col:DATABASE_NAME - key: host value: col:HOST metrics: - key: sap.hana.db.disk_size_usage value: col:SizeRatio

Re: Generic DB Query Plugin SAP HANA

david_lopes — Mon, 08 Jan 2024 14:34:51 GMT

The issue arises from the use of the :count transformation.

Count means "number of datapoints" reported, so you have 3 datapoints reported in that timeframe.
This is not a useful metric, you should use the actual metric value (with avg, sum, max, etc)

If you strictly need to know the number of rows and doesn't care about the actual query value, use the metric custom.db.query.statistics.query.rows instead.

Re: Generic DB Query Plugin SAP HANA

carlos_carreno — Mon, 08 Jan 2024 20:37:16 GMT

What I really need to measure is how many records that query retrieves. According to the conditions of the requested query, I wish to be sure that running that query and using the 'rows' measure would provide the actual data I need.

When selecting the time period, I want to know exactly how many records there are.

Re: Generic DB Query Plugin SAP HANA

david_lopes — Tue, 09 Jan 2024 13:47:35 GMT

Yes that is what customers use the extension for regularly

You can use the `COUNT(*)` version of your query, in which case of course it returns a single row, but you can get the query value just fine (do not use the count transformation in the metric selector, like I mentioned)

OR you can have a query that retrieves multiple rows and use the metric I mentioned above.

Re: Generic DB Query Plugin SAP HANA

carlos_carreno — Tue, 16 Jan 2024 15:02:22 GMT

Thank you for the options. We've decided to go for extension 2.0 to be on the latest extensions right away and be able to adjust our queries there.