How do I get the vcenter name in alert emails

Hello! 

I'd love to take a crack at this! Do you mind sharing your alert Trigger Condition page, if possible? That will let me know what you're already working with and what conditions you're looking to alert on. Feel free to PM me on thwack if you don't feel comfortable posting on this post.

I did some initial testing in my lab with a dummy alert I set up. Please add this to your e-mail message body:

${N=SWQL;M=SELECT VCenterName
FROM Orion.VIM.VirtualDisks
WHERE VirtualMachineID = ${N=SwisEntity;M=VirtualMachine.VirtualMachineID}}

 Yeah that should work. I did a quick test using a mock up with that alert type and scope. No goodnik. I tried a couple of variations - also no good. This is a weird one. Already bumped the feature request. 

Hi Rachel,

Came to this thread thinking "I know this", and then saw that alert condition and thought "when did that go in?!"

in general the macros section kinda work by calling a value with reference to whatever table your alert condition starts on (home). So if someone's alert starts in the hardware table, their solution wont work for you, same for the hosts table or the vcenter table, or whatever else.

No idea you could start in triggered alarms now!

I'll give it a test when I can, but in general you can either:

1) use the macros section, select by category, and look through tables that seem related to what you're trying to achieve

2) build your own in the format of a SWQL query a bit like

SELECT r.thing
FROM relatedTable r

or 

SELECT h.detailTable.thing
FROM homeTable h

That produces the sort of data you care about, then add a where condition like

WHERE r.ID = '${ID_MacroFromTheSelector}'

So that it filters down to the thing you care about

(then you might also need to add a block for a UNION or CASE or subquery such that it always produces output and doesnt spit back the SWQL)

Rachel.Davidson

looking at what i guess might be the table that works on there's a structure like this

Hardware alarms would be on the host, so i'd try something like

Hey Adam,  I did try your code but it only brings the IP address up.  Are you actually getting the name or just the IP?

adam.beedell Hey Adam - I hadn't looked at the cortex entities. That clued me in. I also agree that the vm triggered alert condition is a new entity also. 

I was able to get the following to work, I added both the name and displayname. The name returned only the IP address for me also. The displayname gave the actual name. The where condition is stripped down as I wanted enough data to work with. 

marcrobinson Looks good! So nice when you throw something out at the end of a shift, come back and it's sorted

Rachel.Davidson Marc's got the right datapoint below, displayname rather than name

For production purposes i'd reccomend changing the where condition to stick an ID macro in, but then it's pretty much done

and then maybe cheryl1 could make it standard maybe somehow

I wish! Best way to make this a standard in the product is the feature request. That said, I have added this to my collection of "great ideas from Thwacksters" to share with future customers with the same need.

Rachel.Davidson I'd love to see an example of the final output email (appropriately redacted screenshot of course) to tie a nice bow on the whole process. 

${N=SwisEntity;M=Host.HostID}${N=SWQL;M=SELECT E0.Host.DataCenter.VCenter.name

FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0

WHERE ${N=SwisEntity;M=Host.HostID}}

Hate to say it didn't work but..  it didn't work.  :-)

Here is a picture where I configured the message.

Then this is what I get in the email.

I know we are getting closer!!!

${N=SWQL;M=SELECT E0.Host.DataCenter.VCenter.displayname

FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0

WHERE E0.relatedhost =  ${N=SwisEntity;M=Host.HostID}}

Bad multiple things - edited the query above. And multitasking is bad.... Just say no to it. 

${N=SWQL;M=SELECT E0.Host.DataCenter.VCenter.displayname

FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0

WHERE E0.relatedhost =  ${N=SwisEntity;M=Host.HostID}}

Bad multiple things - edited the query above. And multitasking is bad.... Just say no to it. 

Sorry that still didn't work.  I even used this query in SWQL studio and it does work.

Hi Rachel,

Taking a quick look again in downtime (cheryl1 it takes an age to populate the variables list, i'm sure there's something poorly optimized happening there)

These should work OOTB, but doesnt look like they do to me.:

${N=SwisEntity;M=DataCenter.VCenter.Name} ${N=SwisEntity;M=DataCenter.VCenter.Description}

That said it's all about the object you pass in and it's relationship to the rest of the data. These OOTB macros look like they're for an entity one under a datacenter, so probably a cluster alarm. Might be that a small edit would resolve, say: 

${N=SwisEntity;M=Cluster.DataCenter.VCenter.Name} ${N=SwisEntity;M=Cluster.DataCenter.VCenter.Description}

or another rank for host maybe. Point being that I think the search through the macros bit might have stopped a bit early looking at the screenshots above, you'll find better options setting "show variables for" to the object you're working with and "group by" to category.

Anyway in the Custom SWQL path, worth noting that between Marc and your examples there may be a missed bit of context:

When me and Marc are talking about this bit  ${N=SwisEntity;M=Host.HostID} that's a macro in a macro, where the point of that is to select the one relevant bit of information from the table containing the thing you care about. So often if you're building a custom SWQL or SQL macro you need to put an ID macro in for production, and test in SWQL studio using an actual ID (because the macro wont resolve there, having not got an object going in etc)

Somethng like this works in SWQL (which is the same as before, but i've added a case statement so it fails more gracefully:

Now to make that live i'd have to find some macro or two that produces a relevant ID:

I got this from marcrobinson and it actually works!!!

${N=SWQL;M=SELECT E0.Host.DataCenter.VCenter.displayname

FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0

WHERE E0.relatedhost = ${N=SwisEntity;M=Host.HostID}}

I really appreciate all ya'lls help!!!

Whoo hoo! I'm also going to point a few people I know in PM and etc to this thread. Some great insight. Not sure any of them will actually respond, might just lurk, but this is a great use case

I'm shamelessly snagging the SWQL too in case another customer can use it! 

Thanks everyone! I love it! 

Adam, I like you explanations, as I omitted this from my answers. I agree that the Top1 is ugly, but can work. I was thinking about your isnull too. This might also work, instead of the case function. I tend to forget that hosts can be deployed without a vcenter so I didn't think about trapping that condition with something user friendly.

This comment is a general one to cheryl1  - you have to link through four navigation properties/entities to get this bit of information that should be more readily available. If you could pass that along also?

${N=SWQL;M=SELECT ISNULL(E0.Host.DataCenter.VCenter.displayname,'No Vcenter') AS [Vcenter Name] FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0 WHERE E0.relatedhost = ${N=SwisEntity;M=Host.HostID}}

Will do! 

I have a different alert I am trying to get the vcenter from.  This one is pulling from the datastore table.  I tried this and it does not seem to work. 

${N=SWQL;M=SELECT E0.Hosts.DataCenter.VCenter.displayname
FROM Orion.VIM.Datastores E0
WHERE E0.Hosts.HostID = ${N=SwisEntity;M=Host.HostID}}

Taking a quick peek and hoping that the slight shift in entity for the alert (changing from virtual host to virtual datastore) is not the cause. This can cause some interesting and subtle changes in formatting the swql variable to retrieve the value. SAM application and component alerts are a great example, as you slightly change the swql variables for a node caption in the alert actions. 

What is the error message when you simulate the action in the web console? Does it spit out the query with the HostId resolved in your Where statement, or is just the query as you pasted above? If it is the latter, then it may be what I was thinking about. 

Rachel.Davidson 

 adam.beedell I was looking at the following and noticed that multiples could be returned. Top 1 eliminates that.

SELECT TOP 1  H.DataCenter.VCenter.displayname 

FROM Orion.VIM.HOSTS H 

WHERE H.DataStores.DataStoreID = ${N=SwisEntity;M=DataStoreID}

The other entity that looks good based on the earlier thread are those cortex entities.