efa-project.org

AD login was previously working great, but i just noticed today that after all my DCs are upgraded to 2016, EFA is no longer able to login. i am getting Bad Username or Password on my 3018 box, and my production 3015 box is getting an SQL error.

Error executing query:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 169

SQL:

SELECT
COUNT(*) AS processed,
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
) AS clean,
ROUND((
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS cleanpercent,
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
) AS viruses,
ROUND((
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS viruspercent,
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
-- AND (isspam=0 OR isspam IS NULL)
-- AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS blockedfiles,
ROUND((
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
-- AND (isspam=0 OR isspam IS NULL)
-- AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS blockedfilespercent,
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS otherinfected,
ROUND((
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS otherinfectedpercent,
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS spam,
ROUND((
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS spampercent,
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
) AS highspam,
ROUND((
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highspampercent,
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
) AS mcp,
ROUND((
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS mcppercent,
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS highmcp,
ROUND((
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highmcppercent,
SUM(size) AS size
FROM
maillog
WHERE
date = CURRENT_DATE()
AND
()

has no one else used a Server 2016 AD domain with EFA AD Authentication? mine went belly up when i upgraded my domain from 2012R2 to 2016.

This could be a mailwatch bug. Have you tried upgrading to 3.0.1.8 to see if the problem resolves (after snapshotting and suspending mail flow)?

Also, have you tried using a full email address to login instead of just the username?

full email and just short username both log in successfully, but both formats generate the error above.

ill try updating and see what happens. i was also considering doing a rip and replace, but i hate to lose all my learning data from the database. is there a good way to export and import this data into a new install?