Procedure: [AdventureWorks2016CTP3].[dbo].[AUDIT_prc_AggregateReport]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Type   P 
 Encrypted   
 ID   1291867669 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@DATE_FROM   nvarchar 50 Input  
@DATE_TO   nvarchar 50 Input  
@WHERE   nvarchar 4000 Input  
@ROW_COUNT   int 4 Input  
@GroupByDate   tinyint 1 Input  
@GroupByTableName   bit 1 Input  
@GroupByMODIFIED_BY   bit 1 Input  
@GroupByACTION   bit 1 Input  
@GroupByAPPLICATION   bit 1 Input  
@GroupByCOMPUTER   bit 1 Input  

Collapse image Objects that depend on [dbo].[AUDIT_prc_AggregateReport]


Object name Object type Dep level
[dbo].[AUDIT_prc_DeleteArchitecture] Procedure 1
Total 1 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AUDIT_prc_AggregateReport]
    @DATE_FROM             nvarchar(50)    = NULL,
    @DATE_TO             nvarchar(50)    = NULL,
    @WHERE                nvarchar(4000)    = NULL,
    @ROW_COUNT             int             = NULL,
    @GroupByDate         tinyint         = 1,
    @GroupByTableName     bit             = 0,
    @GroupByMODIFIED_BY bit             = 0,
    @GroupByACTION         bit             = 0,
    @GroupByAPPLICATION bit             = 0,
    @GroupByCOMPUTER     bit             = 0
AS
DECLARE    @sqlstr nvarchar(4000)
DECLARE    @DateExpression varchar(8000)
DECLARE    @DateFieldName varchar(20)
DECLARE @SearcheableName nvarchar(261)
declare @len int
declare @ver7 bit
declare @ver2000 bit
declare @WhereSql nvarchar(4000)
declare @cmptlvl int
Select @cmptlvl = t1.cmptlevel 
from master.dbo.sysdatabases t1
where t1.[name]=DB_NAME()
set @ver7 = 0
IF @cmptlvl < 80 set @ver7 = 1
set @ver2000 = 0
IF @cmptlvl < 90 set @ver2000 = 1
IF @GroupByDate not in (0,1,2,3,4) 
BEGIN
  RAISERROR ('@GroupByDate must be one of: 0,1,2,3,4',16,1)
  RETURN -1
END
if (select count(*) from ##Filter where [index]='DATABASE') = 0
    insert into ##Filter([index], [value]) values('DATABASE', '%')
if (select count(*) from ##Filter where [index]='TABLE_NAME') = 0
    insert into ##Filter([index], [value]) values('TABLE_NAME', '%')
if (select count(*) from ##Filter where [index]='TABLE_OWNER') = 0
    insert into ##Filter([index], [value]) values('TABLE_OWNER', '%')
if (select count(*) from ##Filter where [index]='USER_NAME') = 0
    insert into ##Filter([index], [value]) values('USER_NAME', '%')
if (select count(*) from ##Filter where [index]='ACTION_ID') = 0
    insert into ##Filter([index], [value]) values('ACTION_ID', '%')
if (select count(*) from ##Filter where [index]='HOST_NAME') = 0
    insert into ##Filter([index], [value]) values('HOST_NAME', '%')
if (select count(*) from ##Filter where [index]='APP_NAME') = 0
    insert into ##Filter([index], [value]) values('APP_NAME', '%')
SET @DateExpression = 
  CASE
   WHEN @GroupByDate = 0 
    THEN ''
   WHEN @GroupByDate = 1 
    THEN 'LEFT(CONVERT(varchar(20), convert(datetime,MODIFIED_DATE), 100),14) + RIGHT(CONVERT(varchar(20), convert(datetime,MODIFIED_DATE), 100),2) '
   WHEN @GroupByDate = 2
    THEN 'CONVERT(varchar(20), CONVERT(datetime,MODIFIED_DATE), 107) '
   WHEN @GroupByDate = 3 
    THEN 'LEFT(CONVERT(varchar(20), CONVERT(datetime,MODIFIED_DATE), 107),4)+RIGHT(CONVERT(varchar(20), CONVERT(datetime,MODIFIED_DATE), 107),4) '
   WHEN @GroupByDate = 4
    THEN 'RIGHT(CONVERT(varchar(20), CONVERT(datetime,MODIFIED_DATE), 107),4) '
  END  
SET @DateFieldName = 
  CASE
   WHEN @GroupByDate = 0 
    THEN ''
   WHEN @GroupByDate = 1 
    THEN ' AS ''Hour'''
   WHEN @GroupByDate = 2
    THEN ' AS ''Date'''
   WHEN @GroupByDate = 3 
    THEN ' AS ''Month'''
   WHEN @GroupByDate = 4
    THEN ' AS ''Year'''
  END  
SET @sqlstr = '
select TOP'+STR(CASE WHEN @ROW_COUNT is null THEN 99999 ELSE @ROW_COUNT END)+' * from (
SELECT sum(DATA_COUNT) AS [#], t.[DATABASE] as ''Database'''+
 CASE
  WHEN @GroupByTableName = 0 THEN ''
  ELSE ', TABLE_NAME as [Table name], TABLE_SCHEMA as [' +
    CASE @ver2000 WHEN 1 THEN 'Owner' ELSE 'Table schema' END +']'
 END +
 CASE
  WHEN @GroupByMODIFIED_BY = 0 THEN ''
  ELSE ', MODIFIED_BY as [Modified by]'
 END +
 CASE
  WHEN @GroupByACTION = 0 THEN ''
  ELSE ', CASE t.AUDIT_ACTION_ID 
              WHEN 1 THEN ''Update'' 
              WHEN 2 THEN ''Insert'' 
              WHEN 3 THEN ''Delete'' 
          END AS [Action]'
 END +
 CASE
  WHEN @GroupByAPPLICATION = 0 THEN ''
  ELSE ', APPLICATION as [Application]'
 END +
 CASE
  WHEN @GroupByCOMPUTER = 0 THEN ''
  ELSE ', COMPUTER as [Computer]'
 END +
 CASE
  WHEN @DateExpression <> '' 
  THEN ', '
  ELSE ''
 END +
@DateExpression+
@DateFieldName
set @sqlstr = @sqlstr +
 ' FROM (
        SELECT 
              [DATABASE],
              TABLE_NAME,
              TABLE_SCHEMA,
              AUDIT_ACTION_ID, 
              MODIFIED_BY, 
              CONVERT(varchar(20), MODIFIED_DATE, 113) AS MODIFIED_DATE,
              HOST_NAME AS COMPUTER,
              APP_NAME as APPLICATION,
                 count(distinct convert(nvarchar(100), t.AUDIT_LOG_TRANSACTION_ID)) [DATA_COUNT]
        from dbo.AUDIT_LOG_TRANSACTIONS t 
            inner join dbo.AUDIT_LOG_DATA d
            on t.AUDIT_LOG_TRANSACTION_ID=d.AUDIT_LOG_TRANSACTION_ID
        group by 
            [DATABASE] ,
            [TABLE_NAME] ,
            [TABLE_SCHEMA] ,
            [AUDIT_ACTION_ID] ,
            [HOST_NAME] ,
            [APP_NAME] ,
            [MODIFIED_BY] ,
            [MODIFIED_DATE] 
    ) t
    inner join ##Filter f1 on f1.[index]=''TABLE_NAME'' and t.TABLE_NAME like Replace(f1.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f2 on f2.[index]=''TABLE_OWNER'' and t.TABLE_SCHEMA like Replace(f2.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f3 on f3.[index]=''APP_NAME'' and t.APPLICATION like Replace(f3.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f4 on f4.[index]=''HOST_NAME'' and t.COMPUTER like Replace(f4.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f5 on f5.[index]=''USER_NAME'' and t.MODIFIED_BY like Replace(f5.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f6 on f6.[index]=''ACTION_ID'' and Cast(t.AUDIT_ACTION_ID as char(1)) like Replace(f6.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    inner join ##Filter f7 on f7.[index]=''DATABASE'' and t.[DATABASE] like Replace(f7.[value]' + case @ver7 when 0 then ' collate database_default' else '' end + ', ''['', ''[[]'')
    where [DATA_COUNT]=[DATA_COUNT]
' +
 CASE
  WHEN @DATE_FROM is NULL THEN ''
  ELSE ' AND CONVERT(DATETIME,MODIFIED_DATE) >= '''+CONVERT(varchar(20),@DATE_FROM,120)+''''
 END +
 CASE
  WHEN @DATE_TO is NULL THEN ''
  ELSE ' AND CONVERT(DATETIME,MODIFIED_DATE) < '''+CONVERT(varchar(20),@DATE_TO,120)+''''
 END +
CASE
  WHEN @DateExpression = ''  THEN ' GROUP BY '
  ELSE ' GROUP BY ' + @DateExpression + ','
 END
 + '[DATABASE], ' +
 CASE WHEN @GroupByTableName     = 1     THEN ' TABLE_SCHEMA, TABLE_NAME,'     ELSE '' END +
 CASE WHEN @GroupByMODIFIED_BY     = 1     THEN ' MODIFIED_BY,'     ELSE '' END +
 CASE WHEN @GroupByACTION     = 1     THEN ' AUDIT_ACTION_ID,'     ELSE '' END +
 CASE WHEN @GroupByAPPLICATION     = 1     THEN ' APPLICATION,'     ELSE '' END +
 CASE WHEN @GroupByCOMPUTER     = 1     THEN ' COMPUTER,'     ELSE '' END
set @len = len(@sqlstr)
if substring(@sqlstr, @len, 1) = ','
begin
    set @sqlstr = substring(@sqlstr,1,@len-1)
end
set @sqlstr = @sqlstr + ') [table]'
if @WHERE IS NOT NULL
begin
    set @WhereSql = @sqlstr+' where '+@WHERE
    exec sp_executesql @WhereSql
end
else
begin
    exec sp_executesql @sqlstr
end
RETURN @@ERROR
GO

Collapse image See also


List of procedures