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  

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 OK or error.

Description

Queries JSON structure of LDAP data.
Please first search, then query here the result as JSON.

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

Created 11st December 2017, last changed 27th July 2021


LDAP.GetTimeOut - LDAP.Modify

Feedback: Report problem or ask question.