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; } } }

Finding Objects over 90 days

Bryan_Gerlach

Hello all,

I'm trying to figure out a LiveReport that will help us maintain one area within our instance. What I am trying to accomplish is to have the report list the parent folder if no objects within that folder have been accessed within the past 90 days. So for a brief visual example:

Folder 1

---- Folder 2

---- Folder 3

---- Folder 4 (Old project, can be removed)

I would want a report that returns Folder 4 if anything under Folder 4 hasn't been touched in the past 90 days. I have only been able to get the immediate parent of the object or Folder 1 in my example in all my tries so far. We are using 9.7.1 and Oracle. Any suggestions to get me pointed in the right direction would be appreciated. We also have WebReports if it makes it easier, but I haven't had any experience with them so far.

Thanks,

Bryan

Find more posts tagged with

Comments

Appu_Nair

I belive you are strugglink on the hirarachical walk.A simple eaxmple

Ent Dataid 2000

tons of subfolders and documents

then you can do

select level,name,dataid,parentid from dtree where subtype=0 /*show folders only*/

connect by prior dataid=parentid start with 2000

ot dtreeancestorstable if that is all working correctly in your org

dtreeancestors is a built table by livelink.In the other one you are relying on Oracle's intelligence

plenty of examples on date based things in this forum as well

Bryan_Gerlach

Pretty much, here's what I have so far

select level, substr(Lpad(' ',(level-1) * 2)||a.name ,1,40) "Object", dataid, modifydate, (select mailaddress from kuaf where id = a.createdby) "Creators Email", parentid
from dtree a
where a.subtype in (0,144) and a.modifydate < (select sysdate from dual) -90 connect by prior a.dataid = a.parentid start with a.dataid = 12345
ORDER BY a.createdby, level

That gets me everything over 90 days, but I'm struggling on the part of how to roll that list back into just the parent subfolder. Is there a simple way to work my way back up the folder tree?

I noticed a few oddities with DTreeAncestors in my first few tries at using that table so I went back to the connect by prior method.

Bryan

Appu_Nair

IDEAS Galore:)

Did not test

You would need Lindsay or John to get you over that part,but my inteligence would do it this way.If I have a hit folder then its parentid is immediately available from dtree so I would make your first query a view lets call it vAge90

so I would select dataid from vAge90

assume you have a list of id's like 12345,56789,87765

now one could do this

select parentid,dataid from dtree dt,vAge90 vt where dt.dataid=vt.dataid

I am sure you can do this without a view but I am jsut a big fan of views.

or even in the query just ask for parentid itself that would be the immediate folder don't you think...

Now the date things in livelink are pretty subject to interpretation so if a child object is modified it reflects on parents's modified date so instead of deleting the offending folder I would devise a quarantine mechanism,strip that folder of user permissions and make somebody llike 'admin' the complete acl holder.In case nobody complains in the next 90 day you delete it

Bryan_Gerlach

Hmm looks promising. I'll have to give it a few good tries to see where it gets me. I'll post back either way next week.

Thanks,

Bryan

Bryan_Gerlach

Quick thought, the immediate parentID I can get, but if the file in question is under like 5 subfolders, I would still need to travel back up the folders until I reached one of the immediate subfolders of the main folder.

I like visual examles, so here is a quick one building off my previous one.

Folder 1

--Folder 4

----Folder5

------Folder 6

--------File 1 that's older than 90 days

I can get Folder 6, but I can't figure out how to get back to Folder 4. I realize I'm probably going to have to go the other way with first creating a list of the main folders that have been accessed in the previous 90 days, then finding folders not in that list that I can mark for clean up. Just a quick change to my date sign once I can figure out how to work back up the list.

Bryan

Jim_Nylander

Bryan, you are looking for files that have been accessed.

By this, do you mean viewed as well as modified? If so, then once you have identified the possible candidate folders, you may need to scan the audit log as well. I am not aware of any field in the dtree table that shows when a record was viewed or downloaded.

Once you have your candidate folders, you could find any dataids in the audit log in the past 90 days that have a parentid in your candidate list. This would show any possible activity on them.

This is a possible example:

select da.eventid, da.auditdate, da.auditid, da.auditstr, da.dataid, dt.parentid from dauditnew da, dtree dt

where da.auditdate > sysdate-90 and

da.dataid = dt.dataid(+) and

dt.parentid in(123456,234567) /* <- - - the dataids of the candidate folders */

order by dt.parentid, dt.dataid, da.auditdate

Jim

Bryan_Gerlach

Jim,

Good point. I'd have probably forgotten about that whenever I found the solution and just deleted my candidate folders out of pure joy of finally cracking this problem

Made myself a note to double check against the DAuditNew table as well

Bryan

Appu_Nair

and hopefully you are using a supported method of deletion such as api,oscript or webreports otherwise that joy could turn into a nightmare)

Bryan_Gerlach

So I'm getting closer. Not being a huge oracle person, I did a little more research and found the connect_by_path option. I can now get a list of of all files modified within the previous 90 days and the DataID path back up to my main folder from the initial query. Now I just have to modify it a tad to check the DAuditNew table rather than just ModifyDate.

From there, I should be able to parse the string for the Second DataID to get a list of active project folders, then from there it's a quick compare to show all inactive folders. I figure the active list will be much smaller than the inactive list for the first time so I went that direction for now.

I'll post back if and when I get this all completed.

Thanks,

Bryan

Bryan_Gerlach

So close... Well I can get a list of active main project folders with this query

SELECT a.ActiveFolderID
FROM (
SELECT dt.DataID, dt.Name, dt.PathName, da.AuditStr, substr(dt.PathName, instr(dt.PathName, '-', 1,2) + 1, instr(dt.PathName, '-',1,3) - instr(dt.PathName, '-',1,2)-1) ActiveFolderID
FROM
(SELECT DataID, Name, sys_connect_by_path(DataID, '-') PathName
FROM DTree
START WITH DataID = 123456 CONNECT BY PRIOR DataID = ParentID) dt, DAuditNew da
WHERE dt.DataID=da.DataID AND da.AuditStr ='Fetch'
GROUP BY dt.DataID, dt.Name, dt.PathName, da.AuditStr
HAVING Max(da.AuditDate) > SysDate - 90) a

Seems like it would have been easy to just select the folders with parentID to my main folder and not in that resulting set, but wasn't coming back with anything. No error doing it, just kept trying to process without any results.

Bryan

Bryan_Gerlach

I'm an idiot, I can just reverse my > to < and then that should be all the inactive folders... so it looks like I can finally claim success!

Well after a bit more testing tomorrow that is.

Bryan