When iQSonar is inventorying a database application (DB2, Oracle, SQL Server, Informix, and so on) it not only looks for files on the server's file systems, it needs to log in to the database and execute certain commands. If it fails to log in to the database it cannot fully report all the required information for licencing reports.

Note that the following queries use a "Connection" column to determine what type of database we're looking for. In order to get a list of the possible valid connection types in the user environment, the following one line query is useful:

SELECT DISTINCT Connection FROM [history].[t_ConnectionHistory]

The following queries will enable you to investigate database connection issues:

Note that the "Type" column is the Application Type, which is not the same as the Connection type in most of the other queries on this page. You can get a list of valid types in your environment with the following query:

SELECT DISTINCT Type FROM [history].[v_DiagnosticsApplicationList]

SELECT [ProjectID]
      ,[TargetName]
      ,[IPAddressOrHostname]
      ,[FoundApplicationID]
      ,[Name]
      ,[Type]
      ,[Vendor]
      ,[ApplicationID]
      ,[Status]
      ,[DeviceID]
      ,[Reason]
      ,[scancount]
      ,[DeviceHostname]
      ,[TargetBinary]
  FROM [history].[v_DiagnosticsApplicationList]
  WHERE (Type = 'Oracle Database Server' or Type = 'SQL Server')
    AND ProjectID = '1'

SQL/Oracle credentials which succeeded or failed (per project)

In a large estate, we might need to know which database credentials are valid on which hosts, in order to contact the DB Owners and request new credentials. As with previous queries, the user does need to know the Project ID. The user also needs to know that Oracle Databases and clusters use the Connection type Connection = 'Oracle Database'  and Microsoft SQL Servers/Clusters have the connection type Connection = 'MS SQL server'.

SELECT c.Label
       , c.Username
       , o.Name as Outcome
       , c.Instance     -- Instance for MS SQL is less useful than for Oracle, but still interesting
       , ch.[IPAddress]
       , p.Name as [Project Name]
FROM [history].[t_ConnectionHistory] ch
    INNER JOIN config.t_Credential c ON ch.CredentialID = c.CredentialID
    INNER JOIN config.t_Outcome o ON ch.OutcomeID = o.OutcomeID 
    INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
    join config.t_Project p on jobProj.ProjectID =p.ProjectID
WHERE Connection = 'Oracle Database'
  AND p.ProjectID = '1'

This can be further refined to search for results on a specific date that the target was scanned for example. This example also looks for failed MS SQL connection attempts

SELECT c.Label
       , c.Username
       , o.Name as Outcome
       , ch.[IPAddress]
       , p.Name as [Project Name]
       , ch.AttemptDate
FROM [history].[t_ConnectionHistory] ch 
	INNER JOIN config.t_Credential c ON ch.CredentialID = c.CredentialID
    INNER JOIN config.t_Outcome o ON ch.OutcomeID = o.OutcomeID  
    INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
	join config.t_Project p on jobProj.ProjectID =p.ProjectID
WHERE ( (Connection = 'Oracle Database' ) OR (Connection = 'MS SQL server') )
  AND p.ProjectID = '1'
  AND convert(DATE,AttemptDate) = '2018-05-01'

The syntax for the date filter can be explained as follows. The "AttemptDate" column is a date-time field. By casting it to a date field we can look for a scan that started on the given date. We specify the date to look for in YYYY-MM-DD format as this is how the SQL database formats dates by default.

To specify a range of dates syntax like the following could be used:

 (CONVERT(DATE,AttemptDate) >= '2018-04-18' AND CONVERT(DATE,AttemptDate) <= '2018-04-20'))