--创建视图.业务系统视图(asset_bus_view)
create or replace view asset_bus_view as
select group_uuid as id,
group_name as name,
parent_uuid as parentid,
note as description
from org_group
where logicdelete = '0';--授权视图
grant select on amsesa.asset_bus_view to aqgk;--创建视图.资产视图(asset_view)
create or replace view asset_view as
select
tt.id,tt.name,tt.resCategory,tt.resType,tt.ip,
tt.adminPort,tt.adminProtocol,tt.loginPort,tt.loginType,
tt.url,tt.status,tt.resGroups
from (
(
SELECT APP.RESUUID AS id,
AST.RESNAME AS name,
'APPRESOURCE' AS resType,
'' AS adminAcct,
'APPRES' AS resCategory,
AST.RESIP AS ip,
'' AS adminAcctPwd,
'' AS adminPort,
'' AS adminProtocol,
'' AS dbName,
'' AS domainCtrl,
'' AS domainDn,
'' AS enableAcct,
'' AS enableAcctPwd,
'' AS hostId,
'' AS instanceName,
'' AS loginAcct,
'' AS loginAcctPwd,
'' AS loginPort,
'' AS loginType,
APP.SSOAGENTFILECONTENT AS parameter,
AA.RESGROUPS AS resGroups,
APP.RESNUM AS resNum,
'' AS reserve,
'' AS serviceName,
APP.SSOMODE AS ssoType,
CASE WHEN AST.RESSTATUS IS NULL THEN '0'
ELSE AST.RESSTATUS END AS status,
'' AS tnsName,
CASE APP.SSOMODE
WHEN 'ticket' THEN
APP.ACTIONURL
ELSE
APP.LOGINURL
END AS url
FROM E_APPRESOURCE APP
LEFT JOIN AST_ASSET AST
ON APP.RESUUID = AST.RESUUID
LEFT JOIN((SELECT RESUUID,
LISTAGG(GROUP_UUID, ',') WITHIN GROUP(ORDER BY GROUP_UUID) AS RESGROUPS
FROM R_RESGROUP_RES
GROUP BY RESUUID)) AA
ON AA.RESUUID = APP.RESUUID
WHERE AST.Deleted = '0'
)
UNION ALL
(
SELECT SYSRES.RESUUID AS id,
AST.RESNAME AS name,
CASE
WHEN SYSRES.DRIVERTYPE LIKE '%SQLSERVER%' THEN
'SQLSERVER'
WHEN SYSRES.DRIVERTYPE LIKE '%INFOMIX%' THEN
'INFOMIX'
WHEN SYSRES.DRIVERTYPE LIKE '%DB2%' THEN
'DB2'
WHEN SYSRES.DRIVERTYPE LIKE '%SYBASE%' THEN
'SYBASE'
WHEN SYSRES.DRIVERTYPE LIKE '%MYSQL%' THEN
'MYSQL'
WHEN SYSRES.DRIVERTYPE LIKE '%ORACLE%' THEN
'ORACLE'
WHEN SYSRES.DRIVERTYPE LIKE '%LOADBALANCE%' THEN
'LOADBALANCE'
WHEN SYSRES.DRIVERTYPE LIKE '%FIREWALL%' THEN
'FIREWALL'
WHEN SYSRES.DRIVERTYPE LIKE '%NETSCREEN%' THEN
'FIREWALL'
WHEN SYSRES.DRIVERTYPE LIKE '%ROUTE%' THEN
'ROUTER'
WHEN SYSRES.DRIVERTYPE LIKE '%SWITCH%' THEN
'SWITCH'
WHEN SYSRES.DRIVERTYPE LIKE
'%NETDEVICE_JSAT7605_WLANAC_SSH_DRIVER%' THEN
'SWITCH'
WHEN SYSRES.DRIVERTYPE LIKE '%NETUNIT%' THEN
'NENUNIT'
WHEN SYSRES.DRIVERTYPE LIKE '%NETDEVICE%' THEN
'ROUTER'
WHEN SYSRES.DRIVERTYPE LIKE '%DOMAININNER%' THEN
'DOMAIN_INNER'
WHEN SYSRES.DRIVERTYPE LIKE '%DOMAIN_INNER%' THEN
'DOMAIN_CTRL'
WHEN SYSRES.DRIVERTYPE LIKE '%WINDOWS%' THEN
'WINDOWS'
WHEN SYSRES.DRIVERTYPE LIKE '%LINUX%' THEN
'LINUX'
WHEN SYSRES.DRIVERTYPE LIKE '%UNIX%' THEN
'UNIX'
WHEN SYSRES.DRIVERTYPE LIKE '%UNIX%' THEN
'UNIX'
ELSE
'UNIX'
END AS resType,
SYSRES.ADMINACCT AS adminAcct,
CASE
WHEN SYSRES.RESTYPE LIKE '%APPRES%' THEN
'APPRES'
WHEN SYSRES.RESTYPE LIKE '%WINDOWS%' THEN
'HOST'
WHEN SYSRES.RESTYPE LIKE '%UNIX%' THEN
'HOST'
WHEN SYSRES.RESTYPE LIKE '%NETUNIT%' THEN
'HOST'
WHEN SYSRES.RESTYPE LIKE '%DATABASE%' THEN
'DATABASE'
WHEN SYSRES.RESTYPE LIKE '%NETDEVICE%' THEN
'NETDEVICE'
ELSE
'HOST'
END AS resCategory,
AST.RESIP AS ip,
SYSRES.ADMINPWD AS adminAcctPwd,
SYSRES.ADMINPORT AS adminPort,
'TELNET' AS adminProtocol,
SYSRES.RESDBNAME AS dbName,
SYSRES.DEVDOMAINCTRLER AS domainCtrl,
SYSRES.DEVDOMAINDN AS domainDn,
SYSRES.RESNETDEVENABLECMD AS enableAcct,
SYSRES.RESNETDEVENABLEPWD AS enableAcctPwd,
SYSRES.RESREFERENCE AS hostId,
'' AS instanceName,
SYSRES.SYNACCT AS loginAcct,
SYSRES.SYNACCTPWD AS loginAcctPwd,
COMMINFO.RESCOMMPORTS AS loginPort,
COMMINFO.RESCOMMTYPES AS loginType,
'' AS parameter,
AA.RESGROUPS AS resGroups,
'' AS resNum,
'' AS reserve,
SYSRES.RESSERVERNAME AS serviceName,
'' AS ssoType,
AST.RESSTATUS AS status,
'' AS tnsName,
'' AS url
FROM E_SYSRESOURCE SYSRES
LEFT JOIN AST_ASSET AST
ON AST.RESUUID = SYSRES.RESUUID
LEFT JOIN((SELECT RESUUID,
LISTAGG(RESCOMMTYPE, ',') WITHIN GROUP(ORDER BY RESUUID) AS RESCOMMTYPES,
LISTAGG(RESCOMMPORT, ',') WITHIN GROUP(ORDER BY RESUUID) AS RESCOMMPORTS
FROM E_SYSRESCOMMINFO
GROUP BY RESUUID)) COMMINFO
ON COMMINFO.RESUUID = SYSRES.RESUUID
LEFT JOIN((SELECT RESUUID,
LISTAGG(GROUP_UUID, ',') WITHIN GROUP(ORDER BY GROUP_UUID) AS RESGROUPS
FROM R_RESGROUP_RES
GROUP BY RESUUID)) AA
ON AA.RESUUID = SYSRES.RESUUID
WHERE AST.Deleted = '0'
)
) tt;
部门视图:SELECT * from amsesa.depart_view;
用户视图:SELECT * from amsesa.user_view;
资产视图:select * from amsesa.asset_view;
业务系统:select * from amsesa.asset_bus_view;