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.