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
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
/* Found Instances */ WITH scanned AS ( SELECT DISTINCT fa.FoundApplicationID , d.Hostname , faIP.IPAddress , ch.Instance , fap.Port , fa.Reason as UnscannedReason FROM model.t_FoundApplication fa INNER JOIN model.t_Product prd on prd.ProductID = fa.ProductID 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 prdp.Name name=N'Oracle Database Server' AND fad.DeviceIDHostname IS NOT NULL AND fach.ApplicationIDInstance IS NOT NULL ORDER BY FoundApplicationIDHostname, 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_%'
|