Report to find unmodified folders within 90 days

[bng66]

Hello,

Summary

Folder A
- Folder B
- Folder 1
- Folder a
- Folder C
- Folder 2
- Folder D
- Folder E

Looking for a report that looks through all subfolders of A for Modified dates greater than 90 days old but returns Folder B-E and not 1,2, or a if it meets the criteria so I can delete the content.

Long winded post below.

We are using 9.7.1 and I'm trying to create a report that will return a list of immediate subfolders that have not had any changes within the previous 90 days for deletion. I at first thought I had it by looking at just the modified date of the subfolders, but realized that if they contain additional folders and changes are made there, the main subfolder date does not get altered. I could go the other way if easier and list of folders that have activity in the previous 90 so don't delete them.

My first shot at the report was the following

Select DTree.DataID, DTree.ModifyDate, Dtree.Name,
FROM DTree INNER JOIN DTreeAncestors ON DTree.DataID = DTreeAncestors.DataID
WHERE DTreeAncestors.AncestorID = 1234 AND ModifyDate < SysDate - 90 AND DTree.Subtype = 0
ORDER BY DTree.ModifyDate

but that will give me a list of any folder and then I have to dig around trying to find the top level folder. I also ran into a strange problem where one of my main subfolders doesn't show up in the DTreeAncestors table at all. Rather odd.

Thanks for any help.

B

 

[appnair]

rather than trying to base something on modifydate ,I say that because modifydate is triggered by many things in livelink.
Here's a tweak that is practiced in the OT forum to figure out if a folder is being accessed by anybody or not.One would create a whitespace image small pixel and put it on folders inside a customview.when anybody is coming to the folder the customview is called and audited.You can base some better decision later on audit of that customview,maybe make some rule saying that if the customview in that folder was dormant for 90 days maybe you could turn off the perms and only admins have access to it and delete it in the next sixty days if nobody complains.Also empty folders in livelink really is not a huge load.

Also dtreeanacestors has to be properly populated by the right patches and stuff as older versions have had problems with dtreeancestors

That is more work than trying to find it by "modifydate" but my opinion that is all

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[bng66]

Thanks for the reply,

I'll have to look into that suggestion. The main reason for the report was we have an area set up for people to create group areas for small projects or clubs that span different departments that we would like to periodically clean up. Not a pressing issue but on the to do list.

Would this method track an instance of someone going straight to a document within a folder from a hyperlink?

Now that I have my OpenText account created, I will also look there for some ideas.

Thanks again,

B

 

[appnair]

no a click to document is not like one going to the folder.In that case dauditnew has that info so you know it is a document that some one needs.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[bng66]

Alright, thanks much. The help is appreciated.

B