Looking for SQL to report the count (number of document Fetches or Views) since the documenet was last modified using DTreeCore.ModifyDate
Easy.
You use the DAUDITNEW & DTREE tables…using Oracle. SQL Server would convert or format the date comparison with different functions. There’s also a check for “Enterprise WS”, but it would also exclude anything in Projects….so you could leave that out or put in more checks to exclude Personal WS.
select da.auditid, da.auditstr, count(*)
from dtree dt
join dauditnew da on dt.dataid = da.dataid and da.auditid in (14, 26) and to_char(da.auditdate, 'yyyymmdd') >= to_char(dt.modifydate, 'yyyymmdd')
where dt.subtype = 144 and dt.ownerid = -2000
group by da.auditid, da.auditstr
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Monday, May 7, 2018 2:44 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count of Document Fetches of Views Since It Was Last Modified
Count of Document Fetches of Views Since It Was Last Modified
Posted bychad.latka@hii-ingalls.com (Latka, Chad) On 05/07/2018 03:36 PM
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
My Support
Colin,
Thank you for response. I noticed your SQL does not use the DTreeCore table, but instead uses DTree which is a View in CS10.5.
What are the pro's or con's to using DTree versus DTreeCore ?
Thanks,
Chad
When I originally encountered DTREECORE….I was told in the Forum to never report out of it. DTREECORE has only 1 or 2 extra fields, but I was just told not to. Everything you need to report on is in the DTREE, which is a view of DTREECORE. There was a risk, but I don’t recall it. One of the Extra fields is whether the Row or Object is Deleted or not, but so far, I haven’t had any issues reporting out of.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, May 8, 2018 9:22 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count of Document Fetches of Views Since It Was Last Modified
Posted bychad.latka@hii-ingalls.com (Latka, Chad) On 05/08/2018 10:04 AM
Topic:
The difference being that the DTree View has filters that suppress deleted records from being returned in query results. If report authors query DTreeCore table instead of the view, the results may include deleted object records.
- Pramod Mohandas
[EXTERNAL] - RE Count of Document Fetches of Views Since It Was Last Modified
Posted bycolin.schmidt@cognizant.com (Schmidt, Colin) On 05/08/2018 11:06 AM
Posted by chad.latka@hii-ingalls.com (Latka, Chad) On 05/08/2018 10:04 AM
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
