I have a group created to capture all volumes that are in a Critical status due to low free space. For alerting and reporting purposes, I need a SWQL query to show the members of this group.
I have a group created to capture all volumes that are in a Critical status due to low free space. For alerting and reporting purposes, I need a SWQL query to show the members of this group.
LatteLarry, here's a starting place for you
SELECT GM.DisplayName, GM.DetailsURL, GM.Status FROM Orion.ContainerMembers GM WHERE GM.ContainerID = '6' Order by GM.Status DESC
Follow up question to this... I am trying to pass the SWQL into an alert message as a variable. The output works fine as a query. The query gives me multiple output rows for the members, as expected. However, when placed as a variable in an alert message it seems to only show one row of output.
Are you familiar with a trick to get it to show all rows? Or perhaps the query needs to be done differently? I tried removing the "Order By", but same result.
LatteLarry, yea there is a trick but it's a PITA. You have to convert it to a SQL variable first of cause the function to do it is not available in SWQL. Below is the starting point that i use everytime i have to do it, and then i just modify it as needed.
SELECT cast(cast([ComponentName]as varchar(max)) + '' + '--' as XML), cast(cast([StatusOrErrorDescription]as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML) FROM [ORION].[dbo].[APM_AlertsAndReportsData] where ApplicationId = ${ID} FOR XML PATH('')
*EDIT*
tagging Seashore cause he's much better at this than me
christopher.t.jones123 i can't resist a challange :-)
Created below line that takes out all members from the group "test" and write them with a "</BR>" after each. So if you html-format your email you get them one at each line.
One trick with the SQL-variables is that the query has to be in one line. No line-breaks, so it's a bit hard to read. Also, the output has to be one cell large.
${SQL: SELECT REPLACE(Replace((SELECT CM.FullName + '</BR>' FROM Containers AS C INNER JOIN ContainerMemberSnapshots AS CM ON C.ContainerID=CM.ContainerID WHERE C.Name = 'Test' FOR XML PATH ('') ), '<', '<'), '>', '>') AS [Result]}
Hope it work for you LatteLarry as you wanted.
christopher.t.jones123 i can't resist a challange :-)
Created below line that takes out all members from the group "test" and write them with a "</BR>" after each. So if you html-format your email you get them one at each line.
One trick with the SQL-variables is that the query has to be in one line. No line-breaks, so it's a bit hard to read. Also, the output has to be one cell large.
${SQL: SELECT REPLACE(Replace((SELECT CM.FullName + '</BR>' FROM Containers AS C INNER JOIN ContainerMemberSnapshots AS CM ON C.ContainerID=CM.ContainerID WHERE C.Name = 'Test' FOR XML PATH ('') ), '<', '<'), '>', '>') AS [Result]}
Hope it work for you LatteLarry as you wanted.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.