What is the retention period for data in the monitorCounterLog tables? And is this configurable?
MonitorCounterLog tables are dictated by the number specified in the Agent > Log History page under Monitor Logs:
I don't think this is the correct answer. I believe it is around 2500 points. That's why you will see some things have a large number of days of data and others only have a few days. All depends upon the frequency of sampling and the number of recorded points.I'll correct myself on this. The database tables do take from the configuration on the number of days. However when you look at the results of a monitor set it only shows you a maximum of 2,000 points. If you look at a table view you'll see that it is set to 2,000. I think I had a support ticket a few years back that we were able to change this but at a risk of long queries.
Actually I think that is correct. We have 40 days specified there and there are 41 tables. We have some custom queries that hit the Kaseya database directly and we are running into issues with this view almost every morning: [dbo].[vMonitorCounterLog]
Does anyone know when this view is updated each day? The backend cleanup process that deletes these daily tables is happening before the view is updated. This causes our ETL process to fail because the view references a table that no longer exists.
The highlighted section in my previous post, indicates how long these log types will remain in the SQL Database.
From that section you can control event log, monitor log, SNMP logs, and agent log retention.
However, the configuration that dictates when these logs are archived is specified on the System > Configuration page:
The view should only be checking the database tables and populating the information if it exists.
I am not quite understanding the issue at hand, by the time you are checking the view certain MonitorCounterLogYYYYMMDD tables are gone - thus returning no data?
jimmyc I've noticed the same problem with the view vNtEventLog when I run a weekly SQL Query against it. Sometimes it fails with an error pointing towards missing tables and when you look into it there will be missing tables.
To fix I just edit the view removing the badly referenced tables and this fixes the issue.
There doesn't appear to be any pattern as to how or why this happens. I reckon a reapply schema would probably fix the issue but it's quicker to make the manual change.
So there is something that happens daily to update those views. I just haven't figured out what it is or when it happens. I'm not convinced that its happening with the maintenance/cleanup task either because that runs at 4, and my query runs at 5. Unless maintenance is taking that long to complete. Guess I'll just have to profile the db to find it.