Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

LDAP.JSON

Queries JSON structure of LDAP data.

Component Version macOS Windows Linux Server iOS SDK
LDAP JSON 8.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ❌ No
MBS( "LDAP.JSON"; LDAPRef )   More

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).

Examples

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 ) ]

See also

Release notes

Blog Entries

This function checks for a license.

Created 11st December 2017, last changed 2nd November 2022


LDAP.GetTimeOut - LDAP.List