How do I get the vcenter name in alert emails

Hey ya'll,  I am using alerts triggered by VMware Triggered Alarm to find hardware issues on our VMware Hosts.  I have the alert working but would LOVE to be able to insert the variable for the vcenter name into the email.

 

Unfortunately the only place I can find the vcenter name is in the Orion.VIM.VirtualDisks table.

 

Is there a way to Join enough tables so that I can get the vcenter name in the email alerts.

Parents Reply Children
  • ${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.

    SELECT E0.Host.DataCenter.VCenter.DisplayName FROM Cortex.Orion.Virtualization.TriggeredAlarmState E0
    But I added it to the Insert Variable
    But those just come back blank in the emails.
  • Hi Rachel,

    Taking a quick look again in downtime ( 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:

    SELECT TOP 1 CASE WHEN e0.host.Cluster.DataCenter.VCenter.DisplayName IS NULL THEN 'No Vcenter Found' else e0.host.Cluster.DataCenter.VCenter.DisplayName end as output FROM Cortex.Orion.Virtualization.TriggeredAlarmState AS E0 --where e0.Id = 14101159

    Note the ID commented out for testing
    Now to make that live i'd have to find some macro or two that produces a relevant ID:



    That looks like a pain, no ID macro, annoying (possibly editing a macro as above resolves that)
    Name and Timestamp might work though.


    Putting this together, this seems to work
    ${N=SWQL;M=SELECT
    TOP 1
    CASE
    WHEN e0.host.Cluster.DataCenter.VCenter.DisplayName IS NULL THEN 'No Vcenter Found'
    else e0.host.Cluster.DataCenter.VCenter.DisplayName
    end as output
    FROM Cortex.Orion.Virtualization.TriggeredAlarmState AS E0
    where e0.DisplayName like '${N=SwisEntity;M=DisplayName}'
    order by e0.Timestamp desc}


    Note here i'm not matching the timestamp, which is probably not ideal, and maybe you'd get something weird in the event of quick flaps, but i'm sure someone can continue work on it if need be.
  • 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! Heart

  • 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   - 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}}

     

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

  •  

    SELECT top 1 d.Clusters.DataCenter.VCenter.DisplayName from orion.vim.Datastores d where d.DataStoreID = {put a datastore ID macro here}