Smartsite Documentatie > Install & Config > Security > ADSI > ADSI Example Implementation (SXML)

ADSI Example Implementation (SXML)

Release 1.1 - ...

Example

This example will show and insert data from the configured active directory into the Smartsite Users table using the configured ADSI connectionstring. It's very simple to implement. Just add the items to your site using the correct contenttypes and codes, copy and paste the appropriate titles and logic to the corresponding fields and execute the item.

The example consists of 2 content items of the contenttype "WebPage" (Overview Active Directory Users & Details Active Directory Users) and 1 content item of the contenttype "Application Page" (insert/update) and one manager action using the application page.

  1. Overview Active Directory Users
  2. Details Active Directory Users
  3. Manager Action: Insert/Update Users from Active Directory to Smartsite Users table
Please make sure that your site configuration is configured correctly as described in this article.

Overview Active Directory Users

This page retrieves all users from the configured active directory.

Create the following item:
Title: Overview Active Directory Users
Contenttype: WebPage
Code: OVERVIEWADSUSERS
Logic:

Smartsite SXML CopyCode image Copy Code 
<!-- The object is called userAccountControl and it is assigned a value as per below:
512 - Enable Account
514 - Disable account
544 - Account Enabled - Require user to change password at next logon 
66048 - Password never expires
66050 - Disabled
262656 - Smart Card Logon Required

More information about filtering within the ADSI SQL Query: http://msdn.microsoft.com/en-us/library/aa746494(VS.85).aspx
-->

<style type="text/css" >
 TABLE.tech {border: 1px solid #CCCCCC; border-collapse: collapse; width: 100%}
 TABLE.tech TD {background-color: #EEEEEE; padding-left: 5px; padding-right: 5px; }
 TABLE.tech TH {text-align:left; padding-left: 5px; padding-right: 5px; background-image: url(/scf/gfx/box/boxctop_grijs.png); color: white }
 TABLE.tech TD.bggreen { background-color: #CCFFCC !important; }
 TABLE.tech TD.techr { text-align:right; }
 TABLE.tech tr {border-bottom: 1px dotted black; padding-top:2px}
 TABLE.tech tr.nav th {padding: 20px; text-align: center}
 TABLE.tech td {border: 1px solid #FFFFFF}
 TABLE.tech th.legend {text-align: right; background-color: #c0c0c0}
</style>

<se:itemdata field="body" />

<style type="text/css" >
 .dbinfo_box {width: 400px; }
 .dbinfo_box .sml_clientbox { padding: 0 }
</style>

<div class="sml_clientbox panel_nobox panel_box dbinfo_box">
<h2 class="caption">Overview ADS Users Smartsite.lan</h2>
<se:sqlquery connection="MyADSI" save="adsi" resulttype="datatable">
 <se:parameters>
  <se:parameter name="sql">
   SELECT cn,userPrincipalName,sAMAccountName
   FROM 'LDAP://CN=Users,DC=SMARTSITE,DC=LAN' 
   WHERE userPrincipalName='*'
   AND userAccountControl = 66048 
   AND objectCategory='person'
   AND objectClass='user' 
   ORDER BY CN 
  </se:parameter>
 </se:parameters>
</se:sqlquery>

<se:format inputdata="adsi" whitespace="simpleformat">
 <se:parameters>
  <se:parameter name="format">
   <se:rowformat>
    <tr>
     <td title="CN"><a href="{channel.link(detailadsuser)}?upn={this.field(userPrincipalName)}"><img src="res/UserManager.gif" alt="" /> {this.field(cn, default='')}</a></td> 
     <td title="department">{this.field(sAMAccountName, default='')}</td>
    </tr>
   </se:rowformat>
  </se:parameter>
  <se:parameter name="resultformat">
   <table class="tech"> 
    <tr>
     <th>Name</th>
     <th>Loginname</th>
    </tr>
    {this.result()}
   </table>
  </se:parameter> 
  <se:parameter name="default">No data.</se:parameter>
 </se:parameters>
</se:format> 
</div>

Result

The result of this page:
overview

 

Details Active Directory Users

This page retrieves the data from the selected user from the configured active directory. The only difference between the overview sqlquery and the details sqlquery is the filter on the userprincipalname. Because of limitations within ADSI SQL Dialect this sql query unfortunately can't be reused in e.g. a translation.

Create the following item:
Title: Details Active Directory Users
Contenttype: WebPage
Code: DETAILADSUSER
Logic:

Smartsite SXML CopyCode image Copy Code 
<!-- The object is called userAccountControl and it is assigned a value as per below:
512 - Enable Account
514 - Disable account
544 - Account Enabled - Require user to change password at next logon 
66048 - Password never expires
66050 - Disabled
262656 - Smart Card Logon Required

More information about filtering within the ADSI SQL Query: http://msdn.microsoft.com/en-us/library/aa746494(VS.85).aspx
-->

<style type="text/css" >
 TABLE.tech {border: 1px solid #CCCCCC; border-collapse: collapse; width: 100%}
 TABLE.tech TD {background-color: #EEEEEE; padding-left: 5px; padding-right: 5px; }
 TABLE.tech TH {text-align:left; padding-left: 5px; padding-right: 5px; background-image: url(/scf/gfx/box/boxctop_grijs.png); color: white }
 TABLE.tech TD.bggreen { background-color: #CCFFCC !important; }
 TABLE.tech TD.techr { text-align:right; }
 TABLE.tech tr {border-bottom: 1px dotted black; padding-top:2px}
 TABLE.tech tr.nav th {padding: 20px; text-align: center}
 TABLE.tech td {border: 1px solid #FFFFFF}
 TABLE.tech th.legend {text-align: right; background-color: #c0c0c0}
</style>

<se:itemdata field="body" />

<style type="text/css" >
 .dbinfo_box {width: 500px; }
 .dbinfo_box .sml_clientbox { padding: 0 }
</style>

<div class="sml_clientbox panel_nobox panel_box dbinfo_box">
<se:sqlquery connection="MyADSI" save="adsidetails" resulttype="datatable">
 <se:parameters>
  <se:parameter name="sql">
   SELECT cn, userPrincipalName, sAMAccountName, initials, mail, telephonenumber,
   wwwhomepage,department,userAccountControl,physicalDeliveryOfficeName,streetAddress,l,
   postalCode,st,homePhone,company,mobile,facsimileTelephoneNumber,ipPhone,co,pager,title,description
   FROM 'LDAP://CN=Users,DC=SMARTSITE,DC=LAN' 
   WHERE userPrincipalName='{request.query(upn)}'
   AND userAccountControl = 66048 
   AND objectCategory='person'
   AND objectClass='user' 
   ORDER BY CN
  </se:parameter>
 </se:parameters>
</se:sqlquery>

<se:format inputdata="adsidetails" whitespace="simpleformat">
 <se:parameters>
  <se:parameter name="format">
   <se:rowformat>
    <tr>
     <td><strong>General</strong></td>
     <td></td>
     <td rowspan="24" valign="top"><img src="/pics/smoelenboek/{this.field(initials, default='_geenplaatje')}.jpg?hid=img;w=100" alt="{this.field(cn, default='')}"></img></td>
    </tr>
    <tr>
     <td>Name:</td>
     <td title="Full Name">{this.field(cn, default='')}</td> 
    </tr> 
    <tr>
     <td>Loginname:</td>
     <td title="sAMAccountName">{this.field(sAMAccountName, default='')}</td> 
    </tr>
    <tr>
     <td>Initials:</td>
     <td title="initials">{this.field(initials, default='')}</td>
    </tr> 
    <tr>
     <td>Office:</td>
     <td title="physicalDeliveryOfficeName">{this.field(physicalDeliveryOfficeName, default='')}</td>
    </tr>
    <tr>
     <td>Telephone Number:</td>
     <td title="telephonenumber">{this.field(telephonenumber, default='')}</td>
    </tr>
    <tr>
     <td>E-mail:</td>
     <td title="mail"><a href="mailto:{this.field(mail, default='')}">{this.field(mail, default='')}</a></td>
    </tr>
    <tr>
     <td>Web page:</td>
     <td title="mail"><a href="{this.field(wwwhomepage, default='')}" target="_blank">{this.field(wwwhomepage, default='')}</a></td>
    </tr>
    <tr>
     <td colspan="2"><hr /><strong>Address</strong></td>
    </tr>
    <tr>
     <td>Street:</td>
     <td title="streetAddress">{this.field(streetAddress, default='')}</td> 
    </tr> 
    <tr>
     <td>City:</td>
     <td title="Woonplaats">{this.field(l, default='')}</td> 
    </tr>
    <tr>
     <td>State/province:</td>
     <td title="st">{this.field(st, default='')}</td> 
    </tr>
    <tr>
     <td>ZIP/Postal Code:</td>
     <td title="postalCode">{this.field(postalCode, default='')}</td> 
    </tr>
    <tr>
     <td>Country:</td>
     <td title="country">{this.field(co, default='')}</td> 
    </tr>
    <tr>
     <td colspan="2"><hr /><strong>Telephones</strong></td>
    </tr>
    <tr>
     <td>Home Phone:</td>
     <td title="homePhone">{this.field(homePhone, default='')}</td> 
    </tr>
    <tr>
     <td>Pager:</td>
     <td title="iPPhone">{this.field(pager, default='')}</td> 
    </tr>
    <tr>
     <td>Mobile:</td>
     <td title="mobile">{this.field(mobile, default='')}</td> 
    </tr>
    <tr>
     <td>Fax:</td>
     <td title="facsimileTelephoneNumber">{this.field(facsimileTelephoneNumber, default='')}</td> 
    </tr>
    <tr>
     <td>IP Phone:</td>
     <td title="iPPhone">{this.field(iPPhone, default='')}</td> 
    </tr>
    <tr>
     <td colspan="2"><hr /><strong>Organization</strong></td>
    </tr>
    <tr>
     <td>Title:</td> 
     <td title="title">{this.field(title, default='')}</td>
    </tr> 
    <tr>
     <td>Department:</td> 
     <td title="department">{this.field(department, default='')}</td>
    </tr>
    <tr>
     <td>Company:</td> 
     <td title="company">{this.field(company, default='')}</td>
    </tr>
   </se:rowformat>
  </se:parameter>
  <se:parameter name="resultformat">
   <table class="tech">
    <tr>
     <th colspan="3"><img src="res/UserManager.gif" alt=""></img> Properties</th> 
    </tr> 
    {this.result()}
   </table>
  </se:parameter> 
  <se:parameter name="default">No data.</se:parameter>
 </se:parameters>
</se:format> 
</div>

Result

The result of this page:
details

Manager Action: Insert/Update Users from Active Directory to Smartsite Users table

To insert and update users from the configured Active Directory into the Smartsite users table we need to create an application page that's going to be executed by a manager action (Synchronize Active Directory Data) + we need to update the Smartsite users table with the new fields and we change the Edit Users action that can be called from the Configure Security action.

 

Step 1
Update Smartsite Users table with the following fields (simply use varchar(50) as Data Type):
Telephonenumber
Company
Adres
Postcode
Woonplaats
Provincie
faxthuis
iptelefoon
interneafkorting
kamernummer
Toestel
mobiel
website

After the creation run "pr_rebuildviews".

Step 2
Create the following item:
Title: Manager Action: Insert/Update Users from Active Directory to Smartsite Users table
Contenttype: WebPage
Code: INSERTUPDATEALLADSUSERS
Logic:

Smartsite SXML CopyCode image Copy Code 
<style type="text/css" >
 BODY {font-family: arial, verdana; font-size: 8pt;}
 TABLE.tech {border: 1px solid #CCCCCC; border-collapse: collapse; width: 100%}
 TABLE.tech TD {background-color: #EEEEEE; padding-left: 5px; padding-right: 5px; }
 TABLE.tech TH {text-align:left; padding-left: 5px; padding-right: 5px; background-image: url(/scf/gfx/box/boxctop_grijs.png); color: white }
 TABLE.tech TD.bggreen { background-color: #CCFFCC !important; }
 TABLE.tech TD.techr { text-align:right; }
 TABLE.tech tr {border-bottom: 1px dotted black; padding-top:2px}
 TABLE.tech tr.nav th {padding: 20px; text-align: center}
 TABLE.tech td {border: 1px solid #FFFFFF}
 TABLE.tech th.legend {text-align: right; background-color: #c0c0c0}
</style>

<se:itemdata field="body" />

<style type="text/css" >
 .dbinfo_box {width: 400px; }
 .dbinfo_box .sml_clientbox { padding: 0 }
</style>

<div class="sml_clientbox panel_nobox panel_box dbinfo_box">
<se:sqlquery connection="MyADSI" localid="adsi">
 <se:parameters>
  <se:parameter name="sql">
   SELECT telephonenumber,sAMAccountName,cn,displayname,mail,badpwdcount,company,streetaddress,postalcode,l,st,facsimileTelephoneNumber,ipphone,initials,physicalDeliveryOfficeName,telephoneNumber,pager,wWWHomePage
   FROM 'LDAP://CN=Users,DC=SMARTSITE,DC=LAN' 
   WHERE userPrincipalName='*' 
   AND userAccountControl = 66048 
   AND objectCategory='person'
   AND objectClass='user' 
   ORDER BY CN 
  </se:parameter>
  <se:parameter name="format">
   <se:rowformat>
    <se:sqlquery resulttype="integer" save="usercount" mode="scalar">
     <se:parameters>
      <se:parameter name="sql">
       SELECT count(*)
       FROM users
       WHERE loginname='{scope.adsi.field(sAMAccountName, default='')}'
      </se:parameter> 
     </se:parameters>
    </se:sqlquery>
    <se:if expression="buffer.get(usercount)==1">
     <se:then>
      <se:sqlquery error="{this.error.message()}" mode="nonquery" resulttype="none">
       <se:parameters>
        <se:parameter name="sql">
         UPDATE USERS 
         SET fullname='{scope.adsi.field(displayname, default='')}',
         telephonenumber='{scope.adsi.field(telephonenumber, default='')}', 
         email='{scope.adsi.field(mail, default='')}',
         failedpasswordattemptcount='{scope.adsi.field(badpwdcount, default='')}',
         company='{scope.adsi.field(company, default='')}',
         adres='{scope.adsi.field(streetaddress, default='')}',
         postcode='{scope.adsi.field(postalcode, default='')}',
         woonplaats='{scope.adsi.field(l, default='')}',
         provincie='{scope.adsi.field(st, default='')}',
         faxthuis='{scope.adsi.field(facsimileTelephoneNumber, default='')}',
         iptelefoon='{scope.adsi.field(ipphone, default='')}',
         interneafkorting='{scope.adsi.field(initials, default='')}',
         kamernummer='{scope.adsi.field(physicalDeliveryOfficeName, default='')}',
         toestel='{scope.adsi.field(telephoneNumber, default='')}',
         mobiel='{scope.adsi.field(pager, default='')}',
         website='{scope.adsi.field(wWWHomePage, default='')}' 
         WHERE loginname='{scope.adsi.field(sAMAccountName, default='')}' 
        </se:parameter>
       </se:parameters>
      </se:sqlquery>
      <tr>
       <td><img src="res/UserManager.gif" alt="" />{scope.adsi.field(cn, default='')}</td>
       <td>{scope.adsi.field(sAMAccountName, default='')}</td>
       <td><img src="res/ok.gif" alt="Updated" /></td>
      </tr>
     </se:then>
     <se:else>
      <se:sqlquery error="{this.error.message()}" mode="nonquery" resulttype="none">
       <se:parameters>
        <se:parameter name="sql">
         INSERT INTO USERS (
         loginname,
         fullname,
         telephonenumber,
         Email,
         FailedPasswordAttemptCount,
         company,
         Adres,
         Postcode,
         Woonplaats,
         provincie,
         faxthuis,
         iptelefoon,
         interneafkorting,
         kamernummer,
         toestel,
         mobiel,
         website
         )
         VALUES ('{scope.adsi.field(sAMAccountName, default='')}',
         '{scope.adsi.field(displayname, default='')}',
         '{scope.adsi.field(telephonenumber, default='')}',
         '{scope.adsi.field(mail, default='')}',
         '{scope.adsi.field(badpwdcount, default='')}',
         '{scope.adsi.field(company, default='')}',
         '{scope.adsi.field(streetaddress, default='')}',
         '{scope.adsi.field(postalcode, default='')}',
         '{scope.adsi.field(l, default='')}',
         '{scope.adsi.field(st, default='')}',
         '{scope.adsi.field(facsimileTelephoneNumber, default='')}',
         '{scope.adsi.field(ipphone, default='')}',
         '{scope.adsi.field(initials, default='')}',
         '{scope.adsi.field(physicalDeliveryOfficeName, default='')}',
         '{scope.adsi.field(telephoneNumber, default='')}',
         '{scope.adsi.field(pager, default='')}',
         '{scope.adsi.field(wWWHomePage, default='')}') 
        </se:parameter>
       </se:parameters>
      </se:sqlquery>
      <tr>
       <td><img src="res/UserManager.gif" alt="" /> {scope.adsi.field(cn, default='')}</td>
       <td>{scope.adsi.field(sAMAccountName, default='')}</td>
       <td><img src="res/ok.gif" alt="Inserted" /></td>
      </tr> 
     </se:else>
    </se:if>
   </se:rowformat> 
  </se:parameter>
  <se:parameter name="resultformat">
   <table class="tech">
    <tr>
     <th colspan="3">Result (affected rows: {this.rowcount()})</th> 
    </tr> 
    {this.result()}
   </table>
  </se:parameter>
 </se:parameters>
</se:sqlquery>
</div>

Step 3: create a manager action (configure security/edit action list). Copy the code below, add a new action and paste the code:

XML CopyCode image Copy Code 
<?xml version="1.0" encoding="ISO-8859-1"?> 
<UserAction application="Smartsite5" type="">
 <Action>Synchronize Active Directory data</Action>
 <Code>psaADSIData</Code>
 <Description/>
 <Object>[script]</Object>
 <Parameters>showModelessDialog('/?id=INSERTUPDATEALLADSUSERS',self,'dialogWidth:640px;dialogWidth:418px;resizable:1;status:0');</Parameters>
 <IconURL>/res/UserManager.gif</IconURL>
 <ToolTip/>
 <Hidden>0</Hidden>
 <MainMenu>Configuration</MainMenu>
 <MenuSortIndex>0</MenuSortIndex>
 <PopupSortIndex/>
 <QuickButtonSortIndex/>
 <Roles>Administrator</Roles>
 <ContentTypes/>
</UserAction>

Step 4
To show all the new fields in the Edit Users action that can be called from the Configure Security action we have to update the users.xwf (Local file that can be found in "System\DataEditor\Forms"):

XML CopyCode image Copy Code 
<?xml version="1.0" encoding="ISO-8859-1"?>
<webform>
 <control type="Table" width="100%">
  <control id="frCredentials" type="Frame" caption="Credentials" width="100%" row="1" col="1" height="100%">
   <control id="LoginName" />
   <control id="Password" />
   <control id="HiddenNr" /> 
  </control>
  <control id="frProperties" type="Frame" caption="Properties" width="100%" row="1" col="2" height="100%">
   <control id="FullName" />
   <control id="Email" />
   <control id="Telephonenumber" />
   <control id="Company" />
   <control id="Adres" />
   <control id="Postcode" />
   <control id="Woonplaats" />
   <control id="Provincie" />
   <control id="Faxthuis" caption="Fax thuis" />
   <control id="Iptelefoon" caption="IP Telefoon" />
   <control id="Interneafkorting" caption="Interne Afkorting" />
   <control id="Kamernummer" caption="Kamernummer" />
   <control id="Toestel" />
   <control id="Mobiel" caption="Mobiel" />
   <control id="Website" caption="Website" />   
  </control>
  <control id="frSecurity" type="Frame" caption="Security" width="100%" row="2" col="1" colspan="2" height="100%">
   <control id="UserGroups"/>
   <control id="PrimaryGroupID"/>
  </control>
  <control id="frAccountSettings" type="Frame" caption="Account Settings" width="100%" row="3" col="1" height="100%">
   <control id="Disabled"/>
   <control id="Activate"/>
   <control id="Expires"/>
   <control id="Locale"/>   
  </control>
  <control id="frAccountStatus" type="Frame" caption="Account Status" width="100%" row="3" col="2" height="100%">   
   <control id="LastLogin"/>
   <control id="LastLockoutDate"/>
   <control id="FailedPasswordAttemptCount"/>
   <control id="FailedPasswordAttemptWindowStart"/>
  </control>
 </control>
</webform>

Result

After reloading the metadata you can start the action from the menu Admin, Synchronize Active Directory data.
Output from manager action:
manageraction

After you have run this action all data from the users of the active directory have been imported/updated into the Smartsite Users table. To validate the data start the Edit Users action.

The updated action will look like this:
edituser

Conclusion

This is of course just a simple example. Using differt ADSI SQL Queries gives you the opportunity to use any kind of data from the active directory that can be used in several different applications (e.g. practically any web 2.0 solution) .

Scheduled insert/update
As of Smartsite 1.2 the SignalSmartsite.exe works with Integrated Security.
At that time you can create a scheduled task (with an active directory useraccount) using a *.bat-file that executes the application page at any time. This way you can update the Smartsite Users table automatically with the recent Active Directory data.
Content of bat-file can be something like:
signalsmartsite /render:INSERTUPDATEALLADSUSERS /integrated /endpoint:localhost

Tools
One of the tools that can be used to discover the available fieldnames within the Active Directory is the Active Directory Explorer v1.2 which can be found here: http://technet.microsoft.com/en-us/sysinternals/bb963907.aspx

Install & Config