UCCE Inventory SQL

SELECT MH.HostName, MH.MachineName, MA.Address
, MS.Description, MS.ServicePort, MS.UserName, MS.Password, MS.EnablePassword
FROM Machine_Host MH (NOLOCK)
JOIN Machine_Address MA (NOLOCK) ON MA.MachineHostID = MH.MachineHostID
JOIN Machine_Service MS (NOLOCK) ON MS.MachineAddressID = MA.MachineAddressID
SELECT H.MachineHostID, H.MachineName
, CASE H.MachineType
WHEN 1 THEN 'ESXi Host'
WHEN 2 THEN 'Unified CCE Data Server'
WHEN 3 THEN 'Unified CCE Call Server'
WHEN 4 THEN 'Unified CVP'
WHEN 5 THEN 'Unified CM (Unknown)'
WHEN 6 THEN 'Unified CM Publisher'
WHEN 7 THEN 'Unified CM Subscriber'
WHEN 8 THEN 'Unified CVP Reporting Server'
WHEN 9 THEN 'Coresident Unified IC, LiveData, and Identity Server Publisher'
WHEN 10 THEN 'Coresident Unified IC, LiveData, and Identity Subscriber'
WHEN 11 THEN 'Unified CVP Operations Console'
WHEN 12 THEN 'Finesse'
WHEN 13 THEN 'Gateway'
WHEN 14 THEN 'External Social Miner'
WHEN 15 THEN 'External CM Publisher'
WHEN 16 THEN 'External CM Subscriber'
WHEN 17 THEN 'External CVP Reporting Server'
WHEN 18 THEN 'External CCE HDS'
WHEN 19 THEN 'External Media Sense'
WHEN 20 THEN 'External Enterprise Chat and Email'
WHEN 21 THEN 'External Third Party Multichannel'
WHEN 22 THEN 'Unified CCE Router'
WHEN 23 THEN 'Unified CCE Peripheral Gateway'
WHEN 24 THEN 'Unified CCE LiveData Server'
WHEN 25 THEN 'Unified CCE Primary AW'
WHEN 26 THEN 'Unified CCE Secondary AW'
WHEN 27 THEN 'Unified CCE PROGGER'
WHEN 28 THEN 'Unified CCE AW'
WHEN 29 THEN 'Unified CCE ROGGER'
WHEN 30 THEN 'Enterprise Chat and Email'
WHEN 31 THEN 'Identity Server Publisher'
WHEN 32 THEN 'Identity Server Subscriber'
WHEN 33 THEN 'Finesse Primary'
WHEN 34 THEN 'Finesse Secondary'
WHEN 35 THEN 'Standalone Unified IC Publisher'
WHEN 36 THEN 'Standalone Unified IC Subscriber'
WHEN 37 THEN 'Cisco Virtualized Voice Browser'
ELSE 'Unknown Server'
END AS MachineType
, H.Description AS HostDescription, A.Address, S.Description AS ServiceDescription
, CASE S.ServiceType
WHEN 1 THEN 'Peripheral Gateway TIP'
WHEN 2 THEN 'Router TIP'
WHEN 3 THEN 'LiveData Active MQ'
WHEN 4 THEN 'Peripheral Gateway TIP TOS'
WHEN 5 THEN 'Router TIP TOS'
WHEN 6 THEN 'LiveData Storm DRPC'
WHEN 7 THEN 'LiveData Socket.IO'
WHEN 8 THEN 'LiveData Web Service Rest API'
WHEN 9 THEN 'LiveData Cassandra'
WHEN 10 THEN 'Administration & Data Server (AW) Rest API'
WHEN 11 THEN 'Media Routing Peripheral Gateway Connection'
WHEN 1000 THEN 'ESXi'
WHEN 1002 THEN 'AXL'
WHEN 1003 THEN 'Diagnostic Portal'
WHEN 1004 THEN 'ISE Authentication'
WHEN 1005 THEN 'Gateway'
WHEN 1006 THEN 'Management Link'
WHEN 1007 THEN 'Administration'
WHEN 1008 THEN 'SocialMiner REST API'
WHEN 1009 THEN 'Media Routing Peripheral Gateway A'
WHEN 1010 THEN 'Media Routing Peripheral Gateway B'
WHEN 1011 THEN 'Principal AW'
WHEN 1012 THEN 'Context Service'
WHEN 1013 THEN 'Identity Server'
WHEN 1014 THEN 'Publisher/Primary Machine IP Address'
WHEN 1015 THEN 'Identity Server Primary Machine IP Address'
WHEN 1016 THEN 'Identity Server Secondary Machine IP'
ELSE 'Not Defined'
END AS ServiceType
, S.ServicePort, S.ServiceUri, S.Pairing
FROM Machine_Host H
JOIN Machine_Address A ON H.MachineHostID = A.MachineHostID
JOIN Machine_Service S ON S.MachineHostID = H.MachineHostID
SELECT H.MachineHostID, H.MachineName
, CASE H.MachineType
WHEN 1 THEN 'ESXi Host'
WHEN 2 THEN 'Unified CCE Data Server'
WHEN 3 THEN 'Unified CCE Call Server'
WHEN 4 THEN 'Unified CVP'
WHEN 5 THEN 'Unified CM (Unknown)'
WHEN 6 THEN 'Unified CM Publisher'
WHEN 7 THEN 'Unified CM Subscriber'
WHEN 8 THEN 'Unified CVP Reporting Server'
WHEN 9 THEN 'Coresident Unified IC, LiveData, and Identity Server Publisher'
WHEN 10 THEN 'Coresident Unified IC, LiveData, and Identity Subscriber'
WHEN 11 THEN 'Unified CVP Operations Console'
WHEN 12 THEN 'Finesse'
WHEN 13 THEN 'Gateway'
WHEN 14 THEN 'External Social Miner'
WHEN 15 THEN 'External CM Publisher'
WHEN 16 THEN 'External CM Subscriber'
WHEN 17 THEN 'External CVP Reporting Server'
WHEN 18 THEN 'External CCE HDS'
WHEN 19 THEN 'External Media Sense'
WHEN 20 THEN 'External Enterprise Chat and Email'
WHEN 21 THEN 'External Third Party Multichannel'
WHEN 22 THEN 'Unified CCE Router'
WHEN 23 THEN 'Unified CCE Peripheral Gateway'
WHEN 24 THEN 'Unified CCE LiveData Server'
WHEN 25 THEN 'Unified CCE Primary AW'
WHEN 26 THEN 'Unified CCE Secondary AW'
WHEN 27 THEN 'Unified CCE PROGGER'
WHEN 28 THEN 'Unified CCE AW'
WHEN 29 THEN 'Unified CCE ROGGER'
WHEN 30 THEN 'Enterprise Chat and Email'
WHEN 31 THEN 'Identity Server Publisher'
WHEN 32 THEN 'Identity Server Subscriber'
WHEN 33 THEN 'Finesse Primary'
WHEN 34 THEN 'Finesse Secondary'
WHEN 35 THEN 'Standalone Unified IC Publisher'
WHEN 36 THEN 'Standalone Unified IC Subscriber'
WHEN 37 THEN 'Cisco Virtualized Voice Browser'
ELSE 'Unknown Server'
END AS MachineType
, H.Description AS HostDescription, A.Address
, CASE A.AddressType
WHEN 1 THEN 'Public'
WHEN 2 THEN 'Private'
ELSE 'ERROR'
END AS AddressType
FROM Machine_Host H
JOIN Machine_Address A ON H.MachineHostID = A.MachineHostID
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [MachineServiceID]
	  ,[M].[MachineName]
      ,[S].[MachineHostID]
      ,[S].[MachineAddressID]
      ,[S].[ServiceType]
      ,[S].[ServicePort]
      ,[S].[ServiceUri]
      ,[S].[Pairing]
      ,[S].[UserName]
      ,[S].[Password]
      ,[S].[EnablePassword]
      ,[S].[AutoGenerated]
      ,[S].[Description]
      ,[S].[MachineConnectionProfileID]
      ,[S].[OutOfSyncTimestamp]
  FROM [ucce_awdb].[dbo].[Machine_Service] S
  LEFT JOIN Machine_Host M ON [M].MachineHostID = [S].MachineHostID
 
  ORDER BY Pairing DESC

C:\icm\tomcat\log
C:\temp\ICMInstall.log
C:\icm\logs\websetup.log
C:\temp\icmsetup.log
C:\icm\vmValidation

  • vendors/cisco/uc/icm/sql/inventory.txt
  • Last modified: 2020/01/29 14:54
  • by gerardorourke