Changing the color of a field in Advanced SQL Report

Must admit i'm not a SQL person, but can muck my way through most scripting.... I've written the following Advanded SQL Report to display only certain severities from the Syslog and now would like to enhance it so that i can get some color on it.

i.e IF (SysLog_SysLogSeverities.SeverityName = 'Warning') OR THEN Orange

IF (SysLog_SysLogSeverities.SeverityName = 'Critical') THEN RED

etc...

Would like for the report to display a different color for each severity. Tried a few codes for online but none works. Any ideas?

SELECT Nodes.Caption AS NodeName,
SysLog.DateTime AS Date_Time,
SysLog_SysLogSeverities.SeverityName AS Severity,
SysLog.Message AS Message

FROM
((SysLog INNER JOIN SysLogFacilities SysLog_SysLogFacilities ON
   (SysLog.SysLogFacility = SysLog_SysLogFacilities.FacilityCode)) INNER JOIN
     SysLogSeverities SysLog_SysLogSeverities ON
   (SysLog.SysLogSeverity = SysLog_SysLogSeverities.SeverityCode)) LEFT OUTER JOIN
       Nodes ON (SysLog.IP = Nodes.IP_Address)

WHERE
( DateTime BETWEEN 41525 AND 41556 )
AND
(
(Nodes.MachineType = 'net-snmp - Linux') AND
(
   (SysLog_SysLogSeverities.SeverityName = 'Warning') OR
   (SysLog_SysLogSeverities.SeverityName = 'Critical') OR
   (SysLog_SysLogSeverities.SeverityName = 'Error') OR
   (SysLog_SysLogSeverities.SeverityName = 'Emergency'))
)

ORDER BY 2 DESC

Find more posts tagged with

Reports

advanced_sql_report

network_monitoring

syslog

sql

network_management

color

custom_reports

sql_color

report

Accepted answers

All comments

FormerMember

I'm not a SQL expert but you could insert a case statement something like this which will insert a color code. I believe the .gif files are in c:\inetpub\solarwinds\netperfmon\images or c:\inetpub\solarwinds\orion\images folder. Not exactly sure which one is used for report generation. The 3 highlighted .gifs don't exist but you can search this folder and pick of your choosing or add your own .gif to this folder. There are several severity-x.gif files that may work for you. Hope this is helpful.

SELECT Nodes.Caption AS NodeName,

SysLog.DateTime AS Date_Time,

CASE SysLog_SysLogSeverities.SeverityName

WHEN 'Warning' THEN 'Warning.gif'

WHEN 'Critical' THEN 'Critical.gif'

WHEN 'Error' THEN 'Error.gif'

WHEN 'Emergency' THEN 'Emergency.gif'

END AS Status,

SysLog_SysLogSeverities.SeverityName AS Severity,

SysLog.Message AS Message

FROM ...

Message was edited by: rgward

craigcm

thnx Rgward.. will give this a try.

craigcm

getting a Syntax error with the CASE command - Incorrect syntax near the keyword 'CASE'.

FormerMember

· Sorry, I can’t test right now but did you add comma at end of

· SysLog.DateTime AS Date_Time,

and substitute valid .gif filenames?

craigcm

Yes I did. I also used the full path for the images.

CASE SysLog_SysLogSeverities.SeverityName,

WHEN 'Warning' THEN 'C:\inetpub\SolarWinds\Orion\images\warning_16x16.gif'

WHEN 'Critical' THEN 'C:\inetpub\SolarWinds\Orion\images\Crit.jpg'

justaweso

Have you had any luck with this? None of these answers seem to address your question, I'm running into the same issue. Here's a pre-built report I can add to my home page:

NPM alert color.PNG

On my own advanced sql report I can only get white rows:

NPM alert custom.PNG

Background color is a field in the EventTypes table, but I can't seem do do much with it. I don't care about the icons, I want to see the entire row using the appropriate background color. Here's a shot of the data in the table - showing the color field that the pre-built report is pulling from...

eventtype table.PNG

craigcm

Unfortunately not. Havent had much time to look further into this either.

julionavarro1414

Hi!

Any luck with this? That is the only detail I am missing from my reports :-(

Thank you

craigcm

no. unfortunately.... its kinda on the back burner for now...

tony.johnson

Looks like the value in the backcolor field can be leveraged but requires some supporting scripts

Re: Change Row Colors in Reports