Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Exporting CMS Users List 1
- Thread starter lmtb
- Start date
If you are talking about CMS Supervisor logins you can install the ODBC Drivers for your system and use MS Access to open the table that contains all the users in the system.
Depending on your system, you'll need either the Openlink or the Informix ODBC drivers. I believe that starting with R15 you have to use the Informix ODBC drivers. If you have an earlier release you should have the Openlink CD that came with your system. Or you probably can download it through your self support login with Avaya support. You can get the Informix ODBC driver here:
If you will be using the Openlink ODBC drivers this is the manual you need to set up the drivers:
You probably already have all the configuration setup on the CMS server, you just need to configure the the ODBC drivers on your PC once they are installed, starting on page 34.
If you will be using the Informix drivers then this is the manual you need:
Configuring the ODBC drivers once they are installed on your PC starts on page 24.
Once you have the ODBC client setup on your computer, open MS Access and create a new blank database.
You then need to select the data source. In MS Access 2007 you would click on the External Data tab, then select More, then ODBC database.
Select Import the source data into a new table in the current database. I wouldn't recommend linking the databases as any changes you make in the MS Access table will change the data in the CMS system.
Then select the ODBC data source you created.
Then select root.users and it will import all the user information into a table that you can then print or export or do whatever you need.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
Depending on your system, you'll need either the Openlink or the Informix ODBC drivers. I believe that starting with R15 you have to use the Informix ODBC drivers. If you have an earlier release you should have the Openlink CD that came with your system. Or you probably can download it through your self support login with Avaya support. You can get the Informix ODBC driver here:
If you will be using the Openlink ODBC drivers this is the manual you need to set up the drivers:
You probably already have all the configuration setup on the CMS server, you just need to configure the the ODBC drivers on your PC once they are installed, starting on page 34.
If you will be using the Informix drivers then this is the manual you need:
Configuring the ODBC drivers once they are installed on your PC starts on page 24.
Once you have the ODBC client setup on your computer, open MS Access and create a new blank database.
You then need to select the data source. In MS Access 2007 you would click on the External Data tab, then select More, then ODBC database.
Select Import the source data into a new table in the current database. I wouldn't recommend linking the databases as any changes you make in the MS Access table will change the data in the CMS system.
Then select the ODBC data source you created.
Then select root.users and it will import all the user information into a table that you can then print or export or do whatever you need.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
Another way you can do it isn't pretty but it also works.
Set up a non-existant printer in the CMS system and launch CMS in terminal emulation mode.
For a list of users with logins to CMS you would go to User Permissions > User Data, press Ctrl+Z to clear the form, press Enter, scroll to List All, press Enter. Then click on the commands button, or press F3 and select Print window.
Then using Windows Hyperterminal, log into the CMS system, navigate to /var/spool/lp/temp and find the printer files that are stuck in queue (because it can't print to a non-existing printer).
Export these to your computer using the command: sz *
You need to define in Hyperterminal where to send the files under the menu option Transfer > Receive File. Set the path and define the User receiving protocol as Zmodem with Crash Recovery.
You can then import the printer files into Excel.
Make sure to delete the files in the temp directory after you have them.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
Set up a non-existant printer in the CMS system and launch CMS in terminal emulation mode.
For a list of users with logins to CMS you would go to User Permissions > User Data, press Ctrl+Z to clear the form, press Enter, scroll to List All, press Enter. Then click on the commands button, or press F3 and select Print window.
Then using Windows Hyperterminal, log into the CMS system, navigate to /var/spool/lp/temp and find the printer files that are stuck in queue (because it can't print to a non-existing printer).
Export these to your computer using the command: sz *
You need to define in Hyperterminal where to send the files under the menu option Transfer > Receive File. Set the path and define the User receiving protocol as Zmodem with Crash Recovery.
You can then import the printer files into Excel.
Make sure to delete the files in the temp directory after you have them.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
- Thread starter
- #4
Thanks Stinney,
I'll give them a try. Was hoping there was just a file on the server that I could export (bit like when you do audits on users who haven't logged in for ages. Now I just need to start jumping through the internal hoops to get permission to make the changes you've suggested. Thanks again.
I'll give them a try. Was hoping there was just a file on the server that I could export (bit like when you do audits on users who haven't logged in for ages. Now I just need to start jumping through the internal hoops to get permission to make the changes you've suggested. Thanks again.
- Thread starter
- #6
User Names along with their ID? What ID are you looking for in a list of CMS Logins? Sounds like you're looking for Agent Names and IDs.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
- Thread starter
- #9
Sorry for the confusion.
I'm looking for a file that will give me the list of CMS Supervisors with both the User ID and User name.
If I explain the reason for this it might make it clearer.
A typical audit of the CMS would involve running the passwd –as > /export/home/cms command to get an export of all user ID's with details of when they last reset their password etc. From here I can decide to remove anyone who hasn't accessed the system in the last 'x' months. (If this file also contained the full name from the User Data setup with the user ID then I'd be a happy soul).
However this doesn't let me clean up everything as I occasionally find that I'm asked to reset a password for a user who has left the company, and I find that someone is continuing to use their ID.
Therefore to audit each system fully, I'm looking to get a full list of the user ID's plus the user names (it's the Second line on the Permissions form that you fill in when you set a new CMS Supervisor up), and I can use this to match up with HR spreadsheets of both current and former staff. The alternative at the moment is to look up any User that I'm querying (via their User ID), in the User Data function on CMS and get their full name that way....which is quite a labourious way of doing it.
I'm looking for a file that will give me the list of CMS Supervisors with both the User ID and User name.
If I explain the reason for this it might make it clearer.
A typical audit of the CMS would involve running the passwd –as > /export/home/cms command to get an export of all user ID's with details of when they last reset their password etc. From here I can decide to remove anyone who hasn't accessed the system in the last 'x' months. (If this file also contained the full name from the User Data setup with the user ID then I'd be a happy soul).
However this doesn't let me clean up everything as I occasionally find that I'm asked to reset a password for a user who has left the company, and I find that someone is continuing to use their ID.
Therefore to audit each system fully, I'm looking to get a full list of the user ID's plus the user names (it's the Second line on the Permissions form that you fill in when you set a new CMS Supervisor up), and I can use this to match up with HR spreadsheets of both current and former staff. The alternative at the moment is to look up any User that I'm querying (via their User ID), in the User Data function on CMS and get their full name that way....which is quite a labourious way of doing it.
-
1
- #10
The ODBC drivers and using MS Access is really going to provide you with the best results for this.
How are you getting detail about "when they last reset their password etc." from the passwd? Is the command you are showing above: passwd –as > /export/home/cms
To find out when a user last logged in, or if they have ever logged in, I've been using:
finger `sort /etc/passwd | cut -f1 -d":"` | grep -i log > /userlist.txt
But the results it provides are multiple lines like this:
Login name: jsmith
Never logged in.
Login name: msmith
Last login Thu Mar 21 11:48 on pts/15 from 192.200.13.15
Login name: asmith
Last login Tue Nov 2, 2010 on pts/34 from 192.200.13.16
Login name: bsmith
The first login has never logged in, the second last logged in 3/21 of this year, third last logged in 11/2/2010 and the last one is currently logged in so there is no date provided.
I can export that into an excel file and clean it up, but if something else would provide a cleaner output I'd love to know.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
How are you getting detail about "when they last reset their password etc." from the passwd? Is the command you are showing above: passwd –as > /export/home/cms
To find out when a user last logged in, or if they have ever logged in, I've been using:
finger `sort /etc/passwd | cut -f1 -d":"` | grep -i log > /userlist.txt
But the results it provides are multiple lines like this:
Login name: jsmith
Never logged in.
Login name: msmith
Last login Thu Mar 21 11:48 on pts/15 from 192.200.13.15
Login name: asmith
Last login Tue Nov 2, 2010 on pts/34 from 192.200.13.16
Login name: bsmith
The first login has never logged in, the second last logged in 3/21 of this year, third last logged in 11/2/2010 and the last one is currently logged in so there is no date provided.
I can export that into an excel file and clean it up, but if something else would provide a cleaner output I'd love to know.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
I was unaware of the dbaccess for Informix on the CMS system.
Did a search and found what looks like a really good explaination on how to use it that also provides a way to export data from the tables to a .csv file here:
Checked it out and it works pretty good.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
Did a search and found what looks like a really good explaination on how to use it that also provides a way to export data from the tables to a .csv file here:
Checked it out and it works pretty good.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
- Thread starter
- #13
I get a print out of all users which reads for example :
testid PS 03/18/13 0 60
sample PS 09/17/12 0 60
This gives me :
User ID
PS=Password (sometimes shows as NP, which I understand to mean the password was the deleted)
Date - which I base my rule for deleting this on.
I ignore the zero, not sure what it means.
60 = Password Aging cycle
So for the two users above, 'testid' would remain, but 'sample' would get deleted as they haven't set their password since September last year.
Used this method for about 10 years since been shown it by a Vendor.
I'll now go and investigate the options you've offered for my current problem - thanks again everyone for all your help.
testid PS 03/18/13 0 60
sample PS 09/17/12 0 60
This gives me :
User ID
PS=Password (sometimes shows as NP, which I understand to mean the password was the deleted)
Date - which I base my rule for deleting this on.
I ignore the zero, not sure what it means.
60 = Password Aging cycle
So for the two users above, 'testid' would remain, but 'sample' would get deleted as they haven't set their password since September last year.
Used this method for about 10 years since been shown it by a Vendor.
I'll now go and investigate the options you've offered for my current problem - thanks again everyone for all your help.
Excellent, so reading more about the passwd command (which I've only ever used to reset passwords):
PS = login has a password set
NL = account is a no login account
LK = login is locked (password expired or manually locked)
NP = account has no password
The 0 appears to be the minimum number of days before a password can be changed again - default of 0 days
Unfortunately, we don't have password aging setup so I can't use this to determine if we should remove the account. Guess I'll have to stick with the other method for now. But I'll definitely keep this in my records for when we setup password aging.
- Stinney
I love learning and passing on knowledge. "Because knowing is half the battle".... GI JOOOOOE!
- Status