...
SQL/Oracle credentials which succeeded or failed (per project)
| Warning |
|---|
These queries work with iQSonar releases up to and including Fu R3. the history.t_ ConnectionHistory tableConnectionHistory table. |
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' |
...