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

Applicable RDBMS

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