Updating Users login

Hi,

I use a DXL script that I found at the support link for IBM DOORS to pull a list of  the users that I have in my database. The DXL that I use is:

Then I load this list into an excel sheet and I ran an SQL query on the database for all the users in the company to return for me the SSO for each of those users. for my current DOORS I am using the NT ID as the login and I want to move to the SSO login. I can do it manually by updating each users "Username" from the General tap in the user's properties. but I was wondering if I can take back that excel sheet that I got from my users DB along with that extra column of the users SSO next to each users NT ID and replace that NT ID with an SSO using a DXL script.

SO I basically need a DXL script that can update the usersname that is currently being used as the login with the SSO where I do have a list of each of my DOORS users SSO.

Any thought or idea is highly appreciated.

Regards, 

• User u

  int i = 1

  string ErrMess = ""

   

  print "List of users - emails:\n"

   

  for u in userList do{

  string uname = u.name

  string e = u.email

  print i " " uname " - " e "\n"

  i = i + 1

  }

•  

Re: Updating Users login
Costas..Aravidis - Sat May 21 00:46:07 EDT 2016

Yes you can.

As you have the information in Excel then create a script that reads the excel file and go through each user using the find command and replace the name of the user with the SSO.

You can do even more if you want to you can change the database that the user does not have to sign in to doors. You can either use system user names or you can use keys. 

SSO is not very meaningful so if you change the name for SSO it will be difficult in the future to identify the person in the history of changes. For example when you browse through the hostory of the object you will see a number instead of a name  and your users will not have an easy way correlating those 2. And especially after people leave the company and the SSO is removed.

But whatever you do test it on a test database beforehand. Also don't forget the Administrator user in doors.

Re: Updating Users login
Qsalameh - Mon May 23 08:52:47 EDT 2016

Thank you so much Costas for your respond. I appreciate your input regarding to not using the SSO, but I have to stick with the company's policy. Do you mind tell how to write that DXL script to run the find command for each column and replace the NT ID with the SSO. I can replace the argument to match the ones I have in my DB but unfortunately i am not a DXL expert. if you can do that I really appreciate it if not I still appreciate your answer.

Thansk

Re: Updating Users login
Costas..Aravidis - Mon May 23 10:15:25 EDT 2016

Try something like this but try it in a test database. 

Skip       userSk = create
User       u 
string     s = ""
int        i = 0

for u in userList do {
                s = u.name
                put (userSk, i++, s)
}

for s in userList do {
                u = find(s)
                setUser(u, name, <the name that you want>)
                saveDirectory
                loadDirectory
}

Remember you might create problems with the logon of the users using this kind of scripts. You need to be very familiar with DXL. Take a backup also.

Re: Updating Users login
Qsalameh - Mon May 23 11:01:31 EDT 2016

Is this code based on not using the excel sheet that i have with the SS? is it just creating the list from the users list? if so , how would I replace the username with the SSO if we have no info regarding to the SSO?

Re: Updating Users login
Costas..Aravidis - Mon May 23 12:35:02 EDT 2016

The second part is the one that updates the name of the user. You need to set the SSO to the name of the user. So I guess you need to load the excel file in a skip list an then search through the user name with the find command and then update with the SSO.

The main idea is there on how to update the user. You just need to change the way of passing the information.

Re: Updating Users login
Qsalameh - Tue May 24 09:55:54 EDT 2016

Thank you so much for all the help, I really appreciated. But I am honestly still still stuck not able to figure it out. I have a QA DB that I am testing on. I will be honest with you. I am not that expert when it comes to programming. I am learning it every day, and so far I am trying to get the code to work on user which is my account. I am trying to setup a code that will convert my login NT ID to be my SSO. Assume that I have an excel sheet that has a column that shows my NT ID and that column is " NT ID" and another column has the SSO. and that excel file called IDM Users. how do I call in this excel file into DXL and run the find command then the setuser function.?

Re: Updating Users login
Costas..Aravidis - Wed May 25 02:09:21 EDT 2016

You need to use OLE automation to read the excel file (create an excel object, open the excel file, read the rows from the spreadsheet etc.). Or save the excel file as a csv, read it with a stream in DXL and split the rows into NT ID and SSO. 

In both cases put the NT ID as a key of the skip list and the SSO as an element and then go through the skip list with a for loop.

Then as I'm guessing the NT ID is the doors user name, inside of your skip list for loop is to get the the key of the skip list and search for the user. Then replace it with the SSO and save the directory with the users.

For example

for sso in userSkipList do {
    ntid = (string key(userSkipList))
    u = find(ntid)
    setUser(u, name, sso)
    saveDirectory
    loadDirectory
}

You need to put some error detection on your code also so it does not crash when the user name cannot be found etc.

This is one way of doing it. You can also read the excel file row by row and update the users without skip list, or do the same with csv etc. You need to decide on how you want to do it and write the code that is going to read the file.

Re: Updating Users login
Qsalameh - Wed May 25 13:01:59 EDT 2016

I am testing this using an excel sheet that I created and saved its as a CSV file. That excel sheet has two columns. One is  named in the header as " NTID" and the second one is named in the header as " UserSSO" . That excel sheets has two users from my DB. I am trying to use this code to see if it will do it but I am getting Errors, I am sorry if my code looked weird , I am still trying and learning this  :-)

string csvFile = "C:\\Test\\DoorsUsers.csv"


Stream input = read csvFile

for UserSSO in input do {
    NTID = (string key(input))
    u = find(NTID)
    setUser(u, name, UserSSO)
    saveDirectory
    loadDirectory
}

Please let me know what modification needs to be done to this code to work for me on two NT ID at least.

Thank you so much!

Re: Updating Users login
Costas..Aravidis - Thu May 26 01:59:03 EDT 2016

string csvFile = "C:\\Test\\DoorsUsers.csv"


Stream input = read csvFile

for UserSSO in input do {
    NTID = (string key(input))
    u = find(NTID)
    setUser(u, name, UserSSO)
    saveDirectory
    loadDirectory
}

You cannot use a for loop on a stream. You can read the file line by line.

You need to read each line then use regular expressions to break the line into the information needed.

Your code is really wrong a skip and a stream is not the same. 

Please use the help manual to learn DXL and see how the streams and the skips and loops work. It is similar to C.

Re: Updating Users login
Qsalameh - Tue May 31 10:43:14 EDT 2016

Hi Constas,

I wanted to start of by saying thank you so much for all the help here. I really been lately going through the Manual for DXL to understand how to write that code and unfortuantly I was not able to figure it out. Programming is really my weakness , I never was able to understand or to be good at it, sorry to say that but I just wanted to to share this with you. I am still struggling with that code. I understand you might be busy or you wont be able to help beyond what you offered which i really appreciate so much. But if you could that would be great and highly appreciated. 

Here is my scenario,:

I have an excel file that has two columns. One which is the NTID and the second one is SSO for that NT ID. Assume my excel file name is  " UserSSO"

I wanted a code that will go an look up each user in my DOORS DB with their "Username" which is in my system is the NTID. and once it find a match of it in my Excel file "UserSSO" ,I wanted to replace that username which is the NT ID with the SSO from that excel file. 

Thank you so much

Re: Updating Users login
Costas..Aravidis - Tue May 31 11:40:36 EDT 2016

Basic idea as I said read the file and then use the data to update the users by ether using a skip list (code directly below) 

int                  i = 0
Regexp        csv = regexp2("(.*),(.*)")
string               readline = "", sso = ""
Stream               file = read "UserSSO.csv"
Skip          sk = create
User          u

while(!end( file)) {
       file >> readline
       put(sk, i++, readline)
}
close file

for sso in sk do {
       if(csv sso) {
              u = find(sso[match 1])
              setUser(u, name, sso[match 2]
              saveDirectory
              loadDirectory
       }
}

delete sk
delete csv

OR doing it from the file directly

int                  i = 0
Regexp        csv = regexp2("(.*),(.*)")
string               readline = "", sso = ""
Stream               file = read "UserSSO.csv"
User          u

while(!end( file)) {
       file >> readline
       if(csv readline) {
              u = find(readline[match 1])
              u.name = readline[match 2]
              saveDirectory
              loadDirectory
       }
}
close file

delete csv

If you are not familiar with dxl do not try it as if something goes wrong there is no chance to fix it. The script updates the database users. In the dxl help manual all this information exists so if you read it you could have wrote the script. You need to save the excel as a csv as this is the easiest way instead of creating all the functions to read an excel file directly, which is also possible (i do not have the time for that and is up to you to learn, all the information is in the dxl help).

You run the script at your own risk as any corruption on the database cannot be guaranteed that is ruled out (and the code has not been tested). You probably need to put some code to check for failures etc. the information again how each function work is in the dxl help manual.

Re: Updating Users login
Qsalameh - Tue May 31 13:46:32 EDT 2016

Thank you so much for your help. The second code worked as I wanted. I cant thank you enough.

Appreciate it!

Re: Updating Users login
Qsalameh - Tue Jun 21 16:35:57 EDT 2016

Hi Costas,

I have a question please and I hope that you can help me with it. I have csv file with a list of username in DOORS that I want to disable those accounts. Can you please tell me what code I am looking for to disable a list of username that i have.

I thought about this code:

int                  i = 0

string               usernames = ""
Stream               file = read "C:\\Test\\UserSSO.csv"  // This will be the soruce of our CSV file for the users list with their Usernames.

Skip sk=create


for usernames in sk do {
setUser (usernames, disabled, true)

              
                          
                          saveDirectory
                          loadDirectory
       }

close file
delete csv

I also found this code online to disable specific users but I wasnt able to convert it to read from a CSV file.

string ErrMess = ""
User u = find("Test User") 
loadUserRecord(u) 
bool Disabled = u.disabled
if (!Disabled) 
{
    ErrMess = setUser(u, disabled, true)
    if (ErrMess > "") print ErrMess "\n"
} 
ErrMess = saveUserRecord (u) 
if (ErrMess > "") print ErrMess "\n"
ErrMess = saveDirectory() 
if (ErrMess > "") print ErrMess "\n"

Any help is highly appreciated.

Regards,

Qasem

Re: Updating Users login
Costas..Aravidis - Tue Jun 21 22:24:27 EDT 2016

int                  i = 0

string               usernames = ""
Stream               file = read "C:\\Test\\UserSSO.csv"  // This will be the soruce of our CSV file for the users list with their Usernames.

Skip sk=create


for usernames in sk do {
setUser (usernames, disabled, true)

              
                          
                          saveDirectory
                          loadDirectory
       }

close file
delete csv

string ErrMess = ""
User u = find("Test User") 
loadUserRecord(u) 
bool Disabled = u.disabled
if (!Disabled) 
{
    ErrMess = setUser(u, disabled, true)
    if (ErrMess > "") print ErrMess "\n"
} 
ErrMess = saveUserRecord (u) 
if (ErrMess > "") print ErrMess "\n"
ErrMess = saveDirectory() 
if (ErrMess > "") print ErrMess "\n"

You know in my previous reply, in the loop that sets the user instead of u.name use either the set command or the u.disabled = true. Your csv file needs to have 2 columns or you need to change the regular expression. I'm on holiday so I'm not close to a pic.