Create a Novell Service Desk csv import file from users in Active Directory

  • 7012320
  • 14-Mar-2011
  • 29-May-2013

Environment

Novell Service Desk

Situation

This documents a method for pulling users from Active Directory for a manual import. On the rare circumstance when Novell Service Desk's integrated authentication is not being used.

Resolution

Run the following queries to create a link to the server and a login that can access it, changing the last 3 fields of the last query accordingly, to user details on your system:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
EXEC sp_addlinkedsrvlogin 'ADSI', False, 'SQL-login-name', 'Windows-login-name', 'password'

Once this is done, that user can run queries on the AD server, and the following query is one that will produce a list of the users in the format as Novell Service Desk csv file:

SELECT SUBSTRING(title,1,128) as 'Title(char 128)',
SUBSTRING(givenName,1,64) as 'First Name(char 64)',
SUBSTRING(sn,1,64) as 'Last Name(char 64)',
SUBSTRING(mail,1,128) as 'Email Address(char 128)',
SUBSTRING(company,1,128) as 'Company(char 128)',
SUBSTRING(department,1,128) as 'Department(char 128)',
NULL as 'Primary Contact Company(int 1)',
NULL as 'Primary Contact Department(int 1)',
SUBSTRING(streetAddress,1,128) as 'Address 1(char 128)',
SUBSTRING(streetAddress,129,256) as 'Address 2(char 128)',
SUBSTRING(streetAddress,257,320) as 'City(char 64)',
SUBSTRING(streetAddress,321,448) as 'State(char 128)',
SUBSTRING(postalCode,1,32) as 'Postcode/ZIP(char 32)',
NULL as 'Country(char 128)',
SUBSTRING(telephoneNumber,1,32) as 'Phone(char 32)',
SUBSTRING(mobile,1,32) as 'Mobile(char 32)',
SUBSTRING(facsimileTelephoneNumber,1,32) as 'Fax(char 32)',
SUBSTRING(pager,1,32) as 'Pager(char 32)',
SUBSTRING(sAMAccountName,1,128) as 'Username(char 128)',
NULL as 'Password(char 64)',
NULL as 'Partner''s Email Address(char 128)',
NULL as 'Custom1(char 256)',
NULL as 'Custom2(char 256)',
NULL as 'Custom3(char 256)',
NULL as 'Custom4(char 256)',
NULL as 'Custom5(char 256)',
NULL as 'Notes(CLOB)'
FROM OPENQUERY(ADSI,
'SELECT title, givenName, sn, mail, company, department, streetAddress, postalCode, telephoneNumber, mobile, facsimileTelephoneNumber, pager, sAMAccountName
FROM ''LDAP://CN=Users,DC=uk,DC=Novell Service Desk,DC=com''
WHERE objectClass=''Person''
AND memberOf=''CN=Technicians,CN=Users,DC=uk,DC=Novell Service Desk,DC=com''
')

This lists details for members of the Technicians group under the Users folder. To look for the users of another group, simply change the memberOf list at the bottom of the query accordingly. (The format of this line is based on the LDAP DN (Distinguished Name) addressing format.)

Additional Information

Importing users in to Novell Service Desk is easy with the integrated authentication in place.

However, for the rare circumstance when this is not to be used, it is possible to use SQL Server Management Studio to query the Active Directory server and obtain a list of the users required.

Note that the physical address pulled in this is taken in segments from the streetAddress field in AD. There are variations that could occur, as this may be stored in the streetAddress, postalAddress or homePostalAddress, etc, in the installation. If so, the occurrences of streetAddress need replacing in the lower query and the upper query - ie. every place it exists in the query.

Further details on what can be queried can be found by searching Google. Also, using an LDAP query browser, such as JXplorer can help with other field names that may be queried.

Since Novell Service Desk imports customers using csv, their roles will still have to be assigned manually. Their passwords will also need setting accordingly.

Formerly known as 1001570