Troubleshooting Tip: Report returns no data when an LDAP query filter is enabled

[farhanahmed]

Description	This article describes how to troubleshoot when a report in FortiAnalyzer returns no data when an LDAP query filter is enabled even though logs are present.
Scope	FortiAnalyzer.
Solution	Make sure users are present in the groups because the LDAP Query filters are on Groups, not the users or the Organization Unit (OU). Make sure the Remote Server config in FortiAnalyzer (under System Settings -> Remote Authentication Servers) has correct Common Name identifier. Check the ldap-cache-timeout, reduce it so that FortiAnalyzer can refresh the cache. Refer to article: Technical Tip: Change FortiAnalyzer/FortiManager LDAP cache timeout. Make sure the Common Name Identifier is the same attribute used in FortiAnalyzer LDAP Server config as it is used on the FortiGates to log the user information. If FortiAnalyzer logs show usernames as FIRSTNAME(dot)LASTNAME, it indicates sAMAccountName is used, so configure the FortiAnalyzer LDAP server to use sAMAccountName (under System Settings -> Remote Authentication Servers). Do not use Common Name (CN) in this case, as it formats user in FIRSTNAME(space)LASTNAME, which won't match the FortiGate log entries. Example: In FortiAnalyzer logs, users are in the format FIRSTNAME(dot)LASTNAME (for example, JOHN.DOE): On the Remote LDAP Server, users John Doe and Jane Doe are part of a user group 'Test_GRP' while the user Bob.Doe is part of the group 'Test_GRP_2. In FortiAnalyzer, using the default 'Admin and System Events Report' (which shows FortiGate login information) with LDAP Query filter enabled and filtering for the group 'Test_GRP'. Running the report shows no matching log, even though there are logs for successful logins by Jane Doe and John Doe: In FortiAnalyzer, using the sqlreportd debugs: diagnose debug application sqlreportd 255 diagnose debug enable This shows that the user's information is inthe  format FIRSTNAME(space)LASTNAME (example John Doe): A Packet Capture from FortiAnalyzer to the LDAP server shows that the server is sending the user details after matching the group, but the matching attribute is CN (Common Name). FortiAnalyzer LDAP Server config (System Settings -> Remote Authentication Servers) has the Common Name identifier set to CN. Hence, the report is empty because it is not match the information in logs. After all, Common Name (CN) is in the format FIRSTNAME(space)LASTNAME, but the users in FortiAnalyzer logs are in the format FIRSTNAME(dot)LASTNAME. To fix the issue, delete the LDAP server config in FortiAnalyzer (System Settings -> Remote Authentication Servers) and add it again with the Common Name Identifier set to sAMAccountName: Running the same report again shows correct results: Packet Capture from FortiAnalyzer to the LDAP Server shows attribute sAMAccountName: And the sqlreportd debugs in FortiAnalyzer show the users fetched are in the correct format FIRSTNAME(dot)LASTNAME, hence matching the data in logs: Related documents: Technical Tip: Change FortiAnalyzer/FortiManager LDAP cache timeout Filtering report output – FortiAnalyzer Admin Guide How FortiAnalyzer Reports are populated CLI Reference - FortiAnalyzer How to configure LDAP