Surgient Success

Hi

 has anyone created a report that shows all applications which then shows what servers they come from and then shows what files are attached (.vmdk's)

as we would like to audit the system and clean up unwanted images, this will also show up setup's that no longer have mount points (vmdk's)

Hope you can help 

Cheers

 Ian

Hi Ian -

We do not currently have such a report, but this information might be possible with a custom report, as the information currently does not exist within the reporting database.  However, a good place to start would be the Library Cleanup tool within the management console (under the Library tab).  This will give you a list of all files within your system, tell you how big they are, how many things reference them, how many snapshots are linked to the image, and the date the image was last used.  It will allow you to clean things up and lock any app configs and server configs that were using the image so that you can easily find them for deletion.

 Additionally, if you look at the Application Configurations tab, there is a column for the "# of servers".  The system shouldn't generally allow you to delete a server configuration that has a reference to an application configuration, but if you somehow managed, this column would be 0 for that app config.  Similarly, Surgient wouldn't allow you to delete a file that is referenced within a server configuration (unless you were using the library cleanup tool and then it would lock the associated objects), but if you deleted the file directly from the file system, outside of Surgient control, then it is definitely possible, if not harder to discover.

 Hopefully this at least helps you move in the right direction.

 Jackie.

Ian,

We have query for custom report that will show all library registered files including snapshots, first server config that uses this file and first app config that uses server config. To get this report working you will need to write your Surgient databases names here or change corresponding lines in query by yourself. Then you'll have to create custom report, name and describe it, insert query into it and select couple of filters. If you feel yourself comfortable with these actions and this is what you need for your audit I will provide you query.

Vadym.

Vadym.

 Thanks for the information your query does look like what we are looking for, so if you could pass it onto use that would be great

 Cheers

ian

Ian,

So you should create new report, enter name for it e.g. "Library Files", View type e.g "List", fill in description field e.g. "Organization filter do not work here. If it works then you don't see files that belong to null organization", select report type as "Organization Filter" and paste edited query, then press save. In the query you should replace all values in <> brackets and remove brackets. If you have any questions regarding this please ask them.

Query itself:

SELECT
 RIGHT(F.Handle, CHARINDEX('\', REVERSE(F.Handle))-1) as "File Name",
 LEFT(F.Handle, LEN(F.Handle)-CHARINDEX('\', REVERSE(F.Handle))) as "File Path", 
 O.Name as "Owner Org",
 F.FileSize as "Size in Bytes",
 F.FileDate as "Last Modified",
 SC.LastUpdateOn as "Last Deployed GMT",
 SC.Name as "ServConfig Name",
 AC.Name as "AppConfig Name"
FROM
 <vcs_db_name>.<auth_usually_dbo>.T_File F
 LEFT JOIN <vcs_db_name>.<auth_usually_dbo>.T_Organization O ON F.OrganizationId = O.Id
 LEFT JOIN <vcs_db_name>.<auth_usually_dbo>.T_MountPointConfiguration MPC ON F.Id = MPC.LeafFileId
 LEFT JOIN <vcs_db_name>.<auth_usually_dbo>.T_ServerConfiguration SC ON MPC.ServerConfigurationId = SC.Id
 LEFT JOIN <vcs_db_name>.<auth_usually_dbo>.T_AppConfigServerConfig ACSC ON SC.Id = ACSC.ServerConfigurationId
 LEFT JOIN <vcs_db_name>.<auth_usually_dbo>.T_AppConfig AC ON ACSC.AppConfigId = AC.Id
WHERE
 F.Handle like '%<distinctive_library_path_we_use_hosting.surgient_here>%' /* AND
 <vcs_db_name>.<auth_usually_dbo>.T_Organization.Id in (?) */
ORDER BY
 RIGHT(F.Handle, CHARINDEX('\', REVERSE(F.Handle))-1),
 F.OrganizationId,
 F.FileDate

Regards,
vadym

Hi Vadym

 Thanks for the info, I'll get round to testing sometime next week as i'm flying back to the UK this weekend. I've already noticed that some of the table names are different to what i have

eg T_Organization as its T_OrganizationEvent in our configuration but ill keep you posted

once again Thank You for your help so far

 Cheers

Ian