Getting members into groups seems to be an early hurdle for many people. Microsoft provide the Group Populator – an obtusely round-about method involving SQL tables, Select queries, input text files and code that must be run outside of MIIS. Once I got the hang of the multivalue aspect of the SQL MA, I couldn’t figure out why anyone would use Group Populator at all!
One thing I will concede about Multivalue SQL MAs is they can be monstrously slow for large data sets. Perhaps Group Populator is quicker? Even if it is though, I find the Multivalue method so simple and flexible. Group Populator restricts you to creating groups with rules (like everyone with an office address that contains “Building A” goes into the “Building A” group). A multivalue table gives you complete freedom over who goes into what group, and it can be used for other multivalue attributes as well!
So enough waffle – lets get down to how it works.
A Multivalue SQL MA needs two tables:
- Table One lists all the objects with the minimum information required to join them to their metaverse counterpart. Probably all you will need here is an object type and an identifying attribute.
- Table Two links the objects to values, with multiple values possible for one object. It must include an Attribute Name column – but you can include different Attributes in the one table.
Take an AD/Exchange example – configuring Distribution Groups. Not only must these groups have members, but they are also restricted on who can email them. The attributes are member for the membership list and (somewhat obscurely) dlMemSubmitPerms for the restricted list.
My first table I will call ADMV_Objects. It lists all the possible Groups and Users that may be involved.
| ObjectID | ObjectType |
| jpearson | User |
| Directors | Group |
| Sales | Group |
| mdali | User |
| than | User |
| … | … |
My second table I will call ADMV_Values. It must include the ObjectIDs exactly as they are in the first table, matched to attribute values.
| ObjectID | AttributeName | Value |
| Directors | member | than |
| Directors | member | jpearson |
| Directors | dlMemSubmitPerms | Directors |
| Directors | dlMemSubmitPerms | Sales |
| Sales | member | mdali |
| Sales | dlMemSubmitPerms | Sales |
| … | … | … |
It is now just a matter of configuring my SQL MA.
On the Connect to Database page I put the Objects table in as the primary table, and the Values table in as the Multivalue table.
Then on the Configure Columns page I click the Multi-value button to open the Multi-value settings page. Here I get to tell MIIS:
- where to look for the the attribute names;
- where to look for the values – unhelpfully referred to as “String attribute column”, but values is what they mean; and
- which multivalue attributes to expect.
Finally set your Join rules so MIIS knows where to flow the attributes and you’re away!
What do the join and flow rules look like for this example?
The flow rules are just simple direct ones:
member -> member
dlMemSubmitPerms -> dlMemSubmitPerms
The join rules are again very simply based on the identifying attribute for the object. In this case I was using the group name, which was the same in the original SQL table, in AD and also in LDAP. The important thing to note here is that the identifying attribute must appear in both tables, and the column must have the same name (“ObjectID” in the example above).
HTH,
Carol
First, thanks for your posts. They’re very helpful. I’m wondering if you can answer a question for me. I’m finding that I cannot setup up a direct join on the member attribute. The mv member attribute is not available for joining. Does this have something to do with it being a reference dn type? Thoughts?
Thanks!
nevermind. i see the error in my question. thanks again for your posts.
Oh OK – I was just coming to give you an answer 🙂
In case anyone else is trying this – you join groups to groups and users to users – you would not join users to groups because they are members of it (assuming that’s what you were trying to do). As the member is a reference DN it will automatically update with the correct DN if the user’s name changes, or it gets moved to another OU. And there’s not really any other reason why you’d want to join a user to a group it was a member of.
Carol
I have a table #1 that looks like this:
userId | fieldId | data
101, 1, ABC Inc
101, 2, Software Engineer
where userId would refer to user table (#2) and the fieldId to field definition table (#3) that looks something like this:
fieldId | name
1, Company Name
2, Job Title
I already have a SQL MA updating user table (#2). I have been struggling all day how to update just the first table listed above (#1). Am I suppose to create entirely new table that would list all possible users as well as all possible fields and create second table for multi-values?
Thanks.
on how to*
supposed*
long day. 🙂
Hi there.
Not entirely sure if I’ve got the drift. Firstly I’m confused about why you’re putting the field names in a third tabe instead of using them as headings. The example you’ve put here could be easily represented as a single-valued table:
101, ABC Inc, Software Engineer
Now if you wanted Mr 101 to have three possible email addresses you would need a seperate multivalue table:
101, someone@abc.com
101, someone@gmail.com
101, someoneelse@yahoo.com
On the question about a table that lists all possible users – that’s just for Reference DN values, like group member. In that case the value is a pointer to another object so MIIS needs both objects in the connector space. If you’re just adding string values there is no need to include all objects.
It is because the app allows end users to add custom fields on the fly, hence, the purpose of the third table, storing the field names along with other attributes like maxlength, datatype, etc.
I would like to clarify that while we have a table storing custom fields (#3), we are storing a single value for each field for each user in table #1.
Is it still doable with the usage of multivalue table approach to keep table #1in sync with MV?
Thanks a bunch!
I think what you really have here is a single valued table in a slightly unusual format. So I think you will need another MA and you will have to make the anchor a combination of the user ID and the field ID.
Rich,
Thank you sooo much for this post. Everything was perfect for recreating a similar situation in my environment. With a minor difference (Oracle instead of SQL), I was still able to get most of it working with your detailed instructions, except for one minor problem that I was hoping you might be able to help with…
I have everything sync’ed into the MV, with my user objects as reference values, however, it won’t export into AD. The groups get created, permissions are set correctly, but no members in the groups, and no errors. I am exporting member (group) directly to member (group), and the user objects already exist in the MV…I even tried sync’ing the user objects, thinking this would export the membership…
I feel like I’m missing something important here (ie., rules extension, etc), and couldn’t find any more instructions on your posting for creating the AD MA…
In actuallity, this is the ONLY posting of a good Group Populator in MIIS, without using Group Populator, so thanks again for everything!…
Clifton,
Rich? Who’s Rich? My name is Carol, though I will answer to Miss MIIS if you like that sort of thing 🙂
Anyway, no members exporting. Things to check:
– do you have both user and group objects selected in the ADMA?
– can you see the group members in the connector space?
– if you export to a log file are the members in the file?
Sorry Carol, I saw someone had referred to Rich, and thought that was you. I figured out my problem, as I wasn’t importing the user objects into the MV through the same MA that I was exporting the groups through. I thought since I was doing it through another MA from AD, and the MV group references to user objects were working, that it should have exported it automatically. However, as soon as I imported/joined these users objects in on the group MA from AD, it worked like a champ. Thanks again for this post, and it was very helpful!…
I am using MIIS 2003 (ILM 2007) and I am having a problem provisioning a record to a SQL 2005 database table. The database table has about 18 attributes with 1 of them being a PK (call this attrib01), which is an autgenerated number. This specific MA (call it MA01) has all the attributes in the table defined in the attribute flow to export with the exception of the PK field (b/c it is read only in the CS meaning it is the Anchor Attribute). When I run my Full Imports/Full Synchs (all MAs) the proper MAs (in this case MA01) receive provision/export notifications. During the Export run MA01 I get an exception from my provision DLL that attrib01 is not present. I tried setting attrib01 to a generic value (knowing it is autgenerated) in the provisioning DLL and I still get an exception but it is different stating that attrib01 is read only. Bottom line is I cannot insert a new record in the db table because of attrib01.
I am now looking into an Extensible MA but have never created one before.
I would appreciate any ideas on how to get by this?
JB,
It certainly is possible to export to a table with an identity column – you just leave the value blank and let SQL populate it – but I expect there would be a problem if that column was also the key. I think when I’ve done this in the past I’ve used something else for the key that I can populate from MIIS, and then use another column for the self-seeded identity.
Sorry I’m a bit vague – haven’t done it in a while – but I see you’ve posted on the forum as well so you’re bound to get some good answers there.
Really a very good link..it solved my problem
I realize this is an old one, I was just wondering(hope you are still listening), can you do that with export meaning, syncing groups + members to tables in Oracle (or SQL)?
How would the configuration change in this case?
Yep still here. You can definitely use this for exports, and while I’ve only ever done it with SQL I gather it works just the same for Oracle.