SQL Queries - CAST Central Base - Queries on snapshots - How to get the list of different files between 2 snapshots

Purpose of Query

 The purpose of this query is to provide the list of files which are in one snapshot but not in another. This query requires that two snapshots have been completed.

Applicable CAST Version


Release
Yes/No
8.3.x (tick)
8.2.x (tick)
8.1.x (tick)
8.0.x (tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server (question)
Microsoft SQL Server (question)
CSS3(tick)
CSS2 (tick)


Query for CSS

The following query should be run on the central database. The query will list files that are in <new_snapshot_id> that are not in <old_snapshot_id>. The values for <new_snapshot_id> and <old_snapshot_id> must be manually provided in the query below. See the following page for assistance with getting the snapshot_id: SQL Queries - CAST Central Base - Queries on snapshots - How to get the ID and name of a snapshot for a given application

SELECT   b.object_full_name            ,
         c.metric_id              ,
         a.object_type_id         ,
         a.object_type_description,
         COUNT(DISTINCT b.object_id)
FROM     dss_object_types a,
         dss_objects b     ,
         dss_metric_results c
WHERE    a.object_type_id=b.object_type_id
AND      b.object_id     =c.object_id
AND      c.metric_id     =10154
AND      c.snapshot_id   =<new_snapshot_id>
AND      a.object_group  =0
GROUP BY 1,
         2,
         3,
         4

EXCEPT
       
       ALL
SELECT   b.object_full_name            ,
         c.metric_id              ,
         a.object_type_id         ,
         a.object_type_description,
         COUNT(DISTINCT b.object_id)
FROM     dss_object_types a,
         dss_objects b     ,
         dss_metric_results c
WHERE    a.object_type_id=b.object_type_id
AND      b.object_id     =c.object_id
AND      c.metric_id     =10154
AND      c.snapshot_id   =<old_snapshot_id>
AND      a.object_group  =0
GROUP BY 1,
         2,
         3,
         4
Query result example

"[C:\CASTMS\prod80\Deploy\ASP_tests\My Package\Intranet\Default.asp]";10154;274;"eFile";1
"[C:\CASTMS\prod80\Deploy\ASP_tests\My Package\Intranet\menu.asp]";10154;274;"eFile";1
"[C:\CASTMS\prod80\Deploy\ASP_tests\My Package\Intranet\ACom.asp]";10154;274;"eFile";1
"[C:\CASTMS\prod80\Deploy\ASP_tests\My Package\Intranet\SCom.asp]";10154;274;"eFile";1
"[C:\CASTMS\prod80\Deploy\ASP_tests\My Package\Intranet\index.asp]";10154;274;"eFile";1

Query result interpretation
 The query will return files that are in <new_snapshot_id> but not in <old_snapshot_id>. The information returned for each file is the full name of the file, the type name and type id of the file, and the number of files with that name.


Query for Oracle
Enter the SQL query
Query result example
 
Query result interpretation
 


Query for SQL server
Enter the SQL query
Query result example
 
Query result interpretation
 
Notes/comments
 

 

 

Related Pages