VMware Events Alerts with Regards to Virtual Machine

I havnt tested this yet, but I have done some work in this space

I think the LA alerts want to alert on a node, because being a node/being "monitored" is the criteria for events to not be dropped, at least usually. VMware events are properties of the parent vcenter as well, so if Nodes is forced i'm not shocked as such

Nodes and VMs are parallel, and you can call VM customproperties from a node and node customproperties from a VM

I think the alert will trigger against the parent vsphere, and that's not parallel anymore really. I think what you want is a custom SWQL macro that JOINs to the VM table based on some identifying thingy in the event.

There is a section in the DB for an assumed link, I cant remember what it is at the moment but I didnt find it a reliable thing last I tried, and I'm foggy enough on it that I might be mixing it up. Custom macro should do it.

Hi Adam,

Thank you for the reply.

Can you tell me where to find the section in the DB that you mentioned?

I'm kinda new SolarWinds.

Ah, you've stumbled across a reasonably hard one. Could you put some Sanitized events in the chain of the sort you'd like to link to their owner?

Could you confirm that the custom properties are definitely against virtual machines not nodes?

So the event that I am capturing is as follows:

vSphere HA restarted virtual machine <vm_name> on host <vshpere_host> in cluster <cluster_name>

I'm using the below query to get the <vm_name> from the event message:

${SQL: SELECT SUBSTRING(replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0, charindex(' on ',replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0))}

Then I would like to join the VIM_VirtualMachines table with the VIM_VirtualMachinesCustomProperties table to get the owner emails using the <vm_name>.

To answer your question, yes the custom properties are against virtual machines.

I dont think you count as new anymore if you've already got that together!

Something like the below, I've switched to SWQL over SQL as that tends to be better long term

Because we're using names not UIDs I've added a separate match for the hostname in case there's multiple vms with the same name

${N=SWQL;M=SELECT top 1 v.CustomProperties.OwnerEmail as macro from orion.vim.virtualmachines v where v.DisplayName like SUBSTRING(replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0, charindex(' on ',replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0)) and v.Host.DisplayName like Substring('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+9, Charindex(' in cluster','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-9) UNION ALL (Select top 1 ' ' as stopsNullErrors from orion.vim.virtualmachines v)}

I've stuck that on one line because if you want to email the owner, you cant place linebreaks in a subject line and it can mess with the macro

More readable: 

SELECT top 1 v.CustomProperties.TestThree as macro 
from orion.vim.virtualmachines v
where v.DisplayName like SUBSTRING(replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0, charindex(' on ',replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0))
and v.Host.DisplayName like Substring('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+9, 
Charindex(' in cluster','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-9)
UNION ALL (Select top 1 ' ' as stopsNullErrors from orion.vim.virtualmachines v)

I dont think you count as new anymore if you've already got that together!

Something like the below, I've switched to SWQL over SQL as that tends to be better long term

Because we're using names not UIDs I've added a separate match for the hostname in case there's multiple vms with the same name

${N=SWQL;M=SELECT top 1 v.CustomProperties.OwnerEmail as macro from orion.vim.virtualmachines v where v.DisplayName like SUBSTRING(replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0, charindex(' on ',replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0)) and v.Host.DisplayName like Substring('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+9, Charindex(' in cluster','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-9) UNION ALL (Select top 1 ' ' as stopsNullErrors from orion.vim.virtualmachines v)}

I've stuck that on one line because if you want to email the owner, you cant place linebreaks in a subject line and it can mess with the macro

More readable: 

SELECT top 1 v.CustomProperties.TestThree as macro 
from orion.vim.virtualmachines v
where v.DisplayName like SUBSTRING(replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0, charindex(' on ',replace('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}','vSphere HA restarted virtual machine ',''),0))
and v.Host.DisplayName like Substring('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+9, 
Charindex(' in cluster','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-Charindex(' on host ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-9)
UNION ALL (Select top 1 ' ' as stopsNullErrors from orion.vim.virtualmachines v)

Hi Adam,

Thank you for your input so far.

Unfortunately, the "TO" does not accept queries. Only valid emails or variables.

I have tried SQL & SWQL query variables, but it still won't accept.

I'm thinking of trying to run PowerShell scripts from the alert but I'm worried about spamming.

As each VM that is HA rebooted will trigger the event and alert, but it does not seem like I can pass the VM name (or event message) to the script.

Nah To: accepts queries, I use that in prod. You do have a problem though where linebreaks arent available, so if you copypaste a multiline function it tends to immediately break

You're right, it does accept queries. My apologies.

Although it appears that it does not accept string functions (replace, substring...).

When I tried add the below query, it accepts.

${N=SWQL;M=SELECT TOP 1 Owner_Email FROM SolarWindsOrionDb.VIM_VirtualMachinesCustomProperties WHERE Name LIKE ${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}}

However, when I add a string function. it does not accept it.

Is there a way to pass such functions in the query?

I think it does but somethings erroring for you, in your snippet the olm macro isn't in quotes so I'm surprised it works, what are you trying to make occur ultimately?

Apologies for the late reply, I was on leave.

I scratched the alert, and created a PowerShell script that runs when the event is triggered.

The script queries the database for the needed information, and automatically sends emails.

Thank you adam.beedell for your support & suggestions.