nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Determine if any items have been deleted or moved from a folder/sub-folders

Walt_Ruppar

I have searched all over this forum for a query that will determine if any items have been deleted from a folder/sub-folders and have found some bits and pieces, but not the actual query I need. So far, this query is giving me good results on all audit events for a folder/sub-folders/sub-items, but it leaves out any deletions or moves.

select *

from DAuditNew DA where DA.DataID in ( select DT.DataID from Dtree DT start with DT.DataID = %1 CONNECT BY PRIOR DT.DataID = DT.ParentID ) ORDER BY DA.EventID DESC

I can see that DAuditNew.Value1 holds the 'before' folder location of the object, i.e. "Parent Folder:Sub-Folder:ObjectName" and DAuditNew.Value2 holds the location in the Recycle Bin where the object was moved, i.e. "Recycle Bin:Year:Month:Day:ObjectName".

Furthermore, I see that DAuditNew.AuditID's are as follows:

45 = Recylce

3 = Move

2 = Delete

Now, how do I tie all of this info together into one query which would tell me if anything was deleted or moved from a Folder/Sub-folders?

Find more posts tagged with

Comments

Colin_Schmidt

select *

from DAuditNew DA where DA.auditid in (2, 3, 45) and DA.DataID in ( select DT.DataID from Dtree DT start with DT.DataID = %1 CONNECT BY PRIOR DT.DataID = DT.ParentID ) ORDER BY DA.EventID DESC

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Thu, Oct 9, 2014 at 1:12 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Determine if any items have been deleted or moved from a folder/sub-folders Posted bywalter.ruppar@capitalone.com (Ruppar, Walt)On 10/09/2014 12:59 PM

I have searched all over this forum for a query that will determine if any items have been deleted from a folder/sub-folders and have found some bits and pieces, but not the actual query I need. So far, this query is giving me good results on all audit events for a folder/sub-folders/sub-items, but it leaves out any deletions or moves. select *
from DAuditNew DA where DA.DataID in ( select DT.DataID from Dtree DT start with DT.DataID = %1 CONNECT BY PRIOR DT.DataID = DT.ParentID ) ORDER BY DA.EventID DESC

I can see that DAuditNew.Value1 holds the 'before' folder location of the object, i.e. "Parent Folder:Sub-Folder:ObjectName" and DAuditNew.Value2 holds the location in the Recycle Bin where the object was moved, i.e. "Recycle Bin:Year:Month:Day:ObjectName".

Furthermore, I see that DAuditNew.AuditID's are as follows:
45 = Recylce
3 = Move
2 = Delete

Now, how do I tie all of this info together into one query which would tell me if anything was deleted or moved from a Folder/Sub-folders?
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Walt_Ruppar

Thanks Colin, I too had tried that, but it doesn't appear to be as simple as adding "DA.auditid in (2, 3, 45) and" to the where clause... This only results in finding objects which have been moved within the structure. It actually doesn't find any of the items that have been moved out of the structure, deleted, nor recycled...

What I am seeing is that once an object is moved to the recycle bin, the object's Parent Id changes in DTree, so the connect by prior does not find the object anymore.

I am at a loss of how to do this without attempting to run a very costly query such as:

select *

from DAuditNew DA where DA.Value1 LIKE '%Folder%' and DA.Value2 LIKE '%Recycle Bin%'

Any other thoughts?

eLink User

to find any object in a volume (Recycle bin,Projects,Discussions,personal workspace,Enterprise workspace) are all volumes,with a parentid of -1.So to find any object residing in a recycle volume one can do this
select ownerid,dataid,subtype,name from dtree where subtype=3030 //find the ownerid of recycle bin

select * from dtree where ownerid = <ownerid form the above query>

now that will be all the stuff in the deleted volume so you could filter by subtype for deleted folders,when it was deleted etc then you could

basically write it to go to duaditnew for the "To" "From" kind.Colin would be able to probably write you up a better one.

I just know the volume concept and whether it will help you or not I know not....

..Appu

I wonder how one could debug livelink if Fiddler, Wireshark and Softerra LDAP browser was not invented and not free

On Thu, Oct 9, 2014 at 4:20 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Determine if any items have been deleted or moved from a folder/sub-folders Posted bywalter.ruppar@capitalone.com (Ruppar, Walt)On 10/09/2014 05:18 PM

Thanks Colin, I too had tried that, but it doesn't appear to be as simple as adding "DA.auditid in (2, 3, 45) and" to the where clause... This only results in finding objects which have been moved within the structure. It actually doesn't find any of the items that have been moved out of the structure, deleted, nor recycled... What I am seeing is that once an object is moved to the recycle bin, the object's Parent Id changes in DTree, so the connect by prior does not find the object anymore. I am at a loss of how to do this without attempting to run a very costly query such as: select *

from DAuditNew DA where DA.Value1 LIKE '%Enterprise:Folder:SubFolder%'

Any other thoughts?

[To post a comment, use the normal reply function]Topic:Determine if any items have been deleted or moved from a folder/sub-foldersForum:Content Server LiveReports ForumContent Server:Knowledge Center

Colin_Schmidt

That may be the very type of query you have to do. I'll look at my own data to see what I'd do looking for that.

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Thu, Oct 9, 2014 at 5:20 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Determine if any items have been deleted or moved from a folder/sub-folders Posted bywalter.ruppar@capitalone.com (Ruppar, Walt)On 10/09/2014 05:18 PM

Thanks Colin, I too had tried that, but it doesn't appear to be as simple as adding "DA.auditid in (2, 3, 45) and" to the where clause... This only results in finding objects which have been moved within the structure. It actually doesn't find any of the items that have been moved out of the structure, deleted, nor recycled... What I am seeing is that once an object is moved to the recycle bin, the object's Parent Id changes in DTree, so the connect by prior does not find the object anymore. I am at a loss of how to do this without attempting to run a very costly query such as: select *

from DAuditNew DA where DA.Value1 LIKE '%Enterprise:Folder:SubFolder%'

Any other thoughts?

[To post a comment, use the normal reply function]
Topic:	Determine if any items have been deleted or moved from a folder/sub-folders
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Colin_Schmidt

Attached are a couple of variations of a query to find those "moved" to "Recycle Bin". One uses "LIKE" and the other uses a SUBSTR and an EQUAL operation. I don't know if there's any other way to know what's behind Door #2 ("Recycle Bin:.....").

Expert SQL writers would know far better. You'll have to time them to figure out which one is more efficient.

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Thu, Oct 9, 2014 at 10:00 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Determine if any items have been deleted or moved from a folder/sub-folders 2 Posted bycolin.schmidt@mwv.com (Schmidt, Colin)On 10/09/2014 10:00 PM

That may be the very type of query you have to do. I'll look at my own data to see what I'd do looking for that.

Colin J. Schmidt
Opentext Content Server Support Admin
colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Thu, Oct 9, 2014 at 5:20 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Re Determine if any items have been deleted or moved from a folder/sub-folders Posted bywalter.ruppar@capitalone.com (Ruppar, Walt)On 10/09/2014 05:18 PM

Thanks Colin, I too had tried that, but it doesn't appear to be as simple as adding "DA.auditid in (2, 3, 45) and" to the where clause... This only results in finding objects which have been moved within the structure. It actually doesn't find any of the items that have been moved out of the structure, deleted, nor recycled... What I am seeing is that once an object is moved to the recycle bin, the object's Parent Id changes in DTree, so the connect by prior does not find the object anymore. I am at a loss of how to do this without attempting to run a very costly query such as: select *

from DAuditNew DA where DA.Value1 LIKE '%Enterprise:Folder:SubFolder%'

Any other thoughts?

[To post a comment, use the normal reply function]
Topic: Determine if any items have been deleted or moved from a folder/sub-folders
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

[To post a comment, use the normal reply function]
Topic:	Determine if any items have been deleted or moved from a folder/sub-folders
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

DeletedFiles.txt

Walt_Ruppar

Just to circle back on this question, I worked with my team here and was able to come up with this query:

SELECT dataid, name FROM dtree WHERE dataid IN (SELECT id as DocumentID FROM GDCRECYCLEBIN g WHERE parentid in (SELECT DATAID FROM DTREE START WITH DATAID = %1 CONNECT BY PRIOR dataid = ABS(parentid)))

%1 = Parent Folder Object ID

It won't find moved items, but it will find deleted items provided the object type is configured to be sent to the Recycle Bin (Recycle Object).

John W. Simon, Jr.

Just FYI...

The "ABS(parentid)" will not perform very well as the ABS function will not allow an index to be used (at least with Oracle...that is unless you specifically create a function based index). You might want to take a look at replacing the connect by part of the query with a query against DTreeAncestors ("...where parentid in (select dataid from dtreeancestors where ancestorid = parentid)") OR create that function based index.

Cheers...