Maintaining your LDAPSYNC CRON task with SQL update statements.

If you have ever tried to edit the xml sections of the LDAPSYNC CRON task in Maximo you will realize there should be a better way. There are couple different ways of addressing this issue, but we only discuss one here. That is using SQL update statements to maintain the CRONTASK in Maximo.

The primary concept to understand is that two of the CRONTASK parameters are long pieces of XML code and the rest are more typical of what you might expect in a parameter you are trying to maintain. The table of interest is called: CRONTASKPARAM and the following two parameters contain the XML of interest, GROUPMAPPING and USERMAPPING. Below is an example of the update for the GROUPMAPPING, followed by the USERMAPPING. Note the entire XML code is contained in a set of single quotes, and the carriage return line feeds are ok, they do not bother it. Then of course the where clause at the end must uniquely identify which LDAPSYNC CRONTASK you are updating. The values for the where clause can be found in Maximo when viewing the CRONTASK.

update crontaskparam set value =

'<?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE ldapsync SYSTEM "ldapgroup.dtd">

 <ldapsync>

  <group>

   <basedn>dc=Chicago,dc=com</basedn>

   <filter>(&amp;(objectCategory=Group)(objectClass=group)(sAMAccountName=AD-MAX-D1))</filter>

   <scope>subtree</scope>

   <attributes>

    <attribute>sAMAccountName</attribute>

    <attribute>description</attribute>

    <attribute>memberOf</attribute>

   </attributes>

   <datamap>

    <table name="MAXGROUP">

     <keycolumn name="GROUPNAME" type="UPPER">sAMAccountName</keycolumn>

     <column name="AUTHALLGLS" type="YORN">{0}</column>

     <column name="AUTHALLSITES" type="YORN">{0}</column>

     <column name="AUTHALLSTOREROOMS" type="YORN">{0}</column>

     <column name="AUTHLABORALL" type="YORN">{0}</column>

     <column name="AUTHLABORCREW" type="YORN">{0}</column>

     <column name="AUTHLABORSELF" type="YORN">{0}</column>

     <column name="AUTHLABORSUPER" type="YORN">{0}</column>

     <column name="AUTHPERSONGROUP" type="YORN">{0}</column>

     <column name="DESCRIPTION" type="ALN">{Maximo Dev 1 Security Group}</column>

     <column name="HASLD" type="YORN">{0}</column>

     <column name="INDEPENDENT" type="YORN">{0}</column>

     <column name="LANGCODE" type="UPPER">{EN}</column>

     <column name="MAXGROUPID" type="INTEGER">{:uniqueid}</column>

    </table>

   </datamap>

   <memberdatamap>

    <membertable name="GROUPUSER">

     <keycolumn name="GROUPNAME" type="UPPER">sAMAccountName</keycolumn>

     <membercolumn name="USERID" type="UPPER">

      <member>member</member>

      <memberuser>sAMAccountName</memberuser>

      <membergroup>sAMAccountName</membergroup>

     </membercolumn>

     <column name="GROUPUSERID" type="INTEGER">{:uniqueid}</column>

    </membertable>

   </memberdatamap>

  </group>

 </ldapsync>'

where parameter = 'GroupMapping' and crontaskname = 'LDAPSYNC' and instancename = 'LDAPSYNC01';

Then the second XML update statement might look like what’s below.

update crontaskparam set value =

'<?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE ldapsync SYSTEM "ldapuser.dtd">

 <ldapsync>

  <user>

   <basedn>dc=Chicago,dc=com</basedn>

   <filter>(&amp;(objectCategory=person)(objectClass=user)(memberof=CN= AD-MAX-D1,OU=OrG-Groups,OU=DataCenter,OU=Chicago,DC=Com))</filter>

   <scope>subtree</scope>

   <attributes>

    <attribute>sAMAccountName</attribute>

    <attribute>givenName</attribute>

    <attribute>memberOf</attribute>

   </attributes>

   <datamap>

    <table name="MAXUSER">

     <keycolumn name="USERID" type="UPPER">sAMAccountName</keycolumn>

     <column name="DEFSITE" type="UPPER">{CHG-IT}</column>

     <column name="LOGINID" type="ALN">sAMAccountName</column>

     <column name="PERSONID" type="UPPER">sAMAccountName</column>

     <column name="FAILEDLOGINS" type="INTEGER">{0}</column>

     <column name="FORCEEXPIRATION" type="YORN">{1}</column>

     <column name="MAXUSERID" type="INTEGER">{:uniqueid}</column>

     <column name="PASSWORD" type="CRYPTO">{0}</column>

     <column name="QUERYWITHSITE" type="YORN">{1}</column>

     <column name="STATUS" type="UPPER">{ACTIVE}</column>

     <column name="SYSUSER" type="YORN">{0}</column>

     <column name="TYPE" type="UPPER">{TYPE 1}</column>

     <column name="SCREENREADER" type="INTEGER">{0}</column>

     <column name="INACTIVESITES" type="YORN">{0}</column>

     <column name="DEFSITE" type="ALN">{CHG-IT}</column>

    </table>

    <table name="PERSON">

     <keycolumn name="PERSONID" type="UPPER">sAMAccountName</keycolumn>

     <column name="ACCEPTINGWFMAIL" type="YORN">{0}</column>

     <column name="HASLD" type="YORN">{0}</column>

     <column name="LANGCODE" type="UPPER">{EN}</column>

     <column name="LOCTOSERVREQ" type="YORN">{1}</column>

     <column name="PERSONUID" type="INTEGER">{:uniqueid}</column>

     <column name="STATUS" type="UPPER">{ACTIVE}</column>

     <column name="STATUSDATE" type="ALN">{:sysdate}</column>

     <column name="TRANSEMAILELECTION" type="ALN">{NEVER}</column>

    </table>

   </datamap>

  </user>

 </ldapsync>'

where parameter = 'UserMapping' and crontaskname = 'LDAPSYNC' and instancename = 'LDAPSYNC01';

—

The rest of the parameters are reasonable in length and might look like the following.

Note in this example the port is 636 and 3269 with SSLEnabled set to “TRUE” so this is an example of a secure connection to active directory. The necessary certificates must be in place in WebSphere for this to work.

Another item of interest is the password parameter is set to <CRYPTO> which tells us that application security is enabled for Maximo and the passwords are no longer stored locally, instead the active directory password will be used.

The most interesting parameter is the “cryptovalue”. You will have to enter the principle name user password on the Maximo LDAPSYNC CRONTASK parameter screen, and save it, in order to get the cryptovalue saved to the database. Then you can pull it out with a tool like TOAD and paste into your SQL update statement. The rest of the update statements can be composed in a text editor and then run to update the LDAPSYNC CRONTASK parameters.

If you place all the SQL in one text file and add a commit statement at the end you will have a stored set of code that you can execute at your convenience against the database to update the CRONTASK. The only step left is to then go into Maximo and reload the CRONTASK in order for it to take effect. This is handy in a couple ways. If you have different active directory security groups for development versus production, then you can just copy this file and update for the other environment and change the name of the text file to reflect the new environment. It’s also very handy for testing new configurations when you want to add or delete columns that are being brought down into Maximo. It also represents documentation for what has been configured.

Be advised there may be typos in the above code as it was sanitized so as not to reflect any given environment.