Querying eDirectory for Fun and Profit
Novell Cool Solutions: Trench
By Greg Pott
Digg This -
Posted: 19 Jul 2002
Did you ever wish there was a way to extract information from eDirectory without having to rely on ConsoleOne? Don't get me wrong, ConsoleOne is fine for most day-to-day eDirectory-related tasks you might have, but its ability to return large amounts of useful, customized data is non-existent. For example, what if you want a list of users currently connected to the network, their full name, login time and workstation name? How about a list of all login-disabled users in the tree with the reasons for their being disabled? In short, anything contained in eDirectory can be accessed quickly and easily and for no cost.
How? The short answer to that is the Novell ODBC driver for eDirectory. I'm sure there are a lot of people out there using this driver for similar purposes but I have been really surprised by the lack of information on the subject in the Novell knowledgebase and forums. Perhaps the reason for this is that the ODBC driver is primarily supplied for ConsoleOne to use with the ZENworks reporting option and all mention of it is in relation to this function. However an ODBC driver is an ODBC driver and it can be used to extract information using any number of database or query programs.
For simplicity I will discuss data access using only the Microsoft MSQuery application which ships with MSOffice. The reasons for this are simple; it is common, easy to use, fast, and a lot of people are familiar with it. It can tend to be a bit unreliable but if the queries are okay and don't return too much data, it seems to work reasonably.
Getting hold of the ODBC.exe installation file is simple. If you have ConsoleOne installed, check in
I have ConsoleOne v.1.3.3 installed and the file is dated 01 October 2001. If it is not where it should be, you probably didn't install the reporting snapin as an option on your last ConsoleOne install. In this case you can extract the file by hand with Winzip or something similar (ODBC.exe is in file c1_rpt.zip in c1.exe install file), or reinstall ConsoleOne and check the option for the reporting snapin.
Next, install the driver. Everything is done here automatically. The install creates an entry in the User DSN ODBC data sources called NDS Reporting and it is ready to use.
Getting into eDirectory
Anyone with MSQuery experience can skip this and go sailing off in any direction they choose. But for those a little unfamiliar with MSQuery (or querying in general) I will try to be as detailed as possible to avoid disappointment. I reach a problem at this point, however, and that is the fact that you are the only one who knows what you want to get from eDirectory. At best I can give guidance here, along with details of tables and fields I have found helpful, but really it is up to you to go exploring and try different things.
There are also a few conditions I want to make known. My network is pretty simple with only about 1100 users and 350 workstations with no WAN link. In NetWare terms this is pretty small and simple and I cannot make any guarantees regarding performance and usability of MSQuery beyond my own experiences. In addition, the quality of data you get out really depends on what you (and ZENworks) have put in. If your workstations are not registering properly (or not using Workstation Manager for that matter) you will be missing out on a lot of useful information. Anyway, assuming everything is working properly here are a few examples.
1. Open MSQuery. Hopefully it is installed on your computer. If it isn't and you are running from a network install of MSOffice you will need to answer whether you want it installed, others will need to find their install CD and insert.
2. After MSQuery opens you will need to create a new query. My tip here is don't have auto query set to On (that's the exclamation mark with the two arrows around it). It is really annoying when MSQuery goes away to find reams of data you don't need. Make your queries complete before refreshing.
3. Click on file -> new and you will see a list of ODBC data sources. Down at the bottom there will be a check box with an option to "Use the Query Wizard to create/edit queries". Uncheck this. Select the NDS Reporting data source and click OK.
4. You will now see a box containing table names. This is eDirectory. Each of these tables contains data, some more than others, and all can be queried. I don't know of any documentation from Novell which tells you what is what but there are two here that are very useful. These are Workstation and UserNDS.
5. To make things quick and easy at this point, click on Close and then the SQL button, and cut and paste the following query into the box that opens.
SELECT Workstation.NDS_FullName, Workstation."WM:NAME Computer", Workstation."WM:NAME User", Workstation."WM:Last Registered Time", Workstation.zenwmMACAddress FROM Workstation Workstation ORDER BY Workstation. "WM:Last Registered Time" DESC
6. With a bit of luck this will return all your registered workstations. Included will be the full distinguished name, Workstation Manager Name, the name of the last user, the last registered date and the MAC address.
I find this one really handy as it will identify any potential unreported non-functioning workstations because the Last Registered date/time will not change for those machines.
This query also quickly finds any reimaged and renamed machines which end up leaving a defunct workstation record (just sort by MAC address and find the duplicates). In this table, the WM: (Workstation Manager) fields (not WM:INV) are the most useful and will return network addresses (IPX or IP or both), OS version, CPU etc.
7. Another example involving users is included below.
SELECT UserNDS.NDS_FullName, UserNDS."Full Name", UserNDS."Login Time", UserNDS.zenwmLoggedInWorkstation_S FROM UserNDS UserNDS WHERE (UserNDS."Network Address_Address" Is Not Null) AND (UserNDS.CN Not In ('user_template'))
This will return the full distinguished name, full name, login time and logged in workstation name of all users currently authenticated to eDirectory. You may need to filter out some users you don't want to see. I have excluded user_template as an example but any other may be excluded. If you wish you can filter by group by using the Group Membership field.
8. A final example I have found to be really useful.
SELECT UserNDS.NDS_FullName, UserNDS."Full Name", UserNDS."Login Time", UserNDS.Description FROM UserNDS UserNDS WHERE (UserNDS."Login Disabled"=1) ORDER BY UserNDS."Login Time"
Where I work, disabling a student's login is an important part of ensuring they try to do the right thing (most of the time). The problem with this is keeping track of who is out and why. For this reason I came up with the following idea. When a user has been pushing the envelope with regard to behaviour, their login is disabled and the reason recorded in the General -> Identification -> Description area in ConsoleOne. The following query presents this information along with their last login time. The description area is really handy as you can enter as many events as you like and they will come up as separate records. This is great as a persistently bad user will generate a rap sheet that is easy to see.
That's it for examples. If this has been a bit confusing my tip is to get familiar with how the examples work and the querying process and then try other things. The tables and fields mentioned are the ones I have found to be the most useful but there are many others. I have not even considered making a proper front end for any of this as the data I need at any point in time is different and MSQuery allows easy filtering and sorting of data to get what you want. Basically I just have a desktop shortcut to a directory containing the queries and run them directly. They return data in about 5 seconds and this data can then be dumped to MS Excel if necessary.
I hope this helps some people. I have found it invaluable and a time saver plus it gives me a feeling that I know what is actually going on out there (priceless).
About the Author
A B Paterson College
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com