Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Using SQL in an email action to list virtual machines associated with a host failure event

Hi, I'm hoping someone can help here, I'm trying to setup an email alert action to show a list of vm's in an email, when a host failure is detected. That host failure event is caught by the vmware events log, which works just fine. Not all VM's are nodes though, so I can't use a parent/child node relationship here.

I've got as far as being able to get the host name out of the vm event log but i can't get the macro to populate with a list of vm's.

This is what I currently have.

${N=SQL:M=select vm.name from vim_VirtualMachines vm
left join vim_Hosts H on vm.HostID = h.HostID
where H.HostName = '${SQL: SELECT
SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('host failure of host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') + LEN('host failure of host '),
CHARINDEX(' in cluster','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('host failure of host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - LEN('host failure of host ')
) AS Message
}' FOR XML PATH ('')}

the hostname part of the macro is fine, it extracts the name from the event log message.

Here's what I end up with in the email content, though.

${N=SQL:M=select vm.name from vim_VirtualMachines vm
left join vim_Hosts H on vm.HostID = h.HostID
where H.HostName = 'esxi-host-01.mgmt.cloud' FOR XML PATH ('')}

What am I doing wrong?

Find more posts tagged with

Accepted answers

All comments

adam.beedell

Careful leaving your hostname in

What do you get? I'm expecting you'd want to REPLACE out some '<'s and '>'s outside the subquery

Also, should probably collapse this into 2 macros deep rather than 3, if the long ones evaluate in the wrong order you'd get an error and i'm not super sure how that'd work

rob.pepe

it's a dummy hostname:)

instead of a list of vm's, I get the query as a string.

so instead of

vm1
vm2
vm3...etc

I get this

${N=SQL:M=select vm.name from vim_VirtualMachines vm
left join vim_Hosts H on vm.HostID = h.HostID
where H.HostName = 'esxi-host-01.mgmt.cloud' FOR XML PATH ('')}

like it's unable to parse.

adam.beedell

Aah I understand the last few lines of the OP now.

If you run :
"
select vm.name from vim_VirtualMachines vm
left join vim_Hosts H on vm.HostID = h.HostID
where H.HostName = 'esxi-host-01.mgmt.cloud' FOR XML PATH ('')

in your env does that work or error out or produce no results?

Getting the code back means no results or failed to parse or it's a table and there's nothing in top left cell.

The query looks OK and an equivalent exists in my environment, perhaps the hostname is listed differently on that table or the VMs were all evacuated beforehand so no results.

If it's the evacuation one you want to use a CASE WHEN NULL THEN ' ' or a UNION ALL SELECT ' ' AS col1

I think you could write your SQL to avoid the nested replacement macro, i'd be tempted if the issue is seeming hard to find