TM1 Forum

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

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.

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

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!

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.

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.

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.

jim wood wrote: ↑Wed Aug 07, 2019 1:56 pmEven 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.

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

objectGUID | %{"{0:X}"-f$_}|%{$_ -replace" "}

#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);