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

Create a technical support case if you need further support.

Generating a report that shows the group of a device in PolicyServer

    • Updated:
    • 1 Apr 2014
    • Product/Version:
    • Endpoint Encryption 3.1 PolicyServer
    • Endpoint Encryption 5.0
    • Platform:
    • Windows 2000 Advanced Server
    • Windows 2000 Datacenter Server
    • Windows 2000 Professional
    • Windows 2000 Server
    • Windows 2003 Datacenter
    • Windows 2003 Datacenter 64-bit
    • Windows 2003 Enterprise
    • Windows 2003 Enterprise 64-bit
    • Windows 2003 Server R2
    • Windows 2003 Standard
    • Windows 2003 Standard 64-bit
    • Windows 2003 Web Server 64-bit
    • Windows 2003 Web Server Edition
    • Windows 2008 Datacenter
    • Windows 2008 Datacenter 64-bit
    • Windows 2008 Enterprise
    • Windows 2008 Enterprise 64-bit
    • Windows 2008 Essential Business Server
    • Windows 2008 Server Foundation
    • Windows 2008 Server R2
    • Windows 2008 Standard
    • Windows 2008 Standard 64-bit
    • Windows 2008 Web Server Edition
    • Windows 2008 Web Server Edition 64-bit
Summary
The Enterprise Report can show which devices are not communicating to the PolicyServer. However, this report is very long and contains unnecessary information.
Learn how to use an SQL script to easily generate a report that shows which group a device belongs to.
Details
Public
To generate a report using a custom SQL script:
  1. Download and install Microsoft SQL Server Management Studio Express.
  2. Open the SQL Server Management Studio Object Explorer.
  3. Connect to the SQL Server hosting the Policy Server database files.
  4. Right-click the MobileArmorDB database, and then select New Query.
  5. Run the following SQL query command to generate a report consisting of the device group and its last sync time. This allows you to easily identify which systems report to a specific group.
    SELECT TOP 100 PERCENT dbo.MobileArmorGroup.GroupName, dbo.Device.DeviceName, dbo.Device.DeviceID, dbo.Device.LastSync
    FROM dbo.MobileArmorGroup INNER JOIN
    dbo.DevicesGroups ON dbo.MobileArmorGroup.GroupIndex = dbo.DevicesGroups.GroupIndex INNER JOIN
    dbo.Device ON dbo.DevicesGroups.DeviceIndex = dbo.Device.DeviceIndex
    where dbo.Device.LastSync < DATEADD(week, -2, GETDATE())
    ORDER BY dbo.MobileArmorGroup.GroupName, dbo.Device.DeviceName
    Note: To change the timing, adjust the second parameter of the "DATEADD()" function. For the command above, the "DATEADD()" function has the value of "-2" which means two (2) weeks ago.
The generated report contains the following:
  • GroupName
  • DeviceName
  • DeviceID
  • LastSyncTime
If the device belongs to a subgroup, the full path will not be listed. If there are duplicate group names in different subgroups, the script will not distinguish which subgroup the device belongs to.
For example, if there are two subgroups with same group name like the following:
  • Fixed Password -> DC -> GroupA - Device1
  • SSO -> DC -> GroupA - Device2
The script will show both Device1 and Device2 in GroupA and will not show the full path.
Premium
Internal
Rating:
Category:
Configure
Solution Id:
1095813
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.