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

ZERO Hit Count Live Report

Ravi_Bheemarao

Hello,

I am looking for a zero hit Count LiveReport at any Livelink Enterprise folder location. The report should identify documents within that folder scope that have not been accessed, including but not limited to:

view

version added

version opened

rename

reserve

unreserve

attributes changed

category added

open

basically any function except a permissions change.

The Zero Hit Count LiveReport should have 3 fields:

Enter Starting Folder (Browse for a Container...)

Start Date

End Date

I have been able to get the results from the queries,its not fool proof though.

Wanted to see if anyone has done this before which I an take advantage off.

Appreciate your help

Thanks

Find more posts tagged with

Comments

Lindsay_Davies

Hi Ravi,

You did not give any clues as to what queries you are using so far, what might be wrong or whether you are using Oracle, SQL server or something else.

My guess is that you need to find out what has not been accessed with a view to archiving to a different location or possibly deleting the items.

If I were to start from scratch I would need to do two things.

Define the total set of dataids within the container (‘initial list’).

Define the items that have been accessed using the DAuditNew table but based on the same list.

Take the ‘accessed list’ away from the initial list.

Report the items that are left in the way you want (a list of DataIDs, or names, or a full “LiveReport:” output format list with function menu support.

Select dataid from dtreeancestors where ancestorid = <start dataid>>

MINUS

select dataid from dauditnew where dataid in (select dataid from dtreeancestors where ancestorid= <start dataid>) and auditdate between <startdate> and <enddate> and auditID in (<list of appropriate values>)

MINUS is an Oracle term; in SQLServer you would use EXCEPT.

You would then use that reduced list as input in another (outer) select to determine what you want to output to the screen.

Under normal conditions, End Date is likely to be the current date, so it is irrelevant.

You might want to determine the full range of AuditID and AuditStr values in your table.

select auditid, auditstr, count(*) from dauditnew group by auditid, auditstr order by 2

You will also need to be sure the appropriate actions are being audited before you rely on your results.

Visit the page ‘Set Auditing Interests’ within Admin.Index to see what is set.

Good luck.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 28 June 2016 22:30
To: eLink Recipient
Subject: ZERO Hit Count Live Report

ZERO Hit Count Live Report

Posted by Bheemarao, Ravi On 06/28/2016 05:25 PM

Hello,

view

version added

version opened

rename

reserve

unreserve

attributes changed

category added

open

basically any function except a permissions change.

The Zero Hit Count LiveReport should have 3 fields:

Enter Starting Folder (Browse for a Container...)

Start Date

End Date

I have been able to get the results from the queries,its not fool proof though.

Wanted to see if anyone has done this before which I an take advantage off.

Appreciate your help

Thanks

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

CONFIDENTIALITY NOTICE: This e-mail, including any attachments, contains information that may be confidential, and is protected by copyright. It is directed to the intended recipient(s) only. If you have received this e-mail in error please e-mail the sender by replying to this message, and then delete the e-mail. Unauthorised disclosure, publication, copying or use of this e-mail is prohibited. Any communication of a personal nature in this e-mail is not made by or on behalf of any RES group company. E-mails sent or received may be monitored to ensure compliance with the law, regulation and/or our policies.

Colin_Schmidt

That’s pretty easy, but relative to the size of your DAUDITNEW table. There’s probably a few ways to that report. Here’s one for Oracle:

The DTREEANCESTORS table keeps the query to documents under the one high parent folder. If you want the path, you’d use an Oracle function. It could further be sped along by checking in the DTREECORE for “dt.modifydate”, but that wouldn’t tell you if it had only been “FETCHed”.

select dt.dataid, dt.name

From dtreeancestors dtan

Join dtreecore dt on dtan.dataid = dt.dataid and dt.subtype = 144

Where dtan.ancestorid = %1

and not EXISTS (select da.dataid from dauditnew da where dt.dataid = da.dataid and (dt.auditdate >= %2 and da.auditdate <= %3) and da.auditid in (5, 6, 7, 11, 12, 14, 25, 26, 35))

Attached are some codes that may help you as you go on to write your own.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, June 28, 2016 5:30 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: ZERO Hit Count Live Report

ZERO Hit Count Live Report

Posted by Bheemarao, Ravi On 06/28/2016 05:25 PM

Hello,

view

version added

version opened

rename

reserve

unreserve

attributes changed

category added

open

basically any function except a permissions change.

The Zero Hit Count LiveReport should have 3 fields:

Enter Starting Folder (Browse for a Container...)

Start Date

End Date

I have been able to get the results from the queries,its not fool proof though.

Wanted to see if anyone has done this before which I an take advantage off.

Appreciate your help

Thanks

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

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.

LL-Table codes.xlsx

Ravi_Bheemarao

Hi Both,

Thanks for your reply..

I was using following query - ofcourse I need to modify for Start and End date accordingly.

I have done live reports in my previous job but not any complicated ones.

Hope below make sense

select b.*, a.op, a.dataid from(select max(auditdate) as op, dataid from dauditnew where dataid in (select dataid from calligo.dtree where subtype > 0

start with dataid in (select dataid from calligo.dtree where dataid = '28709664')

CONNECT BY PRIOR dataid = parentid)group by dataid having max(auditdate) <= to_date('30-Jun-16')) a left outer join(select * from dtree) b on a.dataid=b.dataid

We are still using LL 9.7.1 at our company and don't seem to have DTREECORE table in DB.

Did you mean, DTREE table as DTREECORE ?

Thanks

Colin_Schmidt

If you don’t have DTREECORE, then DTREE should be used. If you do have it, either will do. DTREE is a “view”, if you have DTREECORE in your CS installation.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 29, 2016 11:38 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE ZERO Hit Count Live Report 2

RE ZERO Hit Count Live Report 2

Posted by Bheemarao, Ravi On 06/29/2016 11:35 AM

Hi Both,

Thanks for your reply..

I was using following query - ofcourse I need to modify for Start and End date accordingly.

I have done live reports in my previous job but not any complicated ones.

Hope below make sense

select b.*,a.op, a.dataid from(select max(auditdate) as op, dataidfrom dauditnew where dataid in (select dataidfrom calligo.dtreewhere subtype > 0

startwith dataid in (select dataid from calligo.dtreewhere dataid = '28709664')

CONNECTBY PRIOR dataid = parentid)group by dataid having max(auditdate) <=to_date('30-Jun-16'))a left outer join(select * from dtree) bon a.dataid=b.dataid

We are still using LL 9.7.1 at our company and don't seem to have DTREECORE table in DB.

Did you mean, DTREE table as DTREECORE ?

Thanks

[To post a comment, use the normal reply function]
Topic:	ZERO Hit Count Live Report
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Thanks for your quick reply.

Do we have to use DTREEANCESTORS table ?

Can't we accomodate this request by working thru DTREE and DAUDITNEW table ?

Please let me know

Thanks

Colin_Schmidt

I may be missing something, but with the DTREEANCESTORS table, I can be sure I’m getting all sub-items under that one Folder, no matter how many sub-folders there may be. I’m more than willing to be corrected.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 29, 2016 1:04 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE ZERO Hit Count Live Report 2

RE RE ZERO Hit Count Live Report 2

Posted by Bheemarao, Ravi On 06/29/2016 12:20 PM

Thanks for your quick reply.

Do we have to use DTREEANCESTORS table ?

Can't we accomodate this request by working thru DTREE and DAUDITNEW table ?

Please let me know

Thanks

[To post a comment, use the normal reply function]
Topic:	ZERO Hit Count Live Report
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

John Simon

If using Oracle you can use the old "start with connect by" but if you have a large structure this will be slower than DTreeAncestors. If SQL Server there are plenty of posts about how to traverse the tree (from before DTreeAncestors existed).

Cheers...

Colin_Schmidt

I use the “Start with Connect by” combined with “Sys_connect_by_path” when I want the CS path to show, but I use the DTREEANCESTORS to ensure I’ve got everything under a parent Folder on down. But use whatever means works for you. Some sites create DB Functions to produce the path.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 30, 2016 8:59 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE ZERO Hit Count Live Report 2

RE RE ZERO Hit Count Live Report 2

Posted by Simon, John On 06/30/2016 08:49 AM

Cheers...

[To post a comment, use the normal reply function]
Topic:	ZERO Hit Count Live Report
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center