
# Generate TNSNames
select 
   decode(mc.metric_column,
       'Status',     regexp_replace(tgtlsn.display_name,'LISTENER_FIL(\S+)_(.*)','\1')||' = ',    
       'alias',    '  (DESCRIPTION =',
       'tnsAddress', '    '||mc.value,
       'tnsPing',    '    (CONNECT_DATA = (SERVICE_NAME='||regexp_replace(tgtlsn.display_name,'LISTENER_(\S+)_(.*)','\1')||'))',
       'tnserrmsg',  '  )'
   )
from
  mgmt$target tgtlsn,
  mgmt$metric_current mc
where tgtlsn.last_metric_load_time > sysdate-1
  and (tgtlsn.display_name like 'LISTENER_FIL%U1%' or tgtlsn.display_name like 'LISTENER_FIL%X1%' or tgtlsn.display_name like 'LISTENER_FIL%E1%')
  and tgtlsn.target_type = 'oracle_listener'
  and tgtlsn.target_guid = mc.target_guid
  and mc.metric_column in ('Status','alias','tnsAddress','tnsPing','tnserrmsg')
order by tgtlsn.target_name, mc.metric_column
/
