keeleral
Enthusiast
Enthusiast

Email all users of vRA

I have a need to email all users that have access to the vRA portal, in any tenant.  My use case is when i need to take the servers off line for maintenance i would like to send a notification letting them know the times it will be down and come back up.  Does anyone know of a way to email or notify the users?

Thanks

Tags (1)
13 Replies
vMarkusK1985
Expert
Expert

Hi,

there is an Table in the SQL DB:

SELECT TOP 1000 [UserID]

      ,[UserName]

  FROM [vCAC].[dbo].[Users]

Then you should be able to find User in AD and get email Address

var users = new Array();

var usernames = new Array();

var emails = new Array();

for each (var vCAC_User in vCAC_Users) {

  var user = vCAC_User.getProperty("UserName");

  users.push( user );

  }

for (var i = 0; i <  users.length; i++){

  var username = users[i].split( "@" ).shift();

  System.log ("username: " + username);

  usernames.push( username );

  var adUsers = ActiveDirectory.search("User" , username);

  var adUser = adUsers[0];

  if (adUser == null){

  System.log ("No User Found");

  }

  else {

  var email = adUser.getAttribute( "mail" );

  if (email == ""){

  System.log ("No email Found");

  }

  else {

  System.log ("email: " + email);

  emails.push( email );

  }

  }

  }

Kind Regards,

Markus

https://mycloudrevolution.com | https://twitter.com/vMarkus_K | https://github.com/vMarkusK
0 Kudos
keeleral
Enthusiast
Enthusiast

I was hoping for something more out of the box.  Does anyone know if this feature will be added 7?

0 Kudos
vMarkusK1985
Expert
Expert

I have not Seen anyting like that native in vCAC...

But with vCO it is possible. Do you use vCO for extending vCAC?

https://mycloudrevolution.com | https://twitter.com/vMarkus_K | https://github.com/vMarkusK
0 Kudos
SeanKohler
Expert
Expert

>>Does anyone know if this feature will be added 7?

That is probably NDA.

We retain a list of all users and their email addresses (and other data) in a separate postgres table, populated via Business Group/AD Group/AD Nested Group read.

We have an ASD service blueprint that emails machine owners only... but we are working on also using the table to email one or many Business Groups.

Here is a visual...

email.jpg


aamodei01
Enthusiast
Enthusiast

Sean,

How did you actually do this? DId you create a seperate postgres table and script updates out of vRA/vCO for it?

THis workflow looks great, can you explain it a bit more?

0 Kudos
SeanKohler
Expert
Expert

Yeah... my coworker wrote this piece.

Essentially we keep a copy of machine properties in our own Postgres table.  On machine build, we add/update a row for the machine ID with name and other relevant data including machine owner.  These rows are updated with a daily maintenance refresh of the data.  (read environment and update rows where applicable)  On machine destruction, the row for an active machine is set to false. (and this is enforced on the daily maintenance refresh)  I am a big proponent of high data quality and the programmatic means to achieve it.

Here is the simple select statement that will really bring clarity...

select owner from vcac_vminfo where active=true

With this we have an array of users in UPN format.  And since our users have email addresses in AD for their user account, it is pretty easy to get a list of valid (at least as far as the data quality in AD goes)  email addresses to send an email to.

None of this is necessary.  It is just one way.  You can get a full list of users in vRA.  (all who have logged into a specific tenant).  From that list of users, you can then lookup/build email addresses based on your environment. But our *current* methodology is to only notify people with owned machines of pending maintenance.  We have a gap if a person wants to use vRA while it is in maintenance and they do not own a machine.  With our user base, we are willing to accept that risk in our notifications (for now).  Some organizations (especially those acting as a service provider) likely need a better method.

In our case, I build business groups with a 1to1 Active Directory group. We do not manage users/groups within the BusinessGroup in vRA.  We manage them in AD.  This means, I can also just do a nested user lookup in the AD group and get all valid users.  This is a more traditional model for user identification, and will likely be a better way for us to manage notifications.

Always happy to discuss theory and ways of doing things....

aamodei01
Enthusiast
Enthusiast

Awesome, thank you!

Have you ever tried to configure a SQL connection to vRO? I did this in an attempt to achieve the other solution mentioned here (and since Im not a postgres guy...yet)

but something very strange happened, as soon as I configured the SQL connection (used the jdbc URL builder workflow to get the proper URL) vRO slower to a crawl, rebooted it, couldn't do anything, got VMware support involved, long storage short, I actually did a restore of the VRO appliance and had to roll back to a DB backup before I added the connection.

Weird.. you ever try/see that issues?

0 Kudos
SeanKohler
Expert
Expert

Using the SQL Plug-in (currently 1.1.4, but there may be updates)... I have two SQL servers and three tables in inventory in my lab.  I was able to grab posgress data and copy to MS SQL via VRO workflow (SQL plugin) and then present that data in SharePoint through external data table. I didn't have the kind of impacts you experienced, but after we started writing ServiceNow (SOAP wsdl), we stopped needing to have our own CMDB presentation in the Microsoft space.  (we do keep our own records in Postgres through the same plugin)

sql.jpgsql2.jpg

Also, with dynamic types... I have played around with this (early last year): SQL Plug-in + DynamicTypes = Simple CMDB for vCAC - Part 1

It has some usefulness, but mostly became unnecessary as I didn't require Dynamic Type inventory in the vRA GUI for objects.  Basically, if you wanted to reference table rows AS inventory items in vRA... you could do so as a custom resource against dynamic types via ASD.  It doesn't have to be a "CMDB"... it could be any referenced widget in a table row.  Then you can build extensible actions on each widget.

0 Kudos
aamodei01
Enthusiast
Enthusiast

Sean, you the man!

Thsi is very helpful info. Thank you.

Yeah, the SQL connection plugin slowness i experienced is very strange indeed. I noticed your URL string was different then mine I wonder if that has something to do with it.

Also, do you use SQL cred or AD integrated creds for the MS SQL Connection?

0 Kudos
aamodei01
Enthusiast
Enthusiast

AH ha! it seems to be that strange behavoir was in fact caused by the connection string!

I have verified it again just to make sure, but it looks to be that you may not necessarily use what the jdbc generator workflow gives you

0 Kudos
SeanKohler
Expert
Expert

Cool. What was the connection string you were using?

Edit: I used SQL ID and Shared Session.  I think I had a problem with AD Auth, but I cannot remember for sure.

p.s. And this gives me the chance to respond to be one point closer to Grant in the community.  (to which he will respond to 12 other things because he is an over achiever :smileygrin:)

0 Kudos
aamodei01
Enthusiast
Enthusiast

hahah! nice!

Yes, i adjusted 2 things so I can't be 100% sure as to what fully corrected it, but I used the same format of jdbc string from your picture and switched over to sql auth, and boom, nice and quick

Now i'm trying to build the workflow, i dug out the table containing the groups from the postgres DB actually (so I didnt go the MS SQL route, but oh well) and I have to figure out how to filter the javascript array coming back to remove any duplicate values.

thanks!

0 Kudos
SeanKohler
Expert
Expert

So for an array of strings, you can just loop through the array like this...

var deduplicated = new Array();

for each (string in strings){

if (deduplicated.indexOf(string)==-1) {  // so if it isn't found in the array

deduplicated.push(string);  // put it in the array

}

}

.indexOf() doesn't work for objects... so I wrote my own function that works like this (in this case the comparison is AD objects)

here are a couple of snippets... the first shows the function call... the second shows the function

//Get nested groups to add to processing list

for each (extraGroupTarget in tempGroup.groupMembers){

  //If group hasn't been processed and isn't part of the current extraGroupTargets, add it for processing in this loop

  if (!adObjectArrayPeek(extraGroupTarget,extraGroupTargets) && !adObjectArrayPeek(extraGroupTarget,processedGroups)){

    extraGroupTargets.push(extraGroupTarget);

   }

}

4. says if we "peek" in the array for the object and it is not found AND the object is also not in another array holding already processed objects

THEN

5. put the object in an array (the while loop around this call... not shown... actually keeps going until there are no extraGroupTargets left.  This looks recursively through an AD group for all members)

// pass in the object and the array where we will look for the object

function adObjectArrayPeek(object,array)

{

for each (item in array) {

  if (object.distinguishedName == item.distinguishedName){ // validate that the objects match based on my criteria and return true as a HIT in the array

   return true;

  }

}

return false;

}

0 Kudos