Fu R3 and Earlier
...
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
...
Gwynn R1 and Later
| Warning |
|---|
These queries work with iQSonar releases up to and including Fu R3. Changes were made to some underlying tables in Gwynn R1 and Gwynn R2 to reduce the amount of history saved as the diagnostic information can grow. In the Gwynn R3 release the option to preserve or delete this diagnostic can be selected in the user interface. Instructions on how to keep the history data can be found on this page. |
The following query will list all scanned databases (this example is limited to SQL and Oracle)
...
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.
The following query will give a list of unscanned Oracle Instances and the "reason" we were unable to scan them.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
/* Found Instances */
WITH scanned AS (
SELECT DISTINCT d.Hostname, aq.Qualifier as Instance
FROM
model.v_ApplicationDatabase ad
INNER JOIN model.v_ApplicationQualifier aq ON ad.ApplicationID = aq.ApplicationID
INNER JOIN model.t__Application_Device appdev ON ad.ApplicationID = appdev.ApplicationID
INNER JOIN model.t_Device d ON appdev.DeviceID = d.DeviceID
INNER JOIN model.t_Application a ON ad.ApplicationID = a.ApplicationID
INNER JOIN model.t_Product p on a.ProductID = p.ProductID
WHERE p.name='Oracle Database Server'
),found AS (
SELECT DISTINCT d.Hostname, ch.Instance
FROM model.t_FoundApplication fa
INNER JOIN model.v_FoundApplicationIPAddress faIP ON faIP.FoundApplicationID = fa.FoundApplicationID
Inner JOIN model.t_Device d ON d.DeviceID = fa.DeviceID
JOIN [model].[t_FoundApplicationPort] fap ON fap.FoundApplicationID = fa.FoundApplicationID
INNER JOIN history.t_ConnectionHistory ch ON fa.FoundApplicationID = ch.FoundApplicationID
INNER JOIN model.t_Product p on fa.ProductID = p.ProductID
WHERE p.name='Oracle Database Server'
),
OfInterest AS
(
SELECT * FROM found
WHERE Instance IS NOT NULL
AND Hostname IS NOT NULL
except
SELECT * FROM scanned
WHERE Instance IS NOT NULL
AND Hostname IS NOT NULL
)
/* FoundApplication does not store INSTANCE ; INSTANCE is in history.t_ConnectionHistory */
SELECT DISTINCT fa.FoundApplicationID as aID
, d.Hostname
, ch.-Instance
, fap.Port
, faIP.IPAddress
, fa.Reason as UnscannedReason
FROM model.t_FoundApplication fa
INNER JOIN model.v_FoundApplicationIPAddress faIP ON faIP.FoundApplicationID = fa.FoundApplicationID
Inner JOIN model.t_Device d ON d.DeviceID = fa.DeviceID
JOIN [model].[t_FoundApplicationPort] fap ON fap.FoundApplicationID = fa.FoundApplicationID
INNER JOIN history.t_ConnectionHistory ch ON fa.FoundApplicationID = ch.FoundApplicationID
INNER JOIN OfInterest oi on oi.Hostname = d.Hostname AND oi.Instance = ch.Instance
INNER JOIN model.t_Product p on fa.ProductID = p.ProductID
WHERE p.name='Oracle Database Server'
AND d.Hostname IS NOT NULL
AND ch.Instance IS NOT NULL
ORDER BY Hostname, Instance, Port, UnscannedReason |
Here is a second query for use in searching out database instances we know exist which may not be scanned. If we find a running ora_pmon process on Linux or Windows, we know for sure there is an oracle database instance (up to version 11G) or container (version 12c or greater). This query shows discovered processes and whether we simply have a FoundApplication or a fully scanned application.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT DISTINCT
d.hostname
, dp.DeviceID
, dp.ProcessName
, SUBSTRING(dp.ProcessName ,10,255) as InstanceName
, c.q as Qualifier
, c.applicationID
, c.FoundApplicationID
FROM model.v_DeviceProcess dp
join model.t_device d on dp.deviceID =d.deviceID
join model.v_DeviceIPAddress dip on dip.DeviceID =dp.DeviceID
left join(
select Qualifier as [q],ApplicationID as [applicationID], [FoundApplicationID]
from model.v_ApplicationQualifier) c
on c.[q] = SUBSTRING(dp.ProcessName ,10,255)
WHERE dp.ProcessName like '%ora_pmon_%'
|