I am running the query below but I am getting an error "Conversion failed when converting the varchar value "T" to data type int"
Here is the query what am I missing? I also included the functions that I am using
/* APC UPS State UnDP pollers
APCupsAdvStateAbnormalConditions
Status Byte 1 = 'Power Module Failure, '
Status Byte 2 = 'Main Intelligence Module Failure, '
Status Byte 3 = 'Redundant Intelligence Module Failure, '
Status Byte 4 = 'Battery Failure, '
Status Byte 5 = 'Load(kVA) Alarm Threshold Violation, '
Status Byte 6 = 'Redundancy Lost, '
Status Byte 7 = 'Redundancy Below Alarm Threshold, '
Status Byte 8 = 'Bypass notin Range; Either Frequency or Voltage, '
Status Byte 9 = 'Bypass Contactor Stuck in Bypass Condition, '
Status Byte 10 = 'Bypass Contactor Stuck in On-Line Condition, '
Status Byte 11 = 'In Bypass due to an Internal Fault, '
Status Byte 12 = 'In Bypass due to an Overload, '
Status Byte 13 = 'In Maintanence Bypass, '
Status Byte 14 = 'Input Circuit Braker Tripped Open, '
Status Byte 15 = 'System Level Fan Failure, '
Status Byte 16 = 'Redundant Intelligent Module in Control, '
Status Byte 17 = 'IIC Inter-Module Communication Failure, '
Status Byte 18 = 'No Working Power Modules, '
Status Byte 19 = 'Load Shutdown From Bypass; Input Frequency, '
Status Byte 20 = 'Runtime Below Alarm Threshold, '
Status Byte 21 = 'Extended Run Frame Fault, '
Status Byte 22 = 'Output Voltage out of Range, '
Status Byte 23 = 'UPS Not Synchronized, '
Status Byte 24 = 'No Batteries Installed, '
Status Byte 25 = 'Battery Voltage High, '
Status Byte 26 = 'UPS Specific Fault Detected, '
Status Byte 27 = 'Site Wiring Fault, '
Status Byte 28 = 'Backfeed Protection Relay Opened, '
*/
/*
APCupsBasicStateOutputState
Status Byte 1 = 'Abnormal Condition Present, '
Status Byte 2 = 'On Battery, '
Status Byte 3 = 'Low Battery, '
Status Byte 4 = '*online*, '
Status Byte 5 = 'Replace Battery, '
Status Byte 6 = '*Serial Communication Established*, '
Status Byte 7 = 'AVR Boost Active*, '
Status Byte 8 = 'AVR Trim Active*, '
Status Byte 9 = 'Overload, '
Status Byte 10 = 'Runtime Calibration, '
Status Byte 11 = 'Batteries Discharged, '
Status Byte 12 = 'Manual Bypass, '
Status Byte 13 = 'Software Bypass, '
Status Byte 14 = 'In Bypass due to Internal Fault, '
Status Byte 15 = 'In Bypass due to Supply Failure*, '
Status Byte 16 = 'In Bypass due to Fan Failure*, '
Status Byte 17 = 'Sleeping on a Timer, '
Status Byte 18 = 'Sleeping until Utility Power Returns, '
Status Byte 19 = ''*On*, '
Status Byte 20 = 'Rebooting, '
Status Byte 21 = 'Battery Communication Lost*, '
Status Byte 22 = 'Graceful Shutdown Initiated, '
Status Byte 23 = 'Smart Boost or Smart Trim Fault, '
Status Byte 24 = 'Bad Output Voltage*, '
Status Byte 25 = 'Battery Charger Failure*, '
Status Byte 26 = 'High Battery Temperature, '
Status Byte 27 = 'Warning Battery Temperature, '
Status Byte 28 = 'Critical Battery Temperature, '
Status Byte 29 = 'Self Test In Progress, '
Status Byte 30 = 'Low Battery / On Battery, '
Status Byte 31 = 'Graceful Shutdown Issued by Upstream Device, '
Status Byte 32 = 'Graceful Shutdown Issued by Downstream Device, '
Status Byte 33 = 'No Batteries Attached, '
Status Byte 34 = 'Synchronized command is in progress, '
Status Byte 35 = 'Synchronized Sleeping Command is in Progress, '
Status Byte 36 = 'Synchronized Rebooting Command is in Progress, '
Status Byte 37 = 'Inverter DC Imbalance, '
Status Byte 38 = 'Transfer Relay Failure, '
Status Byte 39 = 'Shutdown or Unable to Transfer, '
Status Byte 40 = 'Low Battery Shutdown, '
Status Byte 41 = 'Electronic Unit Fan Failure, '
Status Byte 42 = 'Main Relay Failure, '
Status Byte 43 = 'Bypass Relay Failure, '
Status Byte 44 = 'Temporary Bypass, '
Status Byte 45 = 'High Internal Temperature'
Status Byte 46 = 'Battery Temperature Sensor Fault, '
Status Byte 47 = 'Input Out of Range for Bypass, '
Status Byte 48 = 'DC Bus Overvoltage, '
Status Byte 49 = 'PFC Failure, '
Status Byte 50 = 'Critical Hardware Fault, '
Status Byte 51 = 'Green Mode, '
Status Byte 52 = 'Flag-52 set, '
Status Byte 53 = 'Flag-53 set, '
Status Byte 54 = 'Flag-54 set, '
Status Byte 55 = 'Flag-55 set, '
Status Byte 56 = 'Flag-56 set, '
Status Byte 57 = 'Flag-57 set, '
Status Byte 58 = 'Flag-58 set'
Status Byte 59 = 'Flag-59 set, '
Status Byte 60 = 'Flag-60 set, '
Status Byte 61 = 'Flag-61 set, '
Status Byte 62 = 'Flag-62 set, '
Status Byte 63 = 'Flag-63 set, '
Status Byte 64 = 'Flag-64 set, '
*/
SELECT Nodes.Caption AS Node_Name, Nodes.NodeID AS NodeID,
CustomPollerStatus3.Status as Model,
dbo.MDOT_upsAdvStateAbnormalConditions_FlagParser(CustomPollerStatus.Status) as AbnormalConditions,
dbo.MDOT_APCupsBasicStateOutputState_FlagParser(CustomPollerStatus1.Status) as APCupsBasicStateOutputState,
CustomPollerStatus2.Status as TestDiagnosticsResults
FROM Nodes
JOIN CustomPollerAssignment
ON (CustomPollerAssignment.NodeId = Nodes.NodeId )
JOIN CustomPollers
ON (CustomPollers.CustomPollerId = CustomPollerAssignment.CustomPollerId AND CustomPollers.UniqueName = 'APCupsAdvStateAbnormalConditions')
LEFT JOIN CustomPollerStatus
ON (CustomPollerStatus.CustomPollerAssignmentId = CustomPollerAssignment.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment1
ON (CustomPollerAssignment1.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers1
ON (CustomPollers1.CustomPollerId = CustomPollerAssignment1.CustomPollerId AND CustomPollers1.UniqueName = 'APCupsBasicStateOutputState')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus1
ON (CustomPollerStatus1.CustomPollerAssignmentId = CustomPollerAssignment1.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment2
ON (CustomPollerAssignment2.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers2
ON (CustomPollers2.CustomPollerId = CustomPollerAssignment2.CustomPollerId AND CustomPollers2.UniqueName = 'APcupsAdvTestDiagnosticsResults')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus2
ON (CustomPollerStatus2.CustomPollerAssignmentId = CustomPollerAssignment2.CustomPollerAssignmentId)
JOIN CustomPollerAssignment AS CustomPollerAssignment3
ON (CustomPollerAssignment3.NodeId = Nodes.NodeId )
JOIN CustomPollers AS CustomPollers3
ON (CustomPollers3.CustomPollerId = CustomPollerAssignment3.CustomPollerId AND CustomPollers3.UniqueName = 'APCupsBasicIdentModel')
LEFT JOIN CustomPollerStatus AS CustomPollerStatus3
ON (CustomPollerStatus3.CustomPollerAssignmentId = CustomPollerAssignment3.CustomPollerAssignmentId)
WHERE (Nodes.Status = '1')
AND
(
(CustomPollerStatus1.Status <> '0001010000000000000000000000000000000000000000000000000000000000'
AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000000000000000000'
AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000010000000000000'
AND CustomPollerStatus1.Status <> '0000000000000000000000000000000000000000000000000000000000000000')
OR (CustomPollerStatus2.Status = 'failed(2)'
OR CustomPollerStatus2.Status = 'invalidTest(3)')
)
ORDER by 1 ASCUSE [NetPrefMon]
GO
/****** Object: UserDefinedFunction [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser] Script Date: 11/29/2017 8:28:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Spyfly>
-- Create date: <Nov. 11, 2017>
-- Description: <MDOT_upsAdvStateAbnormalConditions_FlagParser>
-- =============================================
ALTER function [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser]
(
@val as varchar(33)
)
returns varchar(1000)
as
Begin
DECLARE @counter as int;
DECLARE @flaglist as varchar(33);
DECLARE @flagpos as int;
DECLARE @varlist as varchar(1000);
SET @counter = 0;
IF @val LIKE 'NOT SUPPORTED%'
Return '';
SET @flaglist = @val;
SET @flagpos = 0;
SET @varlist = ''; while @counter < 32
Begin
set @varlist = @varlist +CASE
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Power Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'Main Intelligence Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Redundant Intelligence Module Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN 'Battery Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Load(kVA) Alarm Threshold Violation, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN 'Redundancy Lost, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'Redundancy Below Alarm Threshold, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'Bypass notin Range; Either Frequency or Voltage, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Bypass Contactor Stuck in Bypass Condition, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Bypass Contactor Stuck in On-Line Condition, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'In Bypass due to an Internal Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'In Bypass due to an Overload, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'In Maintanence Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'Input Circuit Braker Tripped Open, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'System Level Fan Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'Redundant Intelligent Module in Control, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'IIC Inter-Module Communication Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'No Working Power Modules, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN 'Load Shutdown From Bypass; Input Frequency, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Runtime Below Alarm Threshold, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Extended Run Frame Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Output Voltage out of Range, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'UPS Not Synchronized, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'No Batteries Installed, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Voltage High, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'UPS Specific Fault Detected, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Site Wiring Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Backfeed Protection Relay Opened, '
Else ''
End
set @counter = @counter + 1
set @flagpos = @flagpos + 1
End
return substring(@varlist,0,len(@varlist))
End
USE [SolarWindsOrion]
GO
/****** Object: UserDefinedFunction [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser] Script Date: 11/29/2017 8:31:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Spyfly>
-- Create date: <Nov. 11, 2017>
-- Description: <MDOT_APCupsBasicStateOutputState_FlagParser>
-- =============================================
ALTER function [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser]
(
@val as varchar(64)
)
returns varchar(1000)
as
Begin
DECLARE @counter as int;
DECLARE @flaglist as varchar(64);
DECLARE @flagpos as int;
DECLARE @varlist as varchar(1000);
SET @counter = 0;
SET @flaglist = @val;
SET @flagpos = 0;
SET @varlist = ''; while @counter < 64
Begin
set @varlist = @varlist + CASE
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Abnormal Condition Present, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'On Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Low Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN '' /*online*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Replace Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN '' /*Serial Communication Established*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'AVR Boost Active*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'AVR Trim Active*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Overload, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Runtime Calibration, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'Batteries Discharged, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'Manual Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'Software Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'In Bypass due to Internal Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'In Bypass due to Supply Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'In Bypass due to Fan Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'Sleeping on a Timer, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'Sleeping until Utility Power Returns, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN '' /*On*/
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Rebooting, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Battery Communication Lost*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Graceful Shutdown Initiated, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'Smart Boost or Smart Trim Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'Bad Output Voltage*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Charger Failure*, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'High Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Warning Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Critical Battery Temperature, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 29 THEN 'Self Test In Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 30 THEN 'Low Battery / On Battery, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 32 THEN 'Graceful Shutdown Issued by Downstream Device, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 33 THEN 'No Batteries Attached, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 34 THEN 'Synchronized command is in progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 35 THEN 'Synchronized Sleeping Command is in Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 36 THEN 'Synchronized Rebooting Command is in Progress, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 37 THEN 'Inverter DC Imbalance, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 38 THEN 'Transfer Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 39 THEN 'Shutdown or Unable to Transfer, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 40 THEN 'Low Battery Shutdown, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 41 THEN 'Electronic Unit Fan Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 42 THEN 'Main Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 43 THEN 'Bypass Relay Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 44 THEN 'Temporary Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 45 THEN 'High Internal Temperature'
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 46 THEN 'Battery Temperature Sensor Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 47 THEN 'Input Out of Range for Bypass, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 48 THEN 'DC Bus Overvoltage, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 49 THEN 'PFC Failure, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 50 THEN 'Critical Hardware Fault, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 51 THEN 'Green Mode, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 52 THEN 'Flag-52 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 53 THEN 'Flag-53 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 54 THEN 'Flag-54 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 55 THEN 'Flag-55 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 56 THEN 'Flag-56 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 57 THEN 'Flag-57 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 58 THEN 'Flag-58 set'
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 59 THEN 'Flag-59 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 60 THEN 'Flag-60 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 61 THEN 'Flag-61 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 62 THEN 'Flag-62 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 63 THEN 'Flag-63 set, '
WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 64 THEN 'Flag-64 set, '
Else ''
End
set @counter = @counter + 1
set @flagpos = @flagpos + 1
End
return substring(@varlist,0,len(@varlist))
End