Hi,
I would like to display alerts using custom query and change text colour based on severity can you please advise how this can be achieved.
The custom query resource does not provide a way to control text color.
The custom query custom table (reporting) resource allows unquoted HTML. You can use this to set the text color, perhaps by using a CASE ... WHEN ... END to choose the color based on some condition.
Edit 2017-05-26: I mixed up which resource type allows HTML tags.
There are ways of incorporating color into custom queries but they are difficult. The example below is generated by the custom sql that follows afterwards
select top 25
EventTime,
NetObjectID,
Events.EventType,
'<img src=/NetPerfMon/images/Event-' + LTRIM(STR(Events.EventType,10)) + '.gif> ' + '<a href=/Orion/View.aspx?View=NodeDetails&NetObject=N:' + LTRIM(STR(NetObjectID,10)) + '><font color=' + Icon + '>' + Message + '</font></a>' as Message
from
events join EventTypes on Events.EventType=EventTypes.EventType
where
NetObjectID in (select nodeid from nodes) and
EventTypes.EventType in (1,2,5,8,9,14,40,41,100,940,941,942,3701)
order by
EventTime desc
NOTE: I removed a portion of the sql where clause to protect the guilty (me!)
Chris
Good point! I forgot about that method.
Thanks for the clarification, can this be used in SWQL?
Like this:
SELECT '<span style="color:' +
CASE Status
WHEN 0 THEN 'red'
WHEN 1 THEN 'green'
WHEN 9 THEN 'purple'
ELSE 'black'
END + '">' + Caption + '</span>' AS ColorCaption
FROM Orion.Nodes
Agreed. Your's is simpler. I was recreating the exact color coding and icon selections in a special event view for a customer.
tdanner
You say this works in the custom query, you sure thats not just in custom table/report? When I try this code in a custom query resource I get this
I can use it successfully in a custom table by checking the allow HTML box.
Is there a bit I'm missing?
And if that is WAI, how many cases of what liquor do I have to buy your devs to get an Allow HTML check box added to the custom query resource? (i'm only slightly joking)
No, you are not missing anything. I was mixed up on which resource had that option. I opened CORE-8624 to get the option added to Custom Query resource. Just write that bug number on the cases of liquor before you ship them.
Is it possible to use CAST in SWQL?
/MSARKAR
SWQL doesn't have a general-purpose CAST or CONVERT operation. There is a "ToString" function that will force any data to be a string. What kind of conversion do you need to do?
Nice custom SQL Cgregors ! That work well !
Will be possible to change the association with the events by an association with the severity of the alerts ?
'select top 25
events join EventTypes on Events.EventType=EventTypes.EventType'
tdanner, do we have a way in swql of converting int to float since there is no cast? I'm getting a "The conversion of the varchar value overflowed an int column". Trying to count a lot of calls in VNQM.
Thanks.
Paulo
"+ 0.0" ought to work. If you post a full query I might be able to give more specific advice.
Apologies tdanner, should have included the query. Please see below:
SELECT v.CallManagerName, v.DestCCMRegionName,
CASE v.OrigCause_value
WHEN '0' THEN 'No error'
WHEN '1' THEN 'Unassigned number'
WHEN '2' THEN 'No route to specific transit network'
WHEN '3' THEN 'No route to destination'
END AS Cause,
COUNT(v.OrigCause_value) AS CauseCount
FROM Orion.IpSla.VoipCallDetails v
WHERE v.DateTime > AddDay(-7,GETDATE())
GROUP BY v.DestCCMRegionName, v.CallManagerName, v.OrigCause_value
ORDER BY v.CallManagerName, CauseCount DESC
I'm trying to sum up all the calls that have the same disconnection cause for the past week. I just shortened the number of conditions in my CASE statement for easy reading. Thanks!
I haven't been able to figure out what's going on here. I don't have a VNQM system available that has any call records, so I can't give the query a proper test. It works fine with no calls though. Examining the SWIS schema and the underlying SQL views and table schemas didn't give me a clue.
Can you send me the full error with stack trace? You should be able to get that from SWQL Studio.
Just sent it to you tdanner
The problem is these lines:
WHEN '2701131793' THEN 'CCM_SIP_600_BUSY_EVERYWHERE'
WHEN '2717909013' THEN 'CCM_SIP_603_DECLINE'
WHEN '2734686209' THEN 'CCM_SIP_604_DOES_NOT_EXIST_ANYWHERE'
WHEN '2751463455' THEN 'CCM_SIP_606_NOT_ACCEPTABLE'
These numbers are greater than the maximum value that can be represented by an int, which is the data type of the OrigCause_value column they are being compared to. Removing these WHEN clauses should fix the problem.
Depending on how these values flow through CCM and ultimately through VNQM, it is possible that they will show up in this column as a negative number.
You might want to add an "ELSE v.OrigCause_value" clause to this CASE expression. This way if there are any unexpected values for OrigCause_value, they will show up in the output instead of just being null.
Love this concept and code that you all have provided. Unfortunately I am still getting an 'invalid query' error when trying to use the CASE statement to append the span styling tags. For now my workaround is to modify the table and use column display settings to present an icon instead of the severity text string.
