Topics
All
MacOS
(Only)
Windows
(Only)
Linux
(Only, Not)
iOS
(Only, Not)
Components
Crossplatform Mac & Win
Server
Client
Old
Deprecated
Guides
Examples
Videos
New in version:
10.1
10.2
10.3
10.4
10.5
11.0
11.1
11.2
11.3
11.4
Statistic
FMM
Blog
Queries JSON structure of LDAP data.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
LDAP JSON | 8.0 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ❌ No |
Parameter | Description | Example |
---|---|---|
LDAPRef | The reference number for the LDAP connection. | $ldap |
Returns OK or error.
Query JSON:
# Connect
Set Variable [ $r ; Value: MBS("LDAP.Connect"; "ldap.forumsys.com"; 0; 389) ]
If [ MBS("IsError") ]
Show Custom Dialog [ "LDAP error" ; "Failed to connect." & ¶ & $r ]
Else
Set Variable [ $ldap ; Value: $r ]
# Login
Set Variable [ $r ; Value: MBS("LDAP.Bind"; $ldap; "uid=tesla,dc=example,dc=com"; "password"; "simple") ]
If [ MBS("IsError") ]
Show Custom Dialog [ "LDAP error" ; "Failed to authenticate." & ¶ & $r ]
Else
# Search
Set Variable [ $r ; Value: MBS("LDAP.Search"; $ldap; "dc=example,dc=com"; "subtree"; "(givenName=*)"; ""; 0; 20; 999) ]
# Check results
Show Custom Dialog [ "JSON" ; MBS("LDAP.JSON"; $ldap) ]
End If
# Cleanup
Set Variable [ $r ; Value: MBS("LDAP.Release"; $ldap) ]
End If
Example result:
[{
"dn": "uid=test,dc=example,dc=com",
"attributes": [{
"name": "objectClass",
"values": ["posixAccount", "top", "inetOrgPerson"]
}, {
"name": "gidNumber",
"values": ["0"]
}, {
"name": "givenName",
"values": ["Test"]
}, {
"name": "sn",
"values": ["Test"]
}, {
"name": "displayName",
"values": ["Test"]
}, {
"name": "uid",
"values": ["test"]
}, {
"name": "initials",
"values": ["TS"]
}, {
"name": "homeDirectory",
"values": ["home"]
}, {
"name": "cn",
"values": ["Test"]
}, {
"name": "uidNumber",
"values": ["24601"]
}, {
"name": "o",
"values": ["Company"]
}]
}]
Query a person via JSON:
Set Variable [ $ldap ; Value: MBS("LDAP.Connect"; "ldap.test.de"; 0; 389 ) ]
Set Variable [ $r ; Value: MBS("LDAP.Bind"; $ldap; "cn=admin,dc=ldap,dc=test,dc=de"; "S2A2S@home"; "simple" ) ]
Set Variable [ $r ; Value: MBS("LDAP.Search"; $ldap; "ou=Users,dc=ldap,dc=test,dc=de"; "Subtree"; ""; ""; 0; 20; 999) ]
Set Field [ Employee::_LDAP.JSON.QUERY ; MBS("LDAP.JSON"; $ldap) ]
Set Variable [ $r ; Value: MBS("LDAP.Release"; $ldap) ]
Example result:
[
{
"dn": "ou=Users,dc=ldap,dc=test,dc=de",
"attributes": [
{
"name": "objectClass",
"values": [
"organizationalUnit",
"top"
]
},
{
"name": "ou",
"values": [
"Users"
]
}
]
},
{
"dn": "uid=bob.miller,ou=Users,dc=ldap,dc=test,dc=de",
"attributes": [
{
"name": "objectClass",
"values": [
"posixAccount",
"shadowAccount",
"top",
"inetOrgPerson",
"organizationalPerson",
"person"
]
},
{
"name": "uid",
"values": [
"bob.miller"
]
},
{
"name": "uidNumber",
"values": ["1"]
},
{
"name": "cn",
"values": [
"Bob Miller"
]
},
{
"name": "mail",
"values": [
"bob.miller@test.de"
]
},
{
"name": "homeDirectory",
"values": [
"/usr/local/var/"
]
},
{
"name": "sn",
"values": [
"Miller"
]
},
{
"name": "employeeType",
"values": [
"Employee"
]
},
{
"name": "givenName",
"values": [
"Bob"
]
},
{
"name": "employeeNumber",
"values": ["77"]
},
{
"name": "userPassword",
"values": [
"xxxx"
]
},
{
"name": "gidNumber",
"values": ["100"]
}
]
}
]
Example script get records:
# ========================================
# Purpose:
# Queries Active Directory from a server-side script
# This guarrantees the MBS plugin availability and that the MBS plugin has LDAP access to the Domain Controller
# Returns:
# $error = Error code if unsuccessful
# $error = 0 for success
# $resultText = Text summary of the success or error
# Parameters:
# $serverName
# $serverDomain
# $personIDs
# Called from:
# (script) "Get AD Records"
# Author:
# John Munro (HJM) from Deutsche Schule Tokyo Yokohama
# Notes:
# none
# History:
# 2020-06-15 HJM - created
# 2021-05-20 HJM - Replaced bind code with call to LDAPServerBind (including added parameter $serverDomain to all calls)
# 2021-05-21 HJM - modified result returned to be in #Assign variable method
# ========================================
#
Set Variable [ $! ; Value: #Assign ( Get ( ScriptParameter ) ) ]
#
# The branch in LDAP containing all active entries
Set Variable [ $searchBase ; Value: "dc=dsty,dc=ac,dc=jp" ]
#
Set Error Capture [ On ]
#
# If debugging these parameters will be empty so fill with test data
If [ $serverName = "" ]
Set Variable [ $serverName ; Value: "sys-dc1" ]
End If
If [ $serverName = "" ]
Set Variable [ $serverDomain ; Value: "dsty.ac.jp" ]
End If
If [ $personIDs = "" ]
Set Variable [ $personIDs ; Value: "12345" ]
End If
#
# Jump to a working list populated with the records of the passed IDs
Set Field [ #Selector::SelectedPersonID ; $personIDs ]
Go to Layout [ “@People” (@People) ; Animation: None ]
Go to Related Record [ Show only related records ; From table: “#SelectedPerson” ; Using layout: “@People” (@People) ]
#
#
# Bind to LDAP
Perform Script [ Specified: From list ; “LDAPServerBind” ; Parameter: # ( "serverName" ; $serverName ) & # ( "serverDomain" ; $serverDomain ) ]
# Returns $error,$resultText, $ldap
#
Set Variable [ $! ; Value: #Assign ( Get ( ScriptResult ) ) ]
If [ $error <> 0 ]
Go to Layout [ original layout ; Animation: None ]
Show Custom Dialog [ "LDAP error" ; $resultText ]
Exit Script [ Text Result: # ( "error" ; $error ) & # ( "resultText" ; "LDAP error: " & $resultText ) ]
End If
#
#
# Define search filter
Set Variable [ $usernameList ; Value: Substitute ( @People::Ac | DSTY | Username FoundSet_List ; "¶¶" ; "¶" ) //Remove any CR from the source field ]
Set Variable [ $LDAPFilter ; Value: "(|" & "(sAMAccountName=" & Substitute ( $usernameList ; "¶" ; ")(sAMAccountName=" ) & ")" & ")" ]
#
# Perform the search on LDAP
Set Variable [ $result ; Value: MBS ( "LDAP.Search" ; $ldap ; $searchBase ; "subtree" ; $LDAPFilter ; "" ; 0 ; 30 ; 9999 ) ]
#
# Record the search timestamp for all records (including those not present in AD)
Set Variable [ $LookupTimestamp ; Value: Get ( CurrentTimestamp ) ]
#
// # Check results
// Show Custom Dialog [ "JSON" ; MBS("LDAP.JSON"; $ldap) ]
#
Set Variable [ $foundInAD ; Value: MBS("LDAP.SearchResult.Count"; $ldap) ]
If [ $foundInAD > 0 ]
#
# extract the results
Set Variable [ $jsonIndex ; Value: 0 ]
Loop
Set Variable [ $username ; Value: MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "sAMAccountName" ) ]
#
# find the record with DSTYUsername = $username
Perform Find [ Restore ]
#
If [ Get ( FoundCount ) = 1 ]
# There should only be one record found as DSTYUsername has unique values
#
# Populate the LDAP cache fields
Set Field [ @People::Ac | LDAP | AD Network ; $serverDomain ]
Set Field [ @People::Ac | LDAP | DataTimestamp ; $LookupTimestamp ]
Set Field [ @People::Ac | LDAP | userDN ; MBS("LDAP.SearchResult.DistinguishedName"; $ldap; $jsonIndex) ]
Set Field [ @People::Ac | LDAP | DisplayName ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "displayName" ) ]
Set Field [ @People::Ac | LDAP | EmailAddr ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "mail" ) ]
Set Field [ @People::Ac | LDAP | Groups | Raw ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "memberOf" ) ]
Set Field [ @People::Ac | LDAP | PrivEmail ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "otherMailbox" ) ]
Set Field [ @People::Ac | LDAP | PwdLastSet ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "pwdLastSet" ) ]
Set Field [ @People::Ac | LDAP | userAccountControl ; MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "userAccountControl" ) ]
Set Field [ @People::Ac | LDAP | Username ; $username ]
#
# Convert raw time from AD to a local Filemaker timestamp
Set Variable [ $accountExpires ; Value: MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "accountExpires" ) ]
If [ $accountExpires > 0 ]
Set Variable [ $accountExpires ; Value: Timestamp ( Date ( 1 ; 1 ; 1601 ) ; Time ( 0 ; 0 ; 0 ) + 9 * 3600 ) + Round ( $accountExpires / 10000000 / 60 ; 0 ) * 60 ]
Else
Set Variable [ $accountExpires ; Value: "" ]
End If
Set Field [ @People::Ac | LDAP | accountExpires ; $accountExpires ]
#
# Convert raw time from AD to a local Filemaker timestamp
Set Variable [ $lastLogon ; Value: MBS("LDAP.SearchResult.AttributeValuesByName" ; $ldap ; $jsonIndex ; "lastLogon" ) ]
If [ $lastLogon > 0 ]
Set Variable [ $lastLogon ; Value: Timestamp ( Date ( 1 ; 1 ; 1601 ) ; Time ( 0 ; 0 ; 0 ) + 9 * 3600 ) + Round ( $lastLogon / 10000000 / 60 ; 0 ) * 60 ]
Else
Set Variable [ $lastLogon ; Value: "" ]
End If
#
# Update the lastLogon field with only if it is a more recent date
Set Field [ @People::Ac | LDAP | LastLogon ; Max ( @People::Ac | LDAP | LastLogon ; $lastLogon ) ]
#
End If
#
Set Variable [ $jsonIndex ; Value: $jsonIndex + 1 ]
Exit Loop If [ $jsonIndex >= $foundInAD ]
#
End Loop
#
End If
#
# Check for records not updated (i.e. not present in AD)
#
# Refind the original people set
Set Field [ #Selector::SelectedPersonID ; $personIDs ]
Go to Related Record [ Show only related records ; From table: “#SelectedPerson” ; Using layout: “@People” (@People) ]
#
# Omit those that have just been updated
Constrain Found Set [ Restore ]
Set Variable [ $missingFromAD ; Value: Get ( FoundCount ) ]
#
# if there are any remaining, these were not found in the LDAP query, so clear the fields in case the entry was removed from AD
If [ $missingFromAD > 0 ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | DataTimestamp ; $LookupTimestamp ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | AD Network ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | DisplayName ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | userDN ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | EmailAddr ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | Groups | Raw ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | LastLogon ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | PrivEmail ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | PwdLastSet ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | userAccountControl ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | Username ; "" ]
Replace Field Contents [ With dialog: Off ; @People::Ac | LDAP | accountExpires ; "" ]
End If
#
# Cleanup
Set Variable [ $releaseResult ; Value: MBS("LDAP.Release"; $ldap) ]
Go to Layout [ original layout ; Animation: None ]
#
#
# Return error free result
Exit Script [ Text Result: # ( "error" ; 0 ) & # ( "resultText" ; "Staff found and updated from AD: " & $foundInAD & ¶ & "New staff needing to be added to AD: " & $missingFromAD ) ]
Created 11st December 2017, last changed 27th July 2021
Feedback: Report problem or ask question.