Generic DB Query Plugin SAP HANA

[carlos_carreno]

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)

Spoiler

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."

[Miguel_RinconG]

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

[david_lopes]

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.

[carlos_carreno]

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.

[david_lopes · Accepted solution]

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.

[carlos_carreno]

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.