MEM Tables

What information is saved to the SQL database for the MEM module during enrollment and profile deployment.

The SQL flow will be different depending on whether you use SEG, Powershell, or Google Apps for Work.

SEG & Powershell SQL Flow Diagram

graph TD D -->|1| AW AW[WS1 UEM] -->|2| D[Device] D -->|3| AW AW --> DB[(DB: 1,3)]
  1. The device enrolls in the VMware Workspace ONE. We save some information to the SQL database;
  2. A profile is pushed down to the device;
  3. Sample data is sent from the device. Data is saved to the SQL database.
    Note

    Usually by the time you run a SQL query, all of these steps have already occurred as this happens very quickly.

Google Apps for Work SQL Flow Diagram

graph TD D -->|1| AW AW --> DB[(DB: 1,1)]
  1. The device enrolls in VMware Workspace ONE. We only save password 1 if the customer is
    using Google Apps direct integration. If the customer is using Google Apps with password retention, we also save password 2 to the database.

Useful SQL Queries

You can see what information is saved in the SQL database by running SQL queries. Our primary SQL table is mobileEmailGateway.MEMDevice but we also use other tables including MobileEmailGateway.MEMDeviceActivity, mobileEmailGateway.EasDeviceType, and mobileEmailGateway.MEMConfigproxy to name a few. Each entry in the MEMDevice table relates to one email client. You can think of this table like a giant Microsoft Excel spreadsheet where MEMDeviceID is the row number.

List Information on MEMDevice

For our first query, we want to see the details about all the email clients associated with a specific device. For any SQL troubleshooting issue, this is generally the first query you will want to run.

select * From MobileEmailGateway.MemDevice (nolock) Where DeviceID = 637368

For this query to work, you will need to know what your DeviceID value is. You can find that value by going to the VMware AirWatch Console.

For the second query, we want to see all the email client entries in the SQL database. You might want to see this data after a client enrolls to troubleshoot various issues.

select * from mobileEmailGateway.MEMDevice where LocationGroupID = 8998

The above query returns information on each entry in the MEMDevice table where the location group is equal to 8998. To run this query, you only need to know the location group ID where the device enrolled. To find the location group ID, you can go to your OG and right-click the OG name and choose “Inspect”. From there you can see the OG group ID on the right. See the image below: The result of this query shows you the following information for each MEMDeviceID.

  • MEMDeviceID: You will have a different MEMDeviceID for each email client a user has. You need to have 3 things to have a new MEMDeviceID. DeviceID, EasDeviceIdentifier, and EmailAddress.
  • EasDeviceIdentifier: This is the Exchange ActiveSync ID. Each mail client will have a unique ID for this that can be used to identify the device on the Exchange Server.
  • DeviceID: This is the DeviceID the device received when it enrolled. Each device will have a unique ID that our infrastructure will use to manage the device in the console.
  • IsManaged: This value indicates whether or not the email client is managed or not by the VMware UEM console. This is different for IsEnrolled as that refers to the device, IsManaged is referring to the email client.
  • LocationGroupID: This is the ID of the location group where the user enrolled.
  • AgentReportedPackageName: The name of the email client. For native iOS, this value will always be “NULL”.
  • IsEnrolled: Indicates whether or not the device is enrolled in the VMware UEM. This is different for IsManaged as that refers to the email client, IsEnrolled is referring to the device.
  • IsCompromised: Indicates whether or not the device is jailbroken or rooted.
  • IsDataProtected: Indicates whether the device is encrypted or not.
  • IsModelCompliant: If you are using the managed device policy for Model (under Email\Compliance Policies), this indicates whether or not this device is compliant based on that.
  • IsOSCompliant: If you are using the managed device policy for Operating System (under Email\Compliance Policies), this indicates whether or not this device is compliant based on that.
  • IsMDMCompliant: If you are using any device compliance policies (under Devices\Compliance Policies\List View), this indicates whether or not the device is compliant based on that.
  • AccessState: Based on the evaluation of all of the compliance policies that might affect the device or email client, this indicates if the device is allowed or blocked.
  • DiagnosticsEnabled: Unknown.
  • DiagnosticsEnabledOn: Unknown.
  • EmailAddress: The email address associated with the email client.
  • EasMailboxIdentity: The returned Exchange ActiveSync mailbox identify if available.
  • EasMailboxDisplayName: The returned Exchange ActiveSync mailbox display name if available.
  • EasDeviceGUID: Unknown.
  • EasDeviceTypeID: The Exchange ActiveSync device type returned from the table mobileEmailGateway.EasDeviceType.
  • EasDeviceOS: Displays the operating system version information when available. This appears to usually be “NULL”. If you don’t find any data here, you will usually find this information in EasDeviceFriendlyName.
  • EasDeviceUserAgent: Usually “NULL” but may contain additional details about the email client when available.
  • EasDeviceFriendlyName: Displays model and version information.
  • EasDeviceIdentity: Usually “NULL” or 0. Uknown.
  • EasDeviceImei: Displays the devices IMEI information.
  • CreatedOn: The date the SQL records was created.
  • ModifiedOn: The last date the SQL record was modified.
  • EasProfileInstall: If you are using the managed device policy for Require ActiveSync Profile (under Email\Compliance Policies), this indicates whether or not a valid profile has been pushed. If a profile has never been pushed, this will be “NULL”. You will see a value of “1” if a valid profile has been pushed and a value of “0” if a profile has been removed.
  • LastDeviceStateChangeType: Unknown.
  • ResoruceId: Usually “NULL”. Unknown.

List Information on MEMDeviceActivity

If you are using Powershell, you might want to see what the status of the last Powershell command was after enrollment. In the case of SEG, you can use this to see if the last single device policy update failed or not. To do that, use the following query.

select * From MobileEmailGateway.MEMDeviceActivity (nolock) Where MemDeviceId = 927453

The above query returns information on each entry in the MeMDeviceActivityID table where the memDeviceID is equal to 927453. To run this query, you only need to know the memDeviceID. You can get this value by running the query from the “List Information on MEMDevice”.

The result of this query shows you the following information for each MemDeviceActivityID.

• MemDeviceActivityID: You will have a different MemDeviceActivityID for each activity transaction in the SQL database.
• MemDeviceId: You will have a different MemDeviceId for each email client a user has. You need to have 3 things to have a new MEMDeviceID. DeviceID, EasDeviceIdentifier, and EmailAddress.
• MemConfigId: When you create a new MEM configuration by going to Email > Email Settings > Add, a MEM configuration ID is created in the database. This field indicates the ID associated with this email client. • UserName: Unknown.
• EmailUserIdentity: The email address associated with the account.
• IPAddress: If we were able to determine the IP address for the device, it will be listed here.
• MailClientName: If we were able to determine the name of the mail client, you will see it listed here.
• DeviceAccessStateReason: If we are able to see the reason why the device is allowed or blocked, you will see it here.
• AllowReason: If the device is allowed, you will see the reason it is allowed here if available.
• BlockedReason: If the device is blocked, you will see the reason it is blocked here if available.
• LastGatewayServer: This will list the SEG server that was used last by the mobile email client.
• LastCommand: This will display the status of the last command we issued to the SEG or the Powershell endpoint.
• LastRequestDateTime: This will show the date that the email client last made a request.
• LastUpdate: This will display the last update response from the SEG or Powershell endpoint.

List Information on MEMConfig

select * From MobileEmailGateway.MEMConfig (nolock) Where MemConfigId = 8

The above query returns information on the MEM configuration where the MEM configuration ID is equal to 8. The result of the query shows you the following information for individual MEM configurations.

  • MemConfigId: This is the unique ID that each MEM configuration is assigned. This is an auto-generated value and is unique within an environment.
  • LocationGroupId: This is the Location Group Id where the MEM configuration is created.
  • DeploymentId: This column can hold two values. 1 corresponds to a Proxy configuration while 2 corresponds to a Direct configuration.
  • ProxyTypeId: Unknown.
  • EmailTypeID: The value in this column corresponds to the type of email infrastructure. Exchange = 2, IBM Notes = 5, Google = 7 .
  • EmailVersionID: This corresponds to the email server version. Example: Exchange 2010 = 4, Exchange 2013 = 5, Exchange 2016 = 6, Exchange Online = 7.
  • DisableCompliance: This column can hold two values: 0 = Email compliance engine is enabled, 0 = Email compliance engine is disabled. ◦ NOTE: This is not specific to the MEM configuration but is a property of the location group where the MEM configuration is present.
  • FriendlyName: This is the name given to the MEM configuration.
  • IsEnabled: This column can holds two values: 1 = MEM configuration is active/enabled, 0 = MEM configuration is inactive/disabled.
  • TestModeEnabled: This column holds two values. 1 = Test mode is enabled, 0 = Test mode is disabled. ◦ When Test mode is enabled, you can test Email Compliance Policies without affecting email access for your devices. All devices are allowed for email and the Email Dashboard reflects the expected access state results of your Email Compliance Policies on these devices. This only applies to SEG.
  • UseRecommendedSettings: This column hold two values: 1 = The MEM configuration is using the Recommend Settings under the Advanced Section. 0 = The adminstrator has overriden the ‘Use Default Settings’ option under Advanced Settings.
  • MEMconfigGuid: This is the unique ID assigned to the MEM configuration. This is separate from the MEMConfigId.