External Reporting Tools Returning Inconsistent File Paths

Overview

While accessing data from the Xinet database from external reporting tools, you notice some older now non-existent file paths showing. These paths are resulting in duplicate entries within your results, leading to inconsistent reporting results.

Solution

When file paths are removed from the OS file system, Xinet does not remove the original entries from the webnative.path table of the database. The original entry within the webnative.file table is maintained, and a new entry is generated for the file path. 

You can address these duplicate entries in two ways detailed below, depending on whether you want to retain the older entries for historical purposes or remove them from the database.

Cleaning the Database of the old non-existent file paths

If you no longer require these older entries, you can simply strip them from the database using the dbmgr utility.

1. Verify the PathID for the old, non-existent path by running the following query:
   1. mysql> select PathID from webnative.path where Path = '<old_path>';
2. Execute the following command to remove the non-existent path from the webnative.path table and the duplicate file entries from the webnative.file table:

   1. # /usr/etc/venture/bin/dbmgr -remove -pid <pathid_to_remove>

3. Once executed, any file entries with the old path will be automatically stripped from the database and your reporting queries will return more consistent results.

Adjusting your queries to filter on the new file paths

If you need to retain these older paths for historical documentation, as an alternative you can utilize a nested query to specify a specific file path for your reporting results to target, as shown below:

SELECT * from webnative.file where PathID in (SELECT PathID FROM webnative.path where CAST(Path AS Char) like '<path_to_your_desired_volume>')