Hello everyone,
Can you suggest anyone on the below requirement.
I need a report like "items deleted, moved, permission change and current permissions of the items in a folder"
We are using SQL server 2008.
Thank you in advance.
That’s not hard, but if you want the details of what permissions were changed and the current permissions, I’d break this down into 3 reports:
I have reports for each of these, but they’re written in Oracle. The conversion to SQL Server is not that difficult. I’ve done it for a few on a small installation I occasionally had to work with. I found the necessary conversion methods on the internet.
There are a couple of ways to go with everything under a folder. I use the DTREEANCESTORS. I set the top Parent you have selected as the “ANCESTORID” and check each “DATAID” against the DAUDITNEW.DATAID with AUDITID in (2, 3, 9). If you have RecycleBin installed, a Delete by a User will be logged as a “MOVE” to the RecycleBin.
Permissions: If you think the Documents would be different than the Parent Folder, you’d need to list everything. If you think they’d be the same, then only check for Sub-Folders. It would produce a smaller report. You may need to know if permissions were changed for an item, sub-item, or this item and sub-items. A reference to a Sub-item would come from changing the Perms on a Folder(A document can’t have sub-items).
Attached is a text file containing the Oracle SQL for the three(3) reports. In my reports, I tend to pull in everything I need (or maybe “want” is more accurate) to validate the information. You’re free to strip out what you don’t want. I didn’t use the DTREEANCESTORS in these reports.
Colin J. Schmidt
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Wednesday, June 21, 2017 4:56 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Items Audit information
Items Audit information
Posted by Pati, Suchitra Kumar On 06/21/2017 04:53 AM
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center CS16
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
If you have WebReports and access to the Report pack, there are the types of reports that come with the WR Report pack...
https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=51494605&objAction=browse
Hi Colin,
Thank you very much for your reply.
I also need the report for the deleted items from a folder. So could you please advise me on this?
Best Regards,
Suchitra.
That is more difficult and time consuming. What has to be done is the use of “like” or REGEXP using the CS path notation:
Enterprise:1stMain Folder:2ndsubfolder:
And search for the path found in DAUDITNEW.VALUE1 and “Recycle%” found in VALUE2 along with AUDITID = 2 or 3.
This type of query can take a long time because of the REGEXP operations. If you knew what time frame, you could shorten it by a technique I’ve used. I get the starting EVENTID for each month of each year (and keep a spreadsheet with these values) and use that as another delimiter instead of using a comparison of date values whether by Year or Year-Mth.
Select da.*
From DAUDITNEW da
Where da.EVENTID > <number> and da.AUDITID in (2, 3) and da.VALUE1 like ‘<path>%’ and da.VALUE2 like ‘Recycle%’
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Wednesday, June 21, 2017 10:42 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: [EXTERNAL] - RE Items Audit information
[EXTERNAL] - RE Items Audit information
Posted by Pati, Suchitra Kumar On 06/21/2017 10:27 AM
Topic:
I have also tried the same thing by filtering the Value2 like "Recycl%" and AuditID in (2,3).from DAuditNew table. But I did not found any entries for recyclebin.
Please suggest, is there any other way i can found the deleted items from a folder?
Hey Pati could you try this hope it might work for you.
select Dataid
From DtreeCore D,Dauditnew T
where D.dataid=t.dataid andOwnerId=<Recycle Bin> andValue1 like 'Enterprise:Folder1:Folder1'
Regards,
Ankur
One more effective method is to use one more TABLE: GDCRECYCLEBIN
Check the Immediate parent folder ID against the Table: GDCRECYCLEBIN.PARENTID = DTREE.PARENTID
If it returns values in the GDCRECYCLEBIN:ID, you have a winner!
I just found what that table contained. That’ll save our searches too.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, June 22, 2017 5:07 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: RE [EXTERNAL] - RE Items Audit information
RE [EXTERNAL] - RE Items Audit information
Posted by Pati, Suchitra Kumar On 06/22/2017 05:03 AM
