Query to get number of files with extension

Hi All,

I have a requirement where I need to get number of files in content server with particular extension. for eg if i have 1250 pdf in content server it should show the result No of files- 1250 in first column and File extension- pdf .

Is using Filetype in dtree correct approach ?

Regards,

Ankur

Find more posts tagged with

Comments

John Simon

Filetype is stored in DVersData. I would join dtree with dversdata to make sure 1) I am not picking up orphaned documents and 2) to control whether I am getting all versions or only the current version.

Cheers...

Riten

The DVersData table holds the mime type for each version of the document. I would use that table to understand the number of objects per mime type. Not sure if you are looking for each version or document. You may need to change your query accordingly.

Regards,

Riten

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, April 06, 2018 4:40 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Query to get number of files with extension

Query to get number of files with extension

Posted bysinghan@unhcr.org (Singh, Ankur) On 04/06/2018 04:38 AM

Hi All,

I have a requirement where I need to get number of files in content server with particular extension. for eg if i have 1250 pdf in content server it should show the resultNo of files- 1250 in first column andFile extension- pdf .

Is using Filetype in dtree correct approach ?

Regards,

Ankur

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

Colin_Schmidt

DVERSDATA.FILETYPE will work most of the time, but sometimes it'll be UPPER or lower case. You might check if it has a PERIOD, as well. So, you have to be prepared for a variety.

Another way is to check for MIMETYPE.

Colin J

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Friday, April 6, 2018 4:40:29 AM
To: eLink Recipient
Subject: Query to get number of files with extension

Query to get number of files with extension

Posted by singhan@unhcr.org (Singh, Ankur) On 04/06/2018 04:38 AM

Hi All, I have a requirement where I need to get number of files in content server with particular extension. for eg if i have 1250 pdf in content server it should show the resultNo of files- 1250 in first column and File extension- pdf .Is using Filetype in dtree correct approach ?Regards,Ankur

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

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.

Nizar Ghazal

Here is something for MIMEType, but you could probably modify it, to suit your needs:

SELECT MimeType as "MIME", COUNT(*) as "Count", AVG(cast(DataSize as real)) / 1024 as "AvgFileSize (KB)", SUM(cast(DataSize as real) / 1024) as "TotalFileSize (KB)", MAX(cast(DataSize as real) / 1024) as "Max (KB)"

FROM DVersData

GROUP BY MimeType

ORDER BY 5 DESC