automation import users from active directory

Post Reply
Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

automation import users from active directory

Post by Drg » Tue Aug 06, 2019 12:06 pm

Hello colleagues.
interested in the possibility of adding users from AD using any means of automation.
The essence of the problem is that you need to import about 1000 users at once and assign them to tm1 groups (integration mode 5).
Are there any thoughts on this subject?
UPD:
I’ll explain that users are located in different folders of the active directory structure and adding each search manually does a little scary ...
Last edited by Drg on Tue Aug 06, 2019 2:53 pm, edited 1 time in total.

User avatar
jim wood
Site Admin
Posts: 3748
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: automation import users from active directory

Post by jim wood » Tue Aug 06, 2019 1:44 pm

Are you using CAM?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Tue Aug 06, 2019 2:30 pm

jim wood wrote:
Tue Aug 06, 2019 1:44 pm
Are you using CAM?
Yep

User avatar
macsir
Community Contributor
Posts: 651
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: automation import users from active directory

Post by macsir » Tue Aug 06, 2019 7:52 pm

What you need to do is assign users into a proper cognos group as you are using CAM.
The way I am doing is using Powershell with ActiveDirectory module to read AD info and then using Cognos SDK with Java to manipulate users and groups. Obviously you need SDK license to do so.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
paulsimon
MVP
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: automation import users from active directory

Post by paulsimon » Tue Aug 06, 2019 8:23 pm

Hi

You can import the AD Groups from Server Explorer. If you are using CAM then the Users are automatically created and assigned to Groups according to their AD profile when they first sign in, but the Groups must be created first.

Not sure how many Groups you need to create. The standard default in TM1S.CFG only allows 20 Groups to be created without a server restart.

If for some reason you do need to create the users before their first sign in, a possible solution is to create a linked server to the AD server in SQL Server. You can then read the AD information via a TI and create the CAM User in }Clients. Create one user first to identify which ID field from CAM is used to form the CAMID.

Regards

Paul Simon

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Wed Aug 07, 2019 6:45 am

macsir, paulsimon

there is one important detail, we avoid associating users with AD or BI groups; therefore, we use Integration Mode 5 so that we have the opportunity to bind CAm users to groups created in TM1!

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Wed Aug 07, 2019 6:46 am

macsir wrote:
Tue Aug 06, 2019 7:52 pm
What you need to do is assign users into a proper cognos group as you are using CAM.
The way I am doing is using Powershell with ActiveDirectory module to read AD info and then using Cognos SDK with Java to manipulate users and groups. Obviously you need SDK license to do so.
A very interesting approach. Is there a free description of working with cognos SDK somewhere?
Or maybe you will be so kind and open the door to this wonderful world ...

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Wed Aug 07, 2019 6:51 am

paulsimon wrote:
Tue Aug 06, 2019 8:23 pm
Hi

You can import the AD Groups from Server Explorer. If you are using CAM then the Users are automatically created and assigned to Groups according to their AD profile when they first sign in, but the Groups must be created first.

Not sure how many Groups you need to create. The standard default in TM1S.CFG only allows 20 Groups to be created without a server restart.

If for some reason you do need to create the users before their first sign in, a possible solution is to create a linked server to the AD server in SQL Server. You can then read the AD information via a TI and create the CAM User in }Clients. Create one user first to identify which ID field from CAM is used to form the CAMID.

Regards

Paul Simon
Paul thanks.
Is it really possible for me to generate a CAM ID myself? :shock: I thought that it was always somewhere at the level of the BI engine. And even if I have an entire set of user properties from AD, the all-unique ID is generated according to rules that are not known to me. Do you have any information on this? :?:

tomok
MVP
Posts: 2631
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: automation import users from active directory

Post by tomok » Wed Aug 07, 2019 10:54 am

Drg wrote:
Wed Aug 07, 2019 6:45 am
macsir, paulsimon

there is one important detail, we avoid associating users with AD or BI groups; therefore, we use Integration Mode 5 so that we have the opportunity to bind CAm users to groups created in TM1!
You have already spent more time posting on this board than it would have taken you to manually add them. Granted clicking on a thousand check boxes would be somewhat tedious but I could probably do it in less than five minutes. Then you just need an Excel sheet with their group assignments and some DBS formulas.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Wed Aug 07, 2019 12:49 pm

tomok wrote:
Wed Aug 07, 2019 10:54 am
Drg wrote:
Wed Aug 07, 2019 6:45 am
macsir, paulsimon

there is one important detail, we avoid associating users with AD or BI groups; therefore, we use Integration Mode 5 so that we have the opportunity to bind CAm users to groups created in TM1!
You have already spent more time posting on this board than it would have taken you to manually add them. Granted clicking on a thousand check boxes would be somewhat tedious but I could probably do it in less than five minutes. Then you just need an Excel sheet with their group assignments and some DBS formulas.
Have a nice day.

tomok the fact is that we administer not one or two models, but there are about 5 of them and each of us has about 2500 different users (yes, we have one of the largest systems in the world according to IBM), users in AD are stored in different places of the domain ( about 50 organizations) so everyone needs to be added through a search.
Therefore, I would like to get acquainted with the experience of local gurus. rather than sit like a monkey with a mouse.
But naturally, I will follow your toxic advice if I do not find other options here or from IBM.

User avatar
jim wood
Site Admin
Posts: 3748
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: automation import users from active directory

Post by jim wood » Wed Aug 07, 2019 1:56 pm

Drg wrote:
Wed Aug 07, 2019 12:49 pm
tomok wrote:
Wed Aug 07, 2019 10:54 am
Drg wrote:
Wed Aug 07, 2019 6:45 am
macsir, paulsimon

there is one important detail, we avoid associating users with AD or BI groups; therefore, we use Integration Mode 5 so that we have the opportunity to bind CAm users to groups created in TM1!
You have already spent more time posting on this board than it would have taken you to manually add them. Granted clicking on a thousand check boxes would be somewhat tedious but I could probably do it in less than five minutes. Then you just need an Excel sheet with their group assignments and some DBS formulas.
Have a nice day.

tomok the fact is that we administer not one or two models, but there are about 5 of them and each of us has about 2500 different users (yes, we have one of the largest systems in the world according to IBM), users in AD are stored in different places of the domain ( about 50 organizations) so everyone needs to be added through a search.
Therefore, I would like to get acquainted with the experience of local gurus. rather than sit like a monkey with a mouse.
But naturally, I will follow your toxic advice if I do not find other options here or from IBM.
Sorry about that. Tomok is some what of a blunt instrument. Sometimes his forthright approach can be helpful, sometimes. I get why you're trying to do what you're trying to do. Even if you had only a small model to deal with, there's value in expanding your technical knowledge by trying something like this. After all if you do find a solution and report it here we will all benefit from it, may be even Tomok at some point.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

tomok
MVP
Posts: 2631
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: automation import users from active directory

Post by tomok » Wed Aug 07, 2019 3:45 pm

Drg wrote:
Wed Aug 07, 2019 12:49 pm
tomok the fact is that we administer not one or two models, but there are about 5 of them and each of us has about 2500 different users (yes, we have one of the largest systems in the world according to IBM), users in AD are stored in different places of the domain ( about 50 organizations) so everyone needs to be added through a search.
Therefore, I would like to get acquainted with the experience of local gurus. rather than sit like a monkey with a mouse.
But naturally, I will follow your toxic advice if I do not find other options here or from IBM.
You said 1000 users in your original post, not 2500 and now you have edited that post to say they are in 50 different organizations. Why didn't you edit it to say that too? ;)

You need to maybe not be so sensitive. I didn't insult you are say anything derogatory about you. I just simply pointed out that all the effort needed to come up with a routine to add 1000 users is likely not going to result in any net time savings versus just clicking them in the interface.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: automation import users from active directory

Post by Wim Gielis » Wed Aug 07, 2019 9:34 pm

jim wood wrote:
Wed Aug 07, 2019 1:56 pm
Even if you had only a small model to deal with, there's value in expanding your technical knowledge by trying something like this. After all if you do find a solution and report it here we will all benefit from it, may be even Tomok at some point.
I would be interested in a structural solution, as one of the models we actively support use IntegratedSecurityMode=5 too and has quite a number of users. It's not as straightforward as IntegratedSecurityMode=1 obviously.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
paulsimon
MVP
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: automation import users from active directory

Post by paulsimon » Wed Aug 07, 2019 10:59 pm

@Drg

The CAMID is definitely related to an ID in Active Directory. Check an existing TM1 user that has signed in via CAM. Look at their CAMID, and then go to Active Directory and look at their profile, and you should be able to match the CAMID number to one of the IDs there. The number in the CAMID is not just a random number. If you think about it, the next time that user signs in to TM1, CAM has to know that it is an existing user in TM1 and that is done by matching the numeric part of the CAMID to an ID in Active Directory. That then allows CAM to pull in the latest Group memberships from AD which it needs to be able to do.

You can read from Active Directory by creating a Linked Server in MS SQL Server. I did it a while ago. I can't remember the details but I did it by searching Google, so the same should work for you.

Regards

Paul SImon

User avatar
paulsimon
MVP
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: automation import users from active directory

Post by paulsimon » Wed Aug 07, 2019 11:02 pm

@Drg

I am not clear as to whether you don't use AD Groups at all, or whether you use a mix of AD and TM1 Groups. Either way it doesn't matter since the main issue in your question is about creating the users, which you can do in the manner that I described.

I don't know how you then decide which TM1 Groups they belong to. Do you have this in a separate database or file. If so just read that in via TI create the Groups and assign the users to them.

Regards

Paul SImon

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Thu Aug 08, 2019 8:33 pm

Thank you guys all!
Especially Paul SImon, you were right that CAMID is a derivative of the client’s property.
And how it is all arranged, I will now tell for inquiring minds and for people whose life has faced this situation.
So, we begin:
The structure of CAMid is as follows:
CAM (<NAMESPACEID>: <type>: <ObjectGUID> *)
https://www-01.ibm.com/support/docview. ... wg21338998
https://www-01.ibm.com/support/docview. ... wg21497035
NAMESPACEID is your name the name you specify in cognos confuguration (security - authentication)
type - this is the type of account standard it is a user, but probably it can be groups or properties from AD (I did not check it)
ObjectGUID * is a converted property of a user, if you look it directly in AD and they will be different on an already created user in Cognos.
How I came to "success":
There are several options for how you can convert this property to the view we need. It all depends on what you are processing AD (mssql, powershell, excel, etc.)
I settled on mssql this is the most direct way in terms of tm1.
To do this, I created linkedserver on my mssql server:
https://blog.skufel.net/2012/01/how-to- ... directory/
after wrote a request to get camid from objectGUID
https://www.sqlservercentral.com/forums ... to-varchar

I have not yet solved the problem of the big result from AD, but I think this can be done by examining the syntax of LDAP queries better

That's basically all I got a list of users whom I will add to the customer dimension, add attributes that interest me and bind them to groups tm1. (Macsir wrote about how to bind users to groups in bi above, but you need to dive into the sdk cognos)

Also, if you connect to AD from EXCEL, there is a conversion function from microsoft:
excel(VBA)
https://support.microsoft.com/en-us/hel ... ng-form-fo
powershell
https://social.technet.microsoft.com/Fo ... powershell

ps/
special thanks to our manager at IBM for a quick response and help, they are not always slow and picky

User avatar
jim wood
Site Admin
Posts: 3748
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: automation import users from active directory

Post by jim wood » Fri Aug 09, 2019 11:13 am

Drg wrote:
Thu Aug 08, 2019 8:33 pm
Thank you guys all!
Especially Paul SImon, you were right that CAMID is a derivative of the client’s property.
And how it is all arranged, I will now tell for inquiring minds and for people whose life has faced this situation.
So, we begin:
The structure of CAMid is as follows:
CAM (<NAMESPACEID>: <type>: <ObjectGUID> *)
https://www-01.ibm.com/support/docview. ... wg21338998
https://www-01.ibm.com/support/docview. ... wg21497035
NAMESPACEID is your name the name you specify in cognos confuguration (security - authentication)
type - this is the type of account standard it is a user, but probably it can be groups or properties from AD (I did not check it)
ObjectGUID * is a converted property of a user, if you look it directly in AD and they will be different on an already created user in Cognos.
How I came to "success":
There are several options for how you can convert this property to the view we need. It all depends on what you are processing AD (mssql, powershell, excel, etc.)
I settled on mssql this is the most direct way in terms of tm1.
To do this, I created linkedserver on my mssql server:
https://blog.skufel.net/2012/01/how-to- ... directory/
after wrote a request to get camid from objectGUID
https://www.sqlservercentral.com/forums ... to-varchar

I have not yet solved the problem of the big result from AD, but I think this can be done by examining the syntax of LDAP queries better

That's basically all I got a list of users whom I will add to the customer dimension, add attributes that interest me and bind them to groups tm1. (Macsir wrote about how to bind users to groups in bi above, but you need to dive into the sdk cognos)

Also, if you connect to AD from EXCEL, there is a conversion function from microsoft:
excel(VBA)
https://support.microsoft.com/en-us/hel ... ng-form-fo
powershell
https://social.technet.microsoft.com/Fo ... powershell

ps/
special thanks to our manager at IBM for a quick response and help, they are not always slow and picky
Excellent write thank you. I told Alan we need a like button.......
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
macsir
Community Contributor
Posts: 651
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: automation import users from active directory

Post by macsir » Sun Aug 11, 2019 10:31 pm

For anyone who is interested in Cognos SDK, here is the start point
https://www.ibm.com/developerworks/data ... ge565.html
It is not very hard actually if you have some basic Java programming knowledge.

Forgot to mention, the mapping relationship I found for an ObjectGUID in AD and CAMID in Cognos is following, not sure if it organization-specific but definitely the mapping exists.
# ObjectGUID : ABCDEFGH-IJKL-LMNO-XXXX-XXXXXXXXXXXX
# CAMID("NAMESPACE:u:GHEFCDABKLIJNOLMXXXXXXXXXXXXXXXX")
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: automation import users from active directory

Post by Drg » Mon Sep 09, 2019 8:38 am

i create some litle script maybe any one his help:
!only windows architecture!
this script connect to AD(use tm1 service user) and create csv file with you fileds and filter
this script support data transformation for any field
formated as array separated ';'(Pdlmtr)
to convert objectguid i use next construction
objectGUID | %{"{0:X}"-f$_}|%{$_ -replace" "}

Code: Select all

#PARAMS#
OutputFile ='D:\12345.csv';
#filter like a username1,username2,user*,*name*,*
SAMacc='username1,username2'; 
CSVDelimtr=';';
ActiveDirectoryDomain='mydomain.mycomp.com';



ADSI='LDAP://';
WHILE(ActiveDirectoryDomain @<> '' );
	nPos = IF( SCAN( '.' , ActiveDirectoryDomain ) > 0 , SCAN( '.' , ActiveDirectoryDomain ) , LONG(ActiveDirectoryDomain)+1 );
	ADSI = ADSI | 'DC=' | SUBST( ActiveDirectoryDomain , 1 , nPos-1 ) | ',';
	ActiveDirectoryDomain = DELET ( ActiveDirectoryDomain , 1 , nPos );

END;
ADSI=SUBST( ADSI , 1 , LONG(ADSI)-1 );
#LOGOUTPUT('INFO' , ADSI);


#filter by activedirectory fields uses 
PropertyListWithFilter ='sAMAccountName;title;department;company;objectGUID | %{"{0:X}"-f$_}|%{$_ -replace" "};mail;mobile;manager;memberof'
Pdlmtr=';';
Fdlmtr=',';
key='';
KeyFilter='';
fPos=0;
PSObjKeyValue='';
WHILE(PropertyListWithFilter @<> '' );
	nPos = IF( SCAN( Pdlmtr , PropertyListWithFilter ) > 0 , SCAN( Pdlmtr , PropertyListWithFilter ) , LONG(PropertyListWithFilter)+1 );
	KeyFilter = SUBST( PropertyListWithFilter , 1 , nPos-1 );
	fPos=IF( SCAN( Fdlmtr , KeyFilter ) > 0 ,  SCAN( Fdlmtr , KeyFilter ) , LONG(KeyFilter)+1 );
	key= SUBST( PropertyListWithFilter , 1 , fPos-1 );
	KeyFilter = DELET ( KeyFilter , 1 , fPos-1 );
	filter=IF(KeyFilter @= '' , '$f' , ''''|SUBST( KeyFilter , LONG(Fdlmtr)+1 , LONG(KeyFilter)-LONG(Fdlmtr))|'''' );
	PSObjKeyValue = PSObjKeyValue | key | '=' | filter | ';';
	PropertyListWithFilter = DELET ( PropertyListWithFilter , 1 , nPos+(LONG(Pdlmtr)-1) );
END;
SpecCharEscape=SCAN( '"' , PSObjKeyValue);
WHILE(SpecCharEscape > 0 );
   PSObjKeyValue = INSRT(  '\' ,  PSObjKeyValue , SpecCharEscape );
   tmpStr = DELET(   PSObjKeyValue, 1, SpecCharEscape+1  );
   SpecCharEscape = IF(SCAN( '"' , tmpStr )>0 , SCAN( '"' , tmpStr )+SpecCharEscape+1 , 0 );
END;
LOGOUTPUT('INFO' , PSObjKeyValue );


SCRIPT='cmd /c powershell -command "&{ $fl='''|OutputFile|''';$err=$fl+''_err'';$f=''-replace''''(”|“|"")?'''',''''$0$0'''';'';$p=[ordered]@{'|PSObjKeyValue|'};';
SCRIPT=SCRIPT|'$AD=New-Object System.DirectoryServices.DirectorySearcher( [ADSI]\"'|ADSI|'\");$U=(@('''|SAMacc|'''-split'','')-join'')(sAMAccountName='');$and=''&'';$or=''|'';$AD.Filter=\"($and(objectCategory=person)(objectCategory=user)(objectClass=user)($or(sAMAccountName=$U)))\";';
SCRIPT=SCRIPT|'$AD.PageSize = 1000;$p.keys|%{$AD.PropertiesToLoad.Add($_)}|out-null;$AD.findall()|%{$pr=$_.properties;try{ $q=($p.keys|%{$K=(&([Scriptblock]::Create( ''$pr["$_"]''+$p.Item($_) ))); \"$_=\"\"$K\"\";\"}); &([Scriptblock]::Create( \"[pscustomobject][ordered]@{$q}\")) }CATCH{ $pr.cn | ac -path \"$err\"}}|export-csv $fl -NoTypeInformation -Delimiter '''|CSVDelimtr|''' -Encoding utf8;';
SCRIPT=SCRIPT|'}"';


#LOGOUTPUT('INFO' , SCRIPT);

EXECUTECOMMAND( SCRIPT , 1);
enjoy!

Post Reply