Dear Roger,

You need to install the custom AppLog plugin in the Site24x7 Server Monitoring agent to achieve this. The plugin will execute every 5 minutes once. During that time, the configured query in the plugin will execute and pull the necessary information from the database and write it into a local file. Our server agent will read the files and send them to the Site24x7 AppLogs platform.

For example, if you want to take performance metrics (average query runtime, error count) of each schema in your MySQL database, then we can use the below query.

Query : SELECT schema_name as TableName, SUM(count_star) as StatementCount, ROUND((SUM(sum_timer_wait) / SUM(count_star))/1000000) as AvgRunTime, SUM(sum_errors) as ErrorCount FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;

First, create a log type using the below sample log and log pattern.

SampleLog

{"TableName": "user", "StatementCount":180, "AvgRunTime": 2682, "ErrorCount": 12}
{"TableName": "contact", "StatementCount":280, "AvgRunTime": 16182, "ErrorCount": 10}
{"TableName": "invoice", "StatementCount":300, "AvgRunTime": 6282, "ErrorCount": 11}

Log Pattern

json $DateTime:date:agent_time$ $TableName$ $AvgRunTime:number$ $ErrorCount:number$ $StatementCount:number$

After created the log type, please create the log profile with '/opt/site24x7/monagent/temp/scriptout/query-output*.log' as file path and associate the server monitor agent.

Next step, please deploy the attached "log_query_plugin.py" plugin in the server agent. Create a folder in the "/opt/site24x7/monagent/plugins/log_query_plugin" and place your plugin script file in this folder. Make sure the name of the file and the folder name are identical.

Note: We have used MySQL database in the script, so changing the configuration depends on your database.

Regards,

Magesh Rajan