LDAP.JSON --------- Queries JSON structure of LDAP data. | Component | Version | macOS | Windows | Linux | Server | iOS SDK | |---|---|---|---|---|---|---| | [LDAP](component_LDAP.md) [JSON](component_JSON.md) | [8.0](newinversion80.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ❌ No | MBS( "LDAP.JSON"; LDAPRef ) **MBS**( **"LDAP.JSON";** /\* Queries JSON structure of LDAP data. \*/ **$LDAPRef**) /\* The reference number for the LDAP connection.e.g. $ldap \*/ ### Parameters | Parameter | Description | Example | |---|---|---| | LDAPRef | The reference number for the LDAP connection. | $ldap | ### Result Returns JSON or error. ### Description Queries JSON structure of LDAP data. Please first search, then query here the result as JSON. Value may be hex encoded if the attribute is considered binary data like an uuid (see [LDAP.SetBinaryKeys](LDAPSetBinaryKeys.md)). ### Examples Query JSON: # Connect Set Variable \[ $r ; Value: MBS ("[LDAP.Connect](LDAPConnect.md)"; "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](LDAPBind.md)"; $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](LDAPSearch.md)"; $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](LDAPRelease.md)"; $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](LDAPConnect.md)"; "ldap.test.de"; 0; 389 ) \] Set Variable \[ $r ; Value: MBS ("[LDAP.Bind](LDAPBind.md)"; $ldap ; "cn=admin,dc=ldap,dc=test,dc=de"; "S2A2S@home"; "simple" ) \] Set Variable \[ $r ; Value: MBS ("[LDAP.Search](LDAPSearch.md)"; $ldap ; "ou=Users,dc=ldap,dc=test,dc=de"; "Subtree"; ""; ""; 0; 20; 999) \] Set Field \[ Employee::\_LDAP .[JSON.Query](JSONQuery.md) ; MBS ("LDAP.JSON"; $ldap ) \] Set Variable \[ $r ; Value: MBS ("[LDAP.Release](LDAPRelease.md)"; $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](LDAPSearch.md)" ; $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](LDAPSearchResultCount.md)"; $ldap ) \] If \[ $foundInAD > 0 \] # # extract the results Set Variable \[ $jsonIndex ; Value: 0 \] Loop Set Variable \[ $username ; Value: MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $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](LDAPSearchResultDistinguishedName.md)"; $ldap ; $jsonIndex ) \] Set Field \[ @People::Ac | LDAP | DisplayName ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $ldap ; $jsonIndex ; "displayName" ) \] Set Field \[ @People::Ac | LDAP | EmailAddr ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $ldap ; $jsonIndex ; "mail" ) \] Set Field \[ @People::Ac | LDAP | Groups | Raw ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $ldap ; $jsonIndex ; "memberOf" ) \] Set Field \[ @People::Ac | LDAP | PrivEmail ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $ldap ; $jsonIndex ; "otherMailbox" ) \] Set Field \[ @People::Ac | LDAP | PwdLastSet ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $ldap ; $jsonIndex ; "pwdLastSet" ) \] Set Field \[ @People::Ac | LDAP | userAccountControl ; MBS ("[LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md)" ; $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](LDAPSearchResultAttributeValuesByName.md)" ; $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](LDAPSearchResultAttributeValuesByName.md)" ; $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](LDAPRelease.md)"; $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 ) \] ### See also - [IsError](IsError.md) - [JSON.Query](JSONQuery.md) - [LDAP.AddJSON](LDAPAddJSON.md) - [LDAP.Bind](LDAPBind.md) - [LDAP.SearchResult.AttributeValue](LDAPSearchResultAttributeValue.md) - [LDAP.SearchResult.AttributeValues](LDAPSearchResultAttributeValues.md) - [LDAP.SearchResult.AttributeValuesByName](LDAPSearchResultAttributeValuesByName.md) - [LDAP.SearchResult.Count](LDAPSearchResultCount.md) - [LDAP.SearchResult.DistinguishedName](LDAPSearchResultDistinguishedName.md) - [LDAP.SetBinaryKeys](LDAPSetBinaryKeys.md) ### Release notes - **Version 8.0** - Added [JSON](http://www.mbsplugins.eu/component_JSON.shtml) functions for LDAP: [LDAP.AddJSON](http://www.mbsplugins.eu/LDAPAddJSON.shtml), [LDAP.JSON](http://www.mbsplugins.eu/LDAPJSON.shtml) and [LDAP.ModifyJSON](http://www.mbsplugins.eu/LDAPModifyJSON.shtml). ### Blog Entries - [Embrace JSON](https://www.mbsplugins.de/archive/2020-09-29/Embrace_JSON/monkeybreadsoftware_blog_filemaker) - [With JSON in excellent form](https://www.mbsplugins.de/archive/2019-11-16/With_JSON_in_excellent_form/monkeybreadsoftware_blog_filemaker) - [MBS FileMaker Plugin, version 7.6pr2](https://www.mbsplugins.de/archive/2017-12-11/MBS_FileMaker_Plugin_version_7/monkeybreadsoftware_blog_filemaker) - [LDAP with JSON](https://www.mbsplugins.de/archive/2017-12-11/LDAP_with_JSON/monkeybreadsoftware_blog_filemaker) This function checks for a license. Created 11st December 2017 , last changed 25th February 2026 [LDAP.GetTimeOut](LDAPGetTimeOut.md) - [LDAP.List](LDAPList.md) [HTML Version](LDAPJSON.shtml)