...
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
SELECT DISTINCT Type FROM [history].[v_DiagnosticsApplicationList] |
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 |
|---|
| language | sql |
|---|
| theme | Midnight |
|---|
| title | Database Info |
|---|
|
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 |
|---|
| 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' |
...