Oracle SQL query

[hudatolah]

tpl 1.14 module AssetInfoFromSQL_Oracle;

metadata
origin := "BlackholeSurfer";
tree_path := 'BlackholeSurfer','Database','Oracle';
end metadata;

from SearchFunctions import SearchFunctions 1.5;
from Oracle.OracleRDBMS import DatabaseServer 6.0;

definitions OracleDetails 4.0
"""
Relational Database Queries
"""
type := sql_discovery;
group := "Oracle RDBMS";
define getSerialNumber
"Returns some value from oracle Database with SQL. In the case below it's the db_app_info table with column serialnumber"

query := "select serialnumber from addm_scan.db_app_info";
end define;
end definitions;

pattern DBSerialNumber_Oracle 1.5
"""
When a host is discovered, query the DB and get a value
"""
overview
tags CUSTOM;
end overview;

triggers
on ora_db_detail := Database created, confirmed where type = "Oracle Database";
end triggers;


body
si_list := search(in ora_db_detail traverse Detail:Detail:ElementWithDetail:SoftwareInstance);

if not si_list then
stop;
end if;

si := si_list[0];

if not si then
stop;
end if;

pdb_service_name := none;
pdbSericeName := none;

if not ora_db_detail.instance = 'CDB$ROOT' and ora_db_detail.service_names then
pdbServiceName := none;
for oraDetail in ora_db_detail.service_names do
if oraDetail has substring 'hudsky.com' then
pdbServiceName := oraDetail;
end if;
end for;

if not pdbServiceName then
pdbServiceName := ora_db_detail.service_names[0] + ".hudsky.com";
end if;
pdb_service_name := pdbServiceName;
end if;

/// ------ END Section

query_results := none;
method := none;
ip := none;
sid := none;
db_port := 1599;
service := none;

if not si.success_login_credential then
stop;
end if;

if si.success_login_credential then
db_host := host;
sid := si.instance;
method := si.success_login_credential['method'];
db_port := si.success_login_credential['port'];
service := si.success_login_credential['service_name'];
ip := si.success_login_credential['ip'];

if ip then
db_hosts, node_type := SearchFunctions.getHostingNodes(db_host, ip);
if db_hosts and node_type = 'Host' then
db_host := db_host[0];
else
db_host := none;
end if;
end if;

if db_host then
if method = 'service' then
if pdb_service_name then
service_name := pdb_service_name;
else
service_name := service;
end if;

if ip and and db_port then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, address := ip, port := db_port, service := service_name);
elif ip then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, address := ip, service := service_name);
elif db_port then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, port := db_port, service := service_name);
else
query_results := OracleDetails.getSerialNumber(endpoint := db_host, service := service_name);
end if;

elif method = 'sid' and not pdb_service_name then // choose approved method
if ip and db_port then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, address := ip, port := db_port, sid := sid);
elif ip then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, address := ip, sid := sid);
elif db_port then
query_results := OracleDetails.getSerialNumber(endpoint := db_host, port := db_port, sid := sid);

else
query_results := OracleDetails.getSerialNumber(endpoint := db_host, sid := sid);
end if;
end if;
end if;

db := none;
serialnumber := none;
qresult := none;

if query_results then
qresult := query_results[0].serialnumber; /// Remember this is the table column serialnumber
log.info("qresult is '%qresult%'");
end if;

ora_db_detail.serial := '%qresult%';
model.addDisplayAttribute (ora_db_detail, "serial");
end if; // Ends the login

end body;
and pattern;