This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Need a basic SWQL query to list members of a 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.

Parents
  • , 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.

  • , 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  cause he's much better at this than me

  • 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 as you wanted.

Reply
  • 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 as you wanted.

Children
No Data