Fu R3 and Earlier
The following query will list all discovered databases, and show whether or not they are fully scanned
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT DISTINCT p.Name as ProjectName
, dal.[DeviceHostname]
, dal.[IPAddressOrHostname]
, dal.[DeviceID]
, dal.[FoundApplicationID]
, dal.[ApplicationID]
, dal.[Name] as ProductName
, dal.[Status]
, dal.[Reason]
, dal.[Vendor]
, ad.[Name] AS DBName
, ad.Owner AS DBOwner
FROM [history].[v_DiagnosticsApplicationList] dal
INNER JOIN [config].[t_Project] p ON p.ProjectID = dal.ProjectID
LEFT OUTER JOIN [model].[v_ApplicationDatabase] ad ON dal.ApplicationID = ad.ApplicationID
WHERE (Type = 'Oracle Database Server' or Type = 'SQL Server')
AND (ad.[DatabaseIdent] NOT IN ('master','tempdb','model','msdb'))
|
Gwynn R1 and Later
Due to changes in the way scan history tables store their results, the above query no longer works in Gwynn R1. This query returns less information, but should work on all recent versions of iQSonar.
The following query will list all scanned databases (this example is limited to SQL and Oracle)
| Code Block |
|---|
SELECT app.ApplicationID
, app.Edition
, app.Version
, dev.Hostname
, apq.Qualifier
, prd.Name as ProductName
, appdb.Name as AppDBName
, appdb.DatabaseIdent
FROM [model].[t_Application] app
INNER JOIN model.v_ApplicationQualifier apq ON app.ApplicationID = apq.ApplicationID
INNER JOIN model.t_Product prd on prd.ProductID = app.ProductID
INNER JOIN model.t__Application_Device apd ON app.ApplicationID = apd.ApplicationID
INNER JOIN model.t_Device dev ON apd.DeviceID = dev.DeviceID
LEFT OUTER JOIN model.v_ApplicationDatabase appdb ON appdb.ApplicationID = app.ApplicationID
WHERE prd.Name in (N'SQL Server',N'Oracle Database Server') |