Problem: Analytics Link reports not loading after upgrading to 6.3.0.x
Cause: We discovered in about version 6.3.0.127 that the ActionID that define the events for the Audit database were changed thus causing the Link Report in the Analytics tile to not function. The data for ActionID 107 and higher were incremented by 1, thus causing the reports to search for invalid data.
Prerequisite(s):
- webNetwork upgraded to 6.3.0.197 or higher
- Analytics setup in MySQL or MS SQL server
- Know your Analytics database/schema name
- Access to native SQL server tools
Solution(s): You'll need to manually adjust the logs for the data range that they were effected. The following example will show you how to fix for MySQL. Make sure to replace swaudit for your own database/schema name if different. For MS SQL, replace swaudit.auditdata for dbo.auditdata.
- Verify webNetwork servers are 6.3.0.197 or higher
- Make a backup of your Audit database using native SQL tools
- Connect to SQL server using native tools
- Run commmand - SELECT * FROM swaudit.auditdata where actionid=107 and data3 is not null;
- Look for the first entry in the results and record the datetime field to get the start date (ex: 2015-07-01 00:01:55)
- Look for the last entry in the results and note the datetime filed to get the end date (ex: 2015-10-02 23:53:09)
- Run command - update swaudit.auditdata set actionid=106 where (actionid=107 and (datetime>="2015-07-01 00:01:55" and datetime <="2015-10-02 23:53:09" ));
- Run command again, but increment actionid - update swaudit.auditdata set actionid=107 where (actionid=108 and (datetime>="2015-07-01 00:01:55" and datetime <="2015-10-02 23:53:09" ));
- Repeat until step 8 until actionid=120 where (actionid=121 and (datetime>="2015-07-01 00:01:55" and datetime <="2015-10-02 23:53:09" ));
- Test Analytics Link report and reports should load normally again