Sign In with your
Trend Micro Account
Need Help?
Need More Help?

Create a technical support case if you need further support.

SQL queries for generating Endpoint Encryption reports

    • Updated:
    • 20 Jun 2014
    • Product/Version:
    • Endpoint Encryption 3.0 PolicyServer
    • Endpoint Encryption 3.1 PolicyServer
    • Endpoint Encryption 5.0
    • Platform:
    • Windows 2003 Standard 64-bit
    • Windows 2008 Server R2
    • Windows 8 32-bit
    • Windows 8 64-bit
    • Windows Vista 32-bit
    • Windows Vista 64-bit
    • Windows XP SP3 32-bit
Summary
Use SQL queries if you are unable to run Enterprise Reports on the Policy Server.
Details
Public
The following are SQL queries commonly used in generating reports:
Last sync time for device:
USE MobileArmorDB
SELECT d.deviceid, d.devicename, d.lastsync
    FROM Device d
    INNER JOIN
    (SELECT d.deviceid, d.devicename, d.lastsync
        FROM Device d
        GROUP BY d.deviceid, d.devicename, d.lastsync
        HAVING COUNT(*) = 1) T
    ON d.deviceid = T.deviceid AND d.devicename = T.devicename AND d.lastsync = T.lastsync
Duplicate device names:
USE MobileArmorDB
SELECT d.deviceid, d.devicename
    FROM Device d
    INNER JOIN
    (SELECT d.deviceid, d.devicename
        FROM Device d
        GROUP BY d.deviceid, d.devicename
        HAVING COUNT(*) > 1) T
    ON d.deviceid = T.deviceid AND d.devicename = T.devicename
Last time users logged in:
USE MobileArmorLog
SELECT dbo.AuditLog.UserID,MAX(dbo.AuditLog.EventDateTime)AS "Last Time Logged in" from dbo.AuditLog, dbo.MessageRecord
WHERE ((dbo.AuditLog.MessageID=400001)OR(dbo.AuditLog.MessageID=104000)OR(dbo.AuditLog.MessageID>=100029 AND dbo.AuditLog.MessageID<=100032)OR(dbo.AuditLog.MessageID>=700001 AND dbo.AuditLog.MessageID>=700005)OR(dbo.AuditLog.MessageID>=900001 AND dbo.AuditLog.MessageID>=900009))
GROUP BY dbo.AuditLog.UserID
ORDER BY [Last Time Logged in]
List of deleted users:
USE MobileArmorDB
SELECT MobileArmorUser.UserID, MobileArmorUser.IsDeleted FROM [MobileArmorDB].[dbo].[MobileArmorUser]
WHERE MobileArmorUser.IsDeleted = 1
ORDER BY UserID ASC
Premium
Internal
Rating:
Category:
Configure; Troubleshoot; Deploy; Install; Update
Solution Id:
1103665
Feedback
Did this article help you?

Thank you for your feedback!

To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.

If you need additional help, you may try to contact the support team. Contact Support


To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.


Need More Help?

Create a technical support case if you need further support.