The Management Pack for SCOM uses certain default SQL queries to collect SCOM data and discover SCOM objects. These queries are modified with each version of the Management Pack.
CAUTION! We DO NOT recommend modifying the default database queries unless you are familiar with this type of advanced configuration.
If you choose to modify the default queries at your own risk, they can be found within the following template properties files, which are located at $VCOPS_BASE/user/plugins/inbound/msscom_adapter3/conf/:
sql_db.properties.template – contains default queries for the Database source database type
sql_dw.properties.template – contains the default queries for the Data warehouse source database type
When modifying queries, you will want to re-name the files by removing the .template portion of filename, and then stop and restart data collection for the adapter instance in order for the new properties to take effect.
Each query must return a result that contains the same number of columns with the same names (labels). In addition, you will want to ensure that the default number of parameters (indicated by ?) is retained, in the same order, when modifying the queries. For example, the mainCollectSQL query must have two parameters to specify the time window to retrieve metrics: (pdav.TimeSampled > ? AND pdav.TimeSampled <= ?) ORDER BY TS