SYMPTOMS
How to obtain the number of .PDF and Native types of documents downloads in LegalReview per matter per day.
CAUSE
Not applicable
RESOLUTION
The following script provides information requested:
Use ZyLABAudit
DECLARE @tableName NVARCHAR(100);
DECLARE @rowCnt INT;
DECLARE @sqlStmt NVARCHAR(100);
DECLARE @para NVARCHAR(100);
DECLARE @date VARCHAR(15);
SET @date = 'DATE%';
DECLARE @tbl TABLE(TableName NVARCHAR(100),[RowCount] INT)DECLARE CUR_TABLE CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where Table_Name like 'm[_]____' OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE
INTO @tableName WHILE @@FETCH_STATUS = 0
BEGIN SET @sqlStmt = 'SET @rowCnt = (SELECT COUNT(*) FROM '+ @tableName + ' where RecordType=7 AND ExecutionDate LIKE '''+ @date + ''')'
SET @para = '@rowCnt NVARCHAR(10) OUTPUT'
EXECUTE SP_EXECUTESQL @sqlStmt,@para, @rowCnt = @rowCnt OUTPUT
INSERT INTO @tbl VALUES(@tableName,@rowCnt)
FETCH NEXT FROM CUR_TABLE
INTO @tableName;
END;CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE select [TableName], sum([RowCount]) count from @tbl
group by TableName with rollup
DECLARE @tableName NVARCHAR(100);
DECLARE @rowCnt INT;
DECLARE @sqlStmt NVARCHAR(100);
DECLARE @para NVARCHAR(100);
DECLARE @date VARCHAR(15);
SET @date = 'DATE%';
DECLARE @tbl TABLE(TableName NVARCHAR(100),[RowCount] INT)DECLARE CUR_TABLE CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where Table_Name like 'm[_]____' OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE
INTO @tableName WHILE @@FETCH_STATUS = 0
BEGIN SET @sqlStmt = 'SET @rowCnt = (SELECT COUNT(*) FROM '+ @tableName + ' where RecordType=7 AND ExecutionDate LIKE '''+ @date + ''')'
SET @para = '@rowCnt NVARCHAR(10) OUTPUT'
EXECUTE SP_EXECUTESQL @sqlStmt,@para, @rowCnt = @rowCnt OUTPUT
INSERT INTO @tbl VALUES(@tableName,@rowCnt)
FETCH NEXT FROM CUR_TABLE
INTO @tableName;
END;CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE select [TableName], sum([RowCount]) count from @tbl
group by TableName with rollup
where
DATE - is a date which user wants to get information for and has YYYY-MM-DD format
Additionally, user is capable to obtain more information about other event types by changing RecordType parameter value with one of the following:
0 - create
1 - tag
2 - untag
5 - bulk-tag
6 - produce
7 - download
8 - delete
10 - auto-classification
11 - document list export
12 - tag editing
1 - tag
2 - untag
5 - bulk-tag
6 - produce
7 - download
8 - delete
10 - auto-classification
11 - document list export
12 - tag editing
APPLIES TO
6.6
Comments
0 comments
Article is closed for comments.