DX Unified Infrastructure Management

How can I convert this numeric value (which should be '4'), into a text that says 'Active' ?

SQL:

SELECT s.samplevalue

FROM s_Qos_data d

JOIN s_qos_snapshot s
on d.table_id = s.table_id

WHERE d.source = 'pulfcsw193'
AND d.target = 'pulfcsw193'
AND d.qos = 'QOS_SNMP_VARIABLE_PORT_15__193_PORTHWSTATE'

Possible values are:

• unknown (1)

• failed (2) Port failed diagnostics (port_flt_state).

• bypassed (3) FCAL bypass, loop only (not used).

• active (4) Connected to a device (light and sync are present).

• loopback (5) Port in ext loopback (loopback state).

• txfault (6) Transmitter fault (bad GBIC).

• noMedia (7) Media not installed (GBIC removed).

• linkDown (8) Waiting for activity—rx sync (light with no sync).

QOS is stored int he database as numeric values.

We do not store QOS as text so I am not sure how you would accomplish what you are looking to in the current design of the software.

You would ise an if then else statement in your SQL query to check the value of the field and return it as 1 for active or 0 for not active.

in your custom dashboard you could then display this as text for active or not.

Or might be easier in your custom dashboard to just check the value and display the test you want.

Hi Gene,

It's the SQL-query that I want to change so it can convert the numeric value into a text string.

So if the value is '4' i want to receive the value 'Active' and if the value is '7' i want to receive the value 'noMedia'.

If I do this in the dashboard without the convert, I would need a help text on the side, which says what status the current number is.

so lets clarify, where are you running this query?

If you are using the sqlserver probe or other probe there is no way to get this into the database to be displayed on a remote as the database sample value is a numeric field

below is a link on how to use an if then else or case statement in a SQL select query.

sql server - How do I perform an IF...THEN in an SQL SELECT? - Stack Overflow 

This same logic could be used in a dashboard

I'm running this on the dashboard with a text-widget. So I want that text to say 'Active' if the value is '4', and 'noMedia' if the value is '7', and so on.

So I guess I need the numeric value to turn in to a text string somehow.

CASE (Transact-SQL) | Microsoft Docs 

About half way down is an example of exactly (at least in my understanding of your ask) what you want to do.

-Garin

Try this:

SELECT case s.samplevalue
when 1 then 'unknown (1)'
when 2 then 'failed (2) Port failed diagnostics (port_flt_state).'
when 3 then 'bypassed (3) FCAL bypass, loop only (not used).'
when 4 then 'active (4) Connected to a device (light and sync are present).'
when 5 then 'loopback (5) Port in ext loopback (loopback state).'
when 6 then 'txfault (6) Transmitter fault (bad GBIC).'
when 7 then 'noMedia (7) Media not installed (GBIC removed).'
when 8 then 'linkDown (8) Waiting for activity—rx sync (light with no sync).'
end as ErrorText

FROM s_Qos_data d
JOIN s_qos_snapshot s
on d.table_id = s.table_id
WHERE d.source = 'pulfcsw193'
AND d.target = 'pulfcsw193'
AND d.qos = 'QOS_SNMP_VARIABLE_PORT_15__193_PORTHWSTATE'

Garin, my man!! Thank you  That's exactly what I was looking for!