| 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. |
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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT DISTINCT Connection FROM [history].[t_ConnectionHistory] |
The following queries will enable you to investigate database connection issues:
| 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. |
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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT DISTINCT Type FROM [history].[v_DiagnosticsApplicationList] |
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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'.
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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' |
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
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT c.Label
, c.Username
, o.Name as Outcome
, ch.[IPAddress]
, ch.Instance -- Instance for MS SQL is less useful than for Oracle, but still interesting
, 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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
(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)
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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 |