History Versus Snapshot Tables
Reporting Database snapshots the device information regularly; depending on the export schedule. If run every six hours, then every six hours, Reporting Database creates the following set of snapshot tables:
- mi_device (the main one)
- mi_device_ios
- mi_device_android
- mi_device_windows_phone
- mi_user, mi_user_ldap_attr
- mi_user_ldap_group
When Reporting Database imports the data, it:
- Replaces the snapshot tables on Reporting Database with this latest imported tables from VSP
- Inserts the imported tables to history tables (mi_device_hst, mi_device_android_hst, et cetera). Each snapshot in the history table is distinguished by the etl_run_ts (this is the export run time) column.
For example, if Ivanti EPMM has 10,000 devices, the snapshot table mi_device should only have 10,000 rows (devices), but mi_device_hst would contain as many snapshots as Reporting Database ever takes. In our example, if Reporting Database runs every six hours, after one day, mi_device_hst would contain 4 * 10,000 = 40,000 rows. If these settings are in effect for one month, the mi_device_hst table would contain 40,000 * 30 days = 1,200,000 rows.
You can use the history tables to create some "stats over time" types of reports:
- Blocked or not compliance devices over time
- Number of devices haven't checked in for the last 4 hours of time"
- Number of devices by their status over time
The history tables do not have many entity relationships to their main tables; mi_device_hst is a superset of mi_device, mi_device_ios_hst is a superset of mi_device_ios, et cetera.
When creating your reports, ignore all tables with a *_stg suffix and with a number suffix like *.1.