...
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') |
The following query will list all unscanned databases which have been discovered on hosts that have been scanned.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT fa.FoundApplicationID
, fa.Edition
, fa.Version
, prd.Name as ProductName
, fa.DeviceID
, fa.BuildNumber
, fa.Path
, faIP.IPAddress
, faHN.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
LEFT OUTER JOIN model.v_FoundApplicationHostname faHN ON faHN.FoundApplicationID = fa.FoundApplicationID
WHERE prd.Name in (N'SQL Server',N'Oracle Database Server')
AND fa.DeviceID IS NOT NULL
AND fa.ApplicationID IS NULL |