Fu R3 and Earlier
The following query will list all discovered databases, and show whether or not they are fully scanned
Note, the "Type" column refers to what iQuate calls an "Application". To find a list of valid database types in your environment you can use the following query:
SELECT DISTINCT Type FROM [history].[v_DiagnosticsApplicationList]
Not all the results from the above query will be databases, but any discovered database applications will be listed.
The list of products which can be scanned is found in the table model.t_Product.
Query to list discovered Oracle and SQL Server databases
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)
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')
The following query will list all unscanned databases which have been discovered on hosts that have been scanned.
Please do be aware that this list will produce a large number of "discovered" found applications (databases) for each database that actually exists because the scan engine uses multiple methods to discover databases. We can also not return the name of the database until after we have scanned it. What this query does give is a useful indication of what hosts have unscanned databases on them. It also shows the application path in some cases. This will allow the iQSonar user to work with the DBA team to rescan these hosts and hopefully discover more databases once the correct credentials and/or ports have been determined.
SELECT fa.FoundApplicationID
, fa.Edition
, fa.Version
, prd.Name as ProductName
, fa.DeviceID
, fa.BuildNumber
, fa.Path
, faIP.IPAddress
, d.Hostname
FROM model.t_FoundApplication fa
INNER JOIN model.t_Product prd on prd.ProductID = fa.ProductID
INNER JOIN model.v_FoundApplicationIPAddress faIP ON faIP.FoundApplicationID = fa.FoundApplicationID
Inner JOIN model.t_Device d ON d.DeviceID = fa.DeviceID
WHERE prd.Name in (N'SQL Server',N'Oracle Database Server')
AND fa.DeviceID IS NOT NULL
AND fa.ApplicationID IS NULL
The user is of course free to add additional products to these lists. The list of products which can be scanned is found in the table model.t_Product.