Page Comparison

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:

...

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

...

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 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.

Code Block
language sql theme Midnight title Discovered but unscanned databases
SELECT fa.FoundApplicationID , fa.Edition , fa.Version , prd.Name as ProductName , fa.DeviceID , fa.BuildNumber , fa.Path , faIP.IPAddress , faHNd.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 LEFTInner OUTER JOIN model.vt_FoundApplicationHostnameDevice faHNd ON faHNd.FoundApplicationIDDeviceID = fa.FoundApplicationIDDeviceID 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.

The following query will give a list of unscanned Oracle Instances and the "reason" we were unable to scan them.

Code Block
language sql theme Midnight title Unscanned Oracle Instances
/* 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
language sql theme Midnight title Discovered Oracle PMON processes
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_%'