Columns missing from reports

I have a swql report:

SELECT N.Caption AS NodeName
	,N.IPAddress
	,SME.Description AS Type
	,SME.StatusDescription AS STATUS
	,SME.AncestorDisplayNames AS Object_details --, N.NodeStatusRootCause
FROM Orion.Nodes AS N
JOIN System.ManagedEntity AS SME ON SME.AncestorDisplayNames LIKE CONCAT (
		'%'
		,N.Caption
		,'%'
		)
JOIN Orion.NodesCustomProperties AS CP ON N.NodeID = CP.NodeID
WHERE CP.Team = 'NetSecOps'
	AND N.Vendor LIKE 'Cisco'
	AND SME.Description NOT LIKE ''
	AND SME.StatusDescription NOT IN (
		'Up'
		,'Node status is Up.'
		,'Unknown'
		,'Shutdown'
		,'Unmanaged'
		)
	--AND N.StatusDescription NOT LIKE '%Unmanaged%'
	AND N.Caption NOT IN (
		SELECT N.Caption
		FROM Orion.AlertSuppression AS A
		JOIN Orion.nodes AS N ON N.uri = A.EntityURI
		)
ORDER BY N.Caption

This gives me a report with these columns:

zzlcapd5	15.14.10.55	RAM	Critical	[Physical memory, zzlcapd5]
zzlcapd5	15.14.10.55	Other	Critical	[Cached memory, zzlcapd5]
zzlcapd5	15.14.10.55	Other	Critical	[Shared memory, zzlcapd5]
zzlcapd8	15.14.10.58	RAM	Critical	[Physical memory, zzlcapd8]
zzlcapd8	15.14.10.58	Other	Critical	[Cached memory, zzlcapd8]
zzlcapd8	15.14.10.58	Other	Critical	[Shared memory, zzlcapd8]

So this report is providing exactly what I need, when I run it in SWIS or SWQL studio.

But if I run it as a scheduled report inside of solarwinds, the last column comes over as System.String[].

zzlcapd5	15.14.10.55	RAM	Critical	System.String[]
zzlcapd5	15.14.10.55	Other	Critical	System.String[]
zzlcapd5	15.14.10.55	Other	Critical	System.String[]
zzlcapd8	15.14.10.58	RAM	Critical	System.String[]
zzlcapd8	15.14.10.58	Other	Critical	System.String[]
zzlcapd8	15.14.10.58	Other	Critical	System.String[]

How can I show the data that is supposed to be in that column? The json that is emitted in SWIS has between one and three entries, the first one being CRITICAL to the meaning of the row. Without it all I know is I have 257 critical "other" problems. That the descriptive name of the object is lost is a major problem. How can I produce these reports in the "all reports" section so the report displays the data in the System.String[]?

George

Find more posts tagged with

swis

SWQL Studio

report

Accepted answers

keoki

Ok, I found a solution!!!

If I schedule the report as a PDF, it comes through wrong. But if I schedule it as a excel file it comes through correctly. The key for me was to ignore the previews of the report and not use PDF as the format, because the previewer and PDF tools are not displaying the report the same way the other tools do.

I am a happy camper! Thanks for looking at my swql, Kevin... You have looked at this report before, and were quite clear in your warnings that I was drilling really deep into the database. I think you used other words, and in your head I'm sure they were colorful words... I *know* my swql is scary as it descends into the depths of the database, it essentially probes the state of every sub-part of every part of every object. That is an expensive exhaustive report, but I am in the expensive exhaustive report department. My data sets are small compared to some of the things my team works on.

All comments

KMSigma.SWI

What does this return?

SELECT [Nodes].Caption AS NodeName
     , [Nodes].IPAddress
     , [Nodes].StatusDescription
     , [Nodes].NodeStatusRootCause
     -- , [Nodes].AncestorDisplayNames
     , REPLACE([Nodes].AncestorDisplayNames, ']', '') AS [BetterAncestoryNames]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].StatusDescription NOT IN ('Up', 'Node status is Up.', 'Unknown', 'Shutdown', 'Unmanaged')

keoki

That report displays a lot of nodes, but when I tried to select a node to display in each report for comparison, and it doesn't even have my node in the report at all. It is "a" report, but it doesn't have any of my nodes or desired data. Kind of funny, It seems like it is a list of nodes specifically not on my report.

My report is the report I want, and it works very well. In my environment, one of the largest retailers in the country, with a large percentage of what we sell is special orders, we have an extensive network. The team this report serves have at least 300 active alert conditions at all times. 290 of these "alert conditions" we want displayed as a report so that teams of people can work them in an organized manner. The remaining ten critical alerts we have worked by our best engineers. So those 10 most important alerts actually become alerts in solarwinds. The 290 other alerts are divided by vendor, and handed to the teams for each vendor. These alerts are housekeeping alerts like physical memory, virtual memory, swap, cpu, fan speeds, temperatures, etc. Basically all thresholds that are important, but not causing an outage. So 290 things that need to be fixed someday, and 10 alerts that MUST be fixed now because they cost a million dollars a minute when they are down. This report is my 290 things. And it is not ALL low level alerts, it is just the alerts for one department. My department only has about 20% of the assets inside of SolarWinds. I have 5 reports like this, one for each of 4 vendors, and one as a catch-all for a handful of vendors with only a few devices.

The problem is when a column is displaying a list or array, you have to iterate through the objects in the array to display them. SWQL studio does this, but the SolarWinds report doesn't display any of the data in the column that is a column of lists.

The reason this column is important is because it tells us what we are reporting, it is the description of the error. The "type" column says "other" more than half of the time, so I need the object_details column so I know what "other" even means. Sometimes "type" is "fixed disk", and so I get the name of the bad disk from the "object_details" column.

But the point is the report is working fine in SWIS as well as SWQL studio. But it is a report, and should live in the reports, but when I put it there, the most important column doesn't display. Without that column I have mostly "other" as the description of the issue. Other covers a lot of ground, and isn't helpful.

keoki

Ok, I found a solution!!!

KMSigma.SWI

All good. I was just wondering if stripping odd the ']' would "undo" the array and make it visible. Glad you found a solution!