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.

...

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:

...

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 type Connection = 'Oracle Database'  and Microsoft SQL Servers/Clusters have the connection type Connection = 'MS SQL server'.

Code Block
language sql theme Midnight title Database Credential outcome
SELECT c.Label , c.Username , o.Name as Outcome , ch.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'

...

Code Block
language sql theme Confluence
(CONVERT(DATE,AttemptDate) >= '2018-04-18' AND CONVERT(DATE,AttemptDate) <= '2018-04-20'))

Display credential failures/credential success as a pivot table.

We will need to use the combination of IP Address and Instance name as the left hand column, and the credentials attempted as the other columns,
we want to display success/fail as the data points in the matrix.

For this query, we use a number of CTEs to help with readability.

This example is for Oracle Database Instance connection/login failures, and assumes there are three database credentials defined, with labels Credential1, Credential2 and Credential3. (Don't forget that the Label is what is displayed in the iQSonar UI as opposed to the login name, to allow for multiple passwords for the same user (e.g. the user SYS may have different passwords on different machines)

with ScanAttempts AS (
	-- Find the success/failures
	SELECT CONCAT( ch.Instance, ' on ', ch.[IPAddress] ) as DBInstance
		   , p.Name as [Project Name]
		   , c.Label as CredentialName
		   , c.Username
		   , o.Name as Outcome   
		   , convert(DATE,AttemptDate) as DateAttempted
		   , AttemptDate as RawDate
	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'
),
NewestScanAttempts as (
	SELECT DISTINCT DBInstance, max(DateAttempted) as RecentAttempt
	FROM ScanAttempts
	GROUP BY DBInstance
),
PivotThis as
(
SELECT sa.DBInstance, sa.CredentialName, sa.Outcome
FROM ScanAttempts sa INNER JOIN NewestScanAttempts nsa ON (sa.DBInstance = nsa.DBInstance and sa.DateAttempted = nsa.RecentAttempt)
)
select * From PivotThis 
PIVOT (
	MAX(Outcome) FOR CredentialName IN (
		[Credential1],	[Credential2],	[Credential3]
	) ) AS MyTable