After searching through multiple blogs and forums, I finally wrote a script to find the PUB details for custom reporting purpose.
Here is the SQL script for MS-Sql Server, to find out the current PUB node of the UCCX.
Step1:
Make ODBC Informix connections to UCCX ( 1st with pub and 2nd with sub) using hruser or wallboarduser. It allows you to OpenQuery into UCCX details and replicate data to your custom Db if needed.
Step2:
Run the following script to find which ODBC is the Primary / Pub / Active.
declare @S nvarchar(max) = 'SELECT @x=enddatetime
FROM OPENQUERY(UCCX2, ''select * from RtICDStatistics'' ) 'declare
@xx datetime
set
@xx = 0
exec sp_executesql @S, N'@x datetime2 out',
@xx out
declare @S2 nvarchar(max) = 'SELECT
@x2=enddatetime
FROM OPENQUERY(UCCX, ''select * from RtICDStatistics'' ) '
declare
@xx2 datetime
set
@xx2 = 0
exec sp_executesql @S2, N'@x2 datetime2 out',
@xx2 out
BEGIN
IF
@xx >
@xx2 BEGIN
PRINT ('Latest is Odbc UCCX2 '+ cast(
@xx as varchar ) )
declare
@data nvarchar(max) = 'SELECT *
FROM OPENQUERY(UCCX2, ''select * from RtICDStatistics'' ) '
EXEC (@Data)
END
ELSE
BEGIN PRINT('Latest is Odbc UCCX '+ cast(
@xx2 as varchar ))
declare @Data2 nvarchar(max) = 'SELECT *
FROM OPENQUERY(UCCX, ''select * from RtICDStatistics'' ) '
EXEC (@Data2)
END
END