11.0.8 Audit Exploration/Dashboard issues & Fix

[TomCognos]

Hi

11.0.8 for some reason html encodes the dashboard entries in the AuditStore. This renders the new audit reporting on dashboards useless as the framework manager package uses substring to work out paths and packages and explorations (dashboards).

11.0.9 does fix this.

Though those stuck on 11.0.8 and also those who now have 11.0.9 and wish to report on previous 11.0.8 dashboard usage these will help

Run these and this will temporarily fix the issue (until another dashboard is run). or 11.0.8 you will need to schedule them every Xmins or so.

*SQL Server

Run this to see if you have any problem entries.

select *
  from COGIPF_RUNREPORT
where (    COGIPF_REPORTNAME like '%%5D%'
          or  COGIPF_REPORTNAME like '%%5B%'
     or  COGIPF_REPORTNAME like '%%20%')
    and COGIPF_LOCALTIMESTAMP > '2017-11-17 14:15:00.001'
  order by COGIPF_LOCALTIMESTAMP desc
         ;

Run these to fix it.

--############################################################################################################
--STEP1A - Decodes the reportname
/*
   select COGIPF_REPORTNAME
     from COGIPF_RUNREPORT
   where (COGIPF_REPORTNAME like '%%5D%'
         or  COGIPF_REPORTNAME like '%%5B%'
         or  COGIPF_REPORTNAME like '%%20%')
*/
   update COGIPF_RUNREPORT
         set COGIPF_REPORTNAME = replace(replace(replace(COGIPF_REPORTNAME, '%5B', '['), '%20', ' '), '%5D', ']')
    where (COGIPF_REPORTNAME like '%%5D%'
          or  COGIPF_REPORTNAME like '%%5B%'
          or  COGIPF_REPORTNAME like '%%20%')

--############################################################################################################
--STEP1B - Updates report name stripping out the path
/*
   select COGIPF_REPORTNAME
     from COGIPF_RUNREPORT
   where left(COGIPF_REPORTNAME,8) = '/content'
      and charindex('/exploration',COGIPF_REPORTNAME,1) >0
      and not (COGIPF_REPORTNAME like '%%5D%'
          or      COGIPF_REPORTNAME like '%%5B%'
          or      COGIPF_REPORTNAME like '%%20%')
*/
   update COGIPF_RUNREPORT
             set COGIPF_REPORTNAME = substring(COGIPF_REPORTNAME,charindex('/exploration', COGIPF_REPORTNAME,1)+1,len(COGIPF_REPORTNAME)-(charindex('/exploration', COGIPF_REPORTNAME,1)-1))    
        where left(COGIPF_REPORTNAME,8) = '/content'
      and charindex('/exploration',COGIPF_REPORTNAME,1) >0
      and not (COGIPF_REPORTNAME like '%%5D%'
         or COGIPF_REPORTNAME like '%%5B%'
         or COGIPF_REPORTNAME like '%%20%')

--############################################################################################################
--STEP2 - Update all column paths url decodeing
/*
   select COGIPF_REPORTPATH
     from COGIPF_RUNREPORT
        where (COGIPF_REPORTPATH like '%%5D%'
         or  COGIPF_REPORTPATH like '%%5B%'
         or  COGIPF_REPORTPATH like '%%20%')
      and charindex('CAMID',COGIPF_REPORTPATH,1) =0
*/
   update COGIPF_RUNREPORT
         set COGIPF_REPORTPATH = replace(replace(replace(COGIPF_REPORTPATH, '%5B', '['), '%20', ' '), '%5D', ']')
    where (COGIPF_REPORTPATH like '%%5D%'
          or  COGIPF_REPORTPATH like '%%5B%'
          or  COGIPF_REPORTPATH like '%%20%')
       and charindex('CAMID',COGIPF_REPORTPATH,1) =0

--############################################################################################################
--STEP3 - Update all package columns url decodeing
/*
   select COGIPF_PACKAGE
     from COGIPF_RUNREPORT
        where (COGIPF_PACKAGE like '%%5D%'
         or  COGIPF_PACKAGE like '%%5B%'
         or  COGIPF_PACKAGE like '%%20%') order by 4 desc
*/
   update COGIPF_RUNREPORT
           set COGIPF_PACKAGE = replace(replace(replace(COGIPF_PACKAGE, '%5B', '['), '%20', ' '), '%5D', ']')
    where ( COGIPF_PACKAGE like '%%5D%'
            or COGIPF_PACKAGE like '%%5B%'
            or COGIPF_PACKAGE like '%%20%')

--############################################################################################################
--STEP4 - Update all model columns url decodeing
/*
   select COGIPF_MODEL
     from COGIPF_RUNREPORT
        where (COGIPF_MODEL like '%%5D%'
         or  COGIPF_MODEL like '%%5B%'
         or  COGIPF_MODEL like '%%20%') order by 4 desc
*/
   update COGIPF_RUNREPORT
         set COGIPF_MODEL = replace(replace(replace(COGIPF_MODEL, '%5B', '['), '%20', ' '), '%5D', ']')
    where ( COGIPF_MODEL like '%%5D%'
            or COGIPF_MODEL like '%%5B%'
       or COGIPF_MODEL like '%%20%')

--############################################################################################################