Georgy N Joseph

How To Check The DMS Filestore

Blog Post created by Georgy N Joseph on Dec 22, 2016

Hi Friends,

 

Have you ever received any of the two error messages shown in the banner image while trying to access a filestore document from CA PPM application?

  • Error 500 - Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems. Contact your site administrator.
  • ERROR DMS-02104: The system cannot retrieve the requested document from the DMS. Contact your system administrator.

 

Check The CA PPM Log Files

 

I could not find anything helpful on these errors recorded in the CA PPM log files with the default logger settings in CSA. But if we add a new category com.niku.dms to the STDOUT appender with priority at Debug level, we will be provided with detailed information on the root cause for this DMS error.

Logger Config in CSA

 

My issue was that the file was completely missing from the server filestore location as I could see from the app-ca.log file after doing the above log configuration changes in CSA and a retry to access the file from the application. This is what I could see in the app-ca log file:-

 

DEBUG 2016-12-22 07:45:26,300 [http-bio-***.XX.***.***-***-exec-XXXX] niku.dms (clarity:XXXXXXX:42979***__7BEAFXXX-DCXX-4DXX-BEXX-CEF838XXXXX:dms.viewFile) File "8753293" not found in path "E:\KnowledgeStore\filestore\clarity\Files\753\008"

 

How To Check The Entire DMS Filestore On The Server 

 

But what if this was not the only file that's missing? What if there are so many more which are somehow missing from our server filestore? Obviously we won't be able to manually check each and every file in the Knowledge Store and other file locations inside CA PPM. This forced me to research a bit online for any available scripts which can be readily used with minimum edits. Although I found none like that, I stumbled across many code snippets which can be tweaked to help us in this mission. Thought I will share the same new GEL script with the community here.

 

NOTE: I ran this gel script from the command prompt on the CA PPM server where the filestore folders were present.

 

Gel Script To Check For Missing Files in The DMS Filestore

 

You may need to modify a couple of values in the below Gel script (given in red) as per your environment. Also note that this will check for only the latest versions of the DMS files, as specified in the SQL code within this script. 

 

<gel:script
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:sql="jelly:sql"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 

<gel:setDataSource dbId="niku"/>

 

<!--Write the results to a CSV file in D drive of the CA PPM Server-->
<file:writeFile delimiter="," fileName="D:\\Missing_DMS_Files.csv" >

 

<!--Specifying the number of columns in the CSV and their headers-->
<file:line>
<file:column value="File ID"/>
<file:column value="Server Path"/>
<file:column value="PPM File Name"/>
<file:column value="PPM File Type"/>
<file:column value="Created Date"/>
<file:column value="PPM Path"/>
<file:column value="File Status"/>
</file:line>

 

<!--Oracle SQL code to get DMS file metadata from PPM Database (latest file versions only)-->
<sql:query escapeText="false" var="DMS_Query">
<![CDATA[
SELECT
files.id File_ID,
'E:\KnowledgeStore\filestore\clarity\Files\'||substr(ver.id,2,3)||'\00'||substr(ver.id,1,1)||'\'||ver.id Server_Path,
files.name PPM_File_Name,
files.mime_type PPM_File_Type,
TO_CHAR(files.created_date, 'DD-MON-YY') Created_Date,
fold.path_name||'/'||fold.name PPM_Path
from
CLB_DMS_VERSIONS ver
inner join CLB_DMS_FILES files on ver.file_id=files.id
inner join CLB_DMS_FOLDERS fold on files.parent_folder_id=fold.id
where
ver.is_latest=1
order by Server_Path
]]>
</sql:query>

 

<core:forEach items="${DMS_Query.rowsByIndex}" trim="true" var="row">

<!--Storing the complete Server_Path of the file into a new variable-->
<core:set var="fileName" value="${row[1]}"/>

<core:new className="java.io.File" escapeText="false" var="fileTest" >
<core:arg type="java.lang.String" value="${fileName}" />
</core:new>

<!--Checks whether the file is empty or not available. Skips the record if the file is OK-->
<core:choose>
<core:when test="${fileTest.exists()}">
<core:if test="${fileTest.length() == 0}">
<core:set var="FileStatus" value="Empty File"/>
<!--Writes the record to the CSV file when the file is found but is empty-->
<file:line>
<file:column value="${row[0]}"/>
<file:column value="${row[1]}"/>
<file:column value="${row[2]}"/>
<file:column value="${row[3]}"/>
<file:column value="${row[4]}"/>
<file:column value="${row[5]}"/>
<file:column value="${FileStatus}"/>
</file:line>
</core:if>
</core:when>


<core:otherwise>
<core:set var="FileStatus" value="File Not Found"/>
<!--Writes the record to the CSV file when the file is not found in the Filestore-->
<file:line>
<file:column value="${row[0]}"/>
<file:column value="${row[1]}"/>
<file:column value="${row[2]}"/>
<file:column value="${row[3]}"/>
<file:column value="${row[4]}"/>
<file:column value="${row[5]}"/>
<file:column value="${FileStatus}"/>
</file:line>
</core:otherwise>


</core:choose>

</core:forEach>
</file:writeFile>

</gel:script>

 

Special thanks to gcubed as your inputs all across this forum helped me a lot 

Hope someone finds this script useful.

 

Regards,

Georgy

Attachments

Outcomes