Question & Answer
Question
When restoring archived data to IBM Security Guardium collectors, aggregators or central managers (CM), should I restore only the days I need in my reports?
Answer
In most cases you will need to restore archives for days not directly required by your audit. To be safe, always restore the first day of the month and every day up to the last day needed for your reports.
For instance, if you need a report covering just 2015-02-09 you should restore these archives:
2015-02-01, 2015-02-02, 2015-02-03
2015-02-04, 2015-02-05, 2015-02-06
2015-02-07, 2015-02-08, 2015-02-09
Why? When Guardium logs a single instance of a SQL the data is recorded in several different tables. The parts of the data which are commonly reused are stored in static tables, the parts which are unique or nearly unique are written to dynamic tables. Dynamic tables are time-specific and update frequently, they might log a million rows per day. Static tables are not time-specific and change sporadically, adding maybe a few dozen or a few hundred rows per day.
For efficiency, Guardium does not export or archive complete static tables every day. Those tables might be many gigabytes in size and have tens of millions of rows. We only export or archive the rows which were added yesterday. For safety, we do export and archive complete static tables on the first day of each month, making those export or archive files noticeably larger. For the intervening days it's assumed the aggregator will already have all the older records for those static tables, it just needs to add the new records collected yesterday.
If you restore only one day from the middle of a month, the SQL instances logged in that archive may have pointers to records which do not exist. Report queries may not be able to find the logged SQL instances.
A specific example will illustrate the issue:
Col102.ibm.com archives daily and exports to Agg100.ibm.com each night.
On 2015-02-09 11:34:20 an STAP intercepts "select * from temp234xyz;" run on a monitored DB and forward it to Col102, which applies policy and logs the traffic.
Details of the connection profile like DB_USER and CLIENT_IP are frequently reused and are logged to the static table GDM_ACCESS. This SQL was captured as part of a session which is given a pointer to the GDM_ACCESS record matching that exact connection profile. In this case the ACCESS record was created years ago when Col102 first saw that particular connection profile, while "temp234xyz" is logged as a new record in the static table GDM_OBJECT because Guardium has never seen that table name before.
That night (2015-02-10 @ 02:00) Col102 runs archive. The archive includes all SQL instances recorded between 2015-02-09 00:00:00 and 2015-02-10 00:00:00. It includes new ACCESS records and new OBJECT records for the same period. So the "temp234xyz" record will be in the archive because it's new, but the ACCESS record which was years old will not.
On 2015-02-01 the entire GDM_ACCESS and GDM_OBJECT table were sent with the nightly archive.
Years later, the Guardium admin gets an audit request to look at 2015-02-09 specifically. If they restore only that date "tempXYZ123" might show up in certain reports but in others it may not. Most report queries use GDM_ACCESS, and the record mapping that SQL instance to the session in which it occurred will be missing.
Circumstances like this can lead to unexpected and confusing behavior. To avoid these situations and ensure that all logged data will be visible in your reports, be sure to restore the day you need, the first day of that month and all intervening days too.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22008824