Admin Password Reset

Changing an administrator’s password

Changing an administrator’s password requires first getting the CoreUserID for that user, and then updating the password and password salt for that user in the database.

Getting the CoreUserID

Run the following SQL query to get the CoreUserID for the user.  Make sure to replace with the administrator’s username.  If more than one result are returned, make sure you identify the right entry.

Select * from CoreUser where UserName = '<USERNAME>';

Updating the password and password salt

The following query will update the administrator’s password.  Make sure to replace with the proper CoreUserID identified in the first section.  Have updating the password, log in with the account and reset the password to something else.

-- This Updates the password to 'a123b456c789'
UPDATE CoreUser SET Password = 'c2otxl1SURGxVibCX1K9IJvyizYl4ylnIIfXhwNtfe1iCuuVM8LNPK1oWWSwE3C3BB3AYxspGqrfXaVnryxjzw==' ,
PasswordSalt = '6eklCHP5ixaMT9RREfQbmi4Z2jc=' WHERE CoreUserID = <COREUSERID>
 
-- This Updates the password to 'Password123!'
UPDATE dbo.CoreUser SET Password = 'awhash4:IGdeYQ7eaHqyh3g6RelU0zkbHjoRW111/dg2xyqjiK0=:100000:K91XOTYtcw/20ZxDOUm7pe3DVLFA3XrzuUAYtWdl21M=' WHERE UserName = 'Administrator'

Account lock-out

Check IsLockedOut flag - it must be set to 0 for active/unlocked account.

UPDATE dbo.CoreUser SET IsLockedOut = 0 WHERE UserName = 'Administrator'
UPDATE dbo.CoreUser SET LastLoginAttempts = 0 WHERE UserName = 'Administrator'

APNs Troubleshooting

External links

• VMware KB article on APNs renewal - https://kb.vmware.com/s/article/2960965
• APNs for app renewal in on-prem environments - https://kb.vmware.com/s/article/50121242

APNs incorrect renewal

•  First you need to get the GroupID of the organization group that has APNs configured.  In Chrome, you can do this by right-clicking the organization group drop down as shown below and choosing Inspect Element.  In the HTML, search for data-current-lg and note the GroupID.
• Perform the following SQL query in the database to retrieve information about the APNs certificate:

SELECT DISTINCT DC.CertificateID, DAGS.appleid, DC.SubjectName,
                DC.serialnumber, DC.certificate thumprint,
                DC.notBefore, DC.NotAfter
FROM
      dbo.Certificate (NOLOCK) AS DC
      JOIN
            dbo.ApnsGenerationStatus (NOLOCK) as DAGS
            ON DC.CertificateID = DAGS.IssuedCertID
WHERE
      DAGS.LocationGroupID = 10289
ORDER BY NotBefore ASC

• Validate the results of this query.  In a correct renewal, both the appleid and SubjectName columns of the results should match between the latest certificate and the previous certificate.  You can tell which certificate is which based on the notBefore and notAfter columns, which match the validity dates of the certificate.

• If the appleid field is different, then the administrator used an incorrect Apple ID when renewing the APNs certificate, and so the SubjectName will not match.  The administrator must use the same Apple ID when renewing APNs, or all devices will lose the ability to communicate.  If appleid matches but SubjectName is different, then the administrator used the correct Apple ID but chose the wrong certificate it Apple’s portal to renew.  They must go back to Apple’s portal and renew the correct certificate.
• Note: All devices enrolled after the notBefore date of an incorrect APNs certificate must re-enroll when the certificate is corrected.  These devices will lose communication once the certificate is correct.
• Note: If the administrator previously cleared out the APNs certificate (by selecting the Clear button in the APNs settings page and saving), they will lose communication with all devices.  In this event, a database backup is required to restore the tokens, and this process generally cannot be completed on SaaS environments.

APNs Database Queries

Below are a list of helpful SQL queries specifically when troubleshooting APNs. All of the queries below are SELECT statements, which allows us to view the information but not update it.

1. This statement will display an overall view of the location group. This allows us to look at certain fields as well as the Location Group ID for the location where the APNs certificate is being uploaded. By inputting the name, you can receive the Group ID.

SELECT * FROM dbo.locationgroup lg (NOLOCK) WHERE lg.Name like '%%'

2. This statement displays the APNs settings at and above the LG provided. We are looking for these first four fields and which have values that are not NULL. It should only be configured at one level and inherited below. You simply need to input the group ID found in the first query.

SELECT lgf.LGlvl, lg.MDMAppleApplicationID, lg.MDMAppleDeviceProfileID, lg.AppleMDMEnabled, lg.MDMSettingsInheritable,lg.*
FROM dbo.LocationGroup lg (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON lgf.ParentLocationGroupID = lg.LocationGroupID
WHERE lgf.ChildLocationGroupID = ###
ORDER BY lgf.LGlvl DESC

3. This statement shows the APNs settings at or below the LG provided. You simply need to input the group ID found in the first query. The first four columns after the level should be NULL at every level below the level where the cert was uploaded at. You can also view the levels of the tree in the first column. If you do see multiple levels with values not being NULL, we can look at the different APNs certs and the number of devices associated. This will require some update statements which a DB or T3 Member can assist with.

SELECT lgf.LGlvl, lg.MDMAppleApplicationID, lg.MDMAppleDeviceProfileID, lg.AppleMDMEnabled, lg.MDMSettingsInheritable,lg.*
FROM dbo.LocationGroup lg (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON lgf.ChildLocationGroupID = lg.LocationGroupID
WHERE lgf.ParentLocationGroupID = ###
ORDER BY lgf.LGlvl ASC

4. This statement will display all the APNs certificates in the environment. You simply need to input the group ID found in the first query. It is important to match this with the previous query so we know which cert is at each level. It is also important to view if the topic is the same for different certs. If it is, we can change the application ID associated to the device without any end user interaction or effect. If the topics are different, devices will need to re-enroll. From this query, you will have the ProductionCertificateID and ApplicationID.

SELECT a.ProductionCertificateID, lg.LocationGroupID, lg.Name, a.*
FROM deviceApplication.Application a (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON a.RootLocationGroupID = lgf.ChildLocationGroupID
 JOIN dbo.LocationGroup lg (NOLOCK)
  ON lgf.ChildLocationGroupID = lg.LocationGroupID
WHERE lgf.ParentLocationGroupID = ###
 AND a.PackageID like '%com.apple.mgmt%'

5. This statement will confirm if the certificates are alive by viewing the date. You simply need to input the ProductionCertificateID found using the queries previous.

SELECT *
FROM dbo.Certificate c (NOLOCK)
where c.CertificateID IN (###)

6. This statement will tell the devices that are associated with the Application ID. You simply need to input the ApplicationID found using the queries previous. This will assist if the customer has multiple APNs certificates active in the console and you are trying to determine which to use. Obviously, we will want to choose the cert with the most devices for the least end user impact.

SELECT *
FROM deviceApplication.ApplicationDeviceCredential adc (NOLOCK)
WHERE adc.ApplicationId IN (###)

Condensed query:

SELECT adc.DeviceID, a.ApplicationID, a.PackageID, adc.NotificationID
FROM deviceApplication.ApplicationDeviceCredential adc
LEFT JOIN
deviceApplication.Application a
ON adc.ApplicationID = a.ApplicationIDWHERE DeviceID =

7. This statement will show the serial number associated with the APNs certificate. This will assist if you need to relay this information to Apple to determine if they have a corrupt certificate where AirWatch is unable to continue troubleshooting. Simply input the name of the Location Group where the APNs Certificate is uploaded.

SELECT lg.Name, c.SerialNumber from dbo.Certificate c
inner join deviceApplication.Application a
on ProductionCertificateID = CertificateID
inner join dbo.LocationGroup lg
on lg.MDMAppleAPplicationID = a.ApplicationID
where lg.Name like '%__%'

8. This statement will show you all the APNs certificates that have been uploaded to the console at or below the LG provided. You simply need to input the group ID found in the first query.

SELECT c.SerialNumber,c.SubjectName, c.NotBefore, c.NotAfter, ags.*
FROM dbo.ApnsGenerationStatus ags (NOLOCK)
JOIN dbo.Certificate c (NOLOCK)
ON c.CertificateID = ags.IssuedCertID
WHERE ags.LocationGroupID IN (###)
ORDER BY CreatedDate DESC

PushMagic Token

In Database:

SELECT *
FROM dbo.AppleDeviceEx
WHERE DeviceID = ###

APNs in logs

Console (CN) server logs:

• MessengingServiceLog (C:\AirWatch → Logs → Services) Search for:
• gateway.push.apple.com
• APNs token
• PushMagic token

Device Command Queues

Device operation sequence

A. AirWatch server sends out a PUSH notifications to device:

• AirWatch prepares command, stores in command queue (SQL)
• AirWatch sends check-in request to Messaging Service, one of the following:
  1. Google Cloud Messaging: formerly C2DM (Cloud 2 Device Messaging) deprecated 2012 →  Google Cloud Messaging (GCM) deprecated April 2019 → Firebase Cloud Messaging (FCM)
  2. Internal message bus for Android & Windows devices: AirWatch Cloud Messenger (AWCM)
  3. Message bus for iOS: Apple Push Notification Service (APNs)
• Messaging Service relays check-in request to managed device
• Managed device checks into MDM server
• MDM Server delivers commands to device, one-by-one

B. Device performs the commands; C. Device contacts AirWatch server to report the result of the last commands and request new commands, if any.

Command Queue

Every pending command is in SQL database:

SELECT * FROM deviceCommandQueue.DeviceQueue;

Every processed command is in SQL database:

SELECT * FROM deviceCommandQueue.DeviceQueueAudit;

Commands description is in SQL database:

SELECT * FROM deviceCommandQueue.DeviceQueueCommand;

Command statuses in SQL:

SELECT * FROM deviceCommandQueue.DeviceQueueStatus;

Basic command types & statuses…

Device Management commands

• Lock device
• Query device
• Send Message to device

Application commands

• Install
• Removal
• Settings

Profile commands

• Install
• Removal

Normal Command Status Flow 1 = Queued 2 = Pending 3 = Processed 7 = Held (if batching is enabled)

Troubleshooting Command Logs

Device Services (DS) server (front-end communication with managed device) logs:

• DeviceServicesLogs (C:\AirWatch → Logs → DeviceServices)
• InterrogatorQueueService (C:\AirWatch → Logs → Services)

Console (CN) server (communication with external services: APNs, GCM/FCM, AWCM, ACC etc.) logs:

• MessengingServiceLog (C:\AirWatch → Logs → Services)
• BulkProcessingServiceLog (C:\AirWatch → Logs → Services)
• SchedulerServiceLog (C:\AirWatch → Logs → Services)

Targeted logging: AirWatch console → Device Details → More → Targeted Logging

Device Status

Workspace ONE Registered – Unmanaged WS1 device (MAM Only) ManagedBy column in dbo.Device = 2 EnrollmentCategoryID column in dbo.DeviceExtendedProperties = 1 Query:

select d.ManagedBy, dep.EnrollmentCategoryID, d.DeviceID from dbo.Device d INNER JOIN
dbo.DeviceExtendedProperties dep
ON
d.DeviceID = dep.DeviceID
where d.ManagedBy = 2
AND dep.EnrollmentCategoryID = 1

Workspace ONE Managed – MDM on the device, but did not enroll through agent (Adaptive Mgmt) ManagedBy column in dbo.Device = 1 EnrollmentCategoryID column in dbo.DeviceExtendedProperties = 1 Query:

select d.ManagedBy, dep.EnrollmentCategoryID, d.DeviceID from dbo.Device d INNER JOIN
dbo.DeviceExtendedProperties dep
ON
d.DeviceID = dep.DeviceID
where d.ManagedBy = 1
AND dep.EnrollmentCategoryID = 1

Workspace ONE Managed – MDM on the device, but did not enroll through agent (Direct Enrollment) ManagedBy column in dbo.Device = 1 EnrollmentCategoryID column in dbo.DeviceExtendedProperties = 3 Query:

select d.ManagedBy, dep.EnrollmentCategoryID, d.DeviceID from dbo.Device d INNER JOIN
dbo.DeviceExtendedProperties dep
ON
d.DeviceID = dep.DeviceID
where d.ManagedBy = 1
AND dep.EnrollmentCategoryID = 3

Agent Enrollment with Workspace ONE – Workspace ONE as an app catalog, pushed as a managed app. ManagedBy column in dbo.Device = 1 EnrollmentCategoryID column in dbo.DeviceExtendedProperties = NULL or 0 Query:

select d.ManagedBy, dep.EnrollmentCategoryID, d.DeviceID from dbo.Device d INNER JOIN
dbo.DeviceExtendedProperties dep
ON
d.DeviceID = dep.DeviceID
where d.ManagedBy = 1
AND dep.EnrollmentCategoryID IN (NULL, 0)

EnrollmentCategoryID is mapped to following enum:

public enum EnrollmentCategory
{
    Unknown = 0,
    VmWorkspace = 1,
    Offline = 2,
    WorkSpaceOneDirectEnrollment = 3
}

ManagedBy is mapped to following enum:

public enum DeviceManagedBy
{
    /// <summary>
    /// Unknown value
    /// </summary>
 
    Unknown = 0,
 
    /// <summary>
    /// Managed By MDM
    /// </summary>
 
    MDM = 1,
 
    /// <summary>
    /// Managed by Workspace (newer terminology is 'Container')
    /// </summary>
 
    Workspace = 2,
 
    /// <summary>
    /// Managed by Application Catalog (Web MAM enrollment)
    /// </summary>
 
    AppCatalog = 3,
 
    /// <summary>
    /// Managed by Application
    /// </summary>
 
    AppLevel = 4,
 
    /// <summary>
    /// managed by VmWorkspace
    /// </summary>
 
    VmWorkspace = 5,
 
    /// <summary>
    /// managed by Offline (Enrolled Offline)
    /// </summary>
 
    Offline = 6
}

ENSv2 Database

ENS Database Schema

The ENS system leverages Amazon RDS(SQL Server) instance for its data persistence needs and makes use of 3 main tables.

EnsSubscriptionInfo

Keeps all user records with keys needed for decryption of url callbacks and device tokens to send messages to

APITokens

Keeps all valid API tokens for authenticating with the API

EWSUrlCache

Security

For security, apart from leveraging best practices defaults followed by Amazon on their RDS instances, the following has been enforced to protect customers data and integrity of the system:

1. Database never contain user’s emails or passwords. All identification is done via the userId that gets matched to a public/private key pair
2. Database access by ENS machines is limited via a single AWS IAM account with limited role, it has read/write records permission but no database/table level access.

API/Services Client Authentication

Authentication by clients is done via API keys created by us. The API token is sent as a header parameter (key: “ApiToken”) for all outgoing connections and authenticated by ENS server by checking against a table of authorized API keys. The api key is provided to the clients via a configuration pushed down by the console. Each VMware/Airwatch client is provided 1 API key for all their devices.
API Keys can be revoked and invalidated as explained in the API Key Revocal/Refresh section.

Boxer client activation via console

There are some keys that can be configured via console to activate the feature on Boxer iOS. These are described in Managed App Configuration

API/Services Provided by ENS Server to client devices.

Alive: Used to determine if ENS the service is up and running.

Get Public Key:

Gets a public key from the server.

Register Device V2:

Register devices so ENS starts monitoring its email Inbox Task: REQUEST/POST Endpoint: /registerdevicev2 Description: Based on credentials passed, ENS should now autodiscover what is the EWS endpoint it needs to subscribe to for push notifications. Upon receiving a success it will update the registered column in the DB to true and the endpoint as well. Any errors should be reported to the client. Note: We can force a registerdevicev2 call to ignore current user status (including Subscribed) and force a resubscription by putting a “force” flag equal to 1 in the payload Params:

userid = sha256Hash( lowercase(email))

email = email address of the user to aid in autodiscovery.

*creds = {
apnsToken: <device apns token>,
ewsUrl: <EWS endpoint url>,
**oauthAccessToken: <oauth access token>,
**userName: <username for account>,
**password: <password for accounts>,

**CBAData: {
certificate: <pkcs12 certificate>
password: <certificate password>
}
}

bundleid = string with application id
exchVersion = integer specifying which exchange version is to be used first on autodiscovery
force = parameter that forces the server to ignore the users current status (subscribed) and redoes the registration completely
defaultSound = string with soundname to be returned back on the notification payload.
ewsDeviceId = Eas device identifier which will be sent to SEG to evaluate the policies, depending on allowed status we will send the notification to device.

*parameter is encrypted with publickey and encoded in base 64. base64(RSAEncryptWithKey(creds))  
**optional values depending on authentication type

Response:

{  
responseCode = <response_code>
}

Unregister:

Tells the server that we want to remove a specific device from using push notifications. Task: REQUEST/POST Endpoint: /unregister Description: Based on userId and device token passed ENS will update corresponding record.

1. Get record and list of devices
2. Remove device, and Update Record
3. If its last device on list unregister from Exchange push subscription, and delete record Params:

userid = sha256Hash( lowercase(email))  
token =  
base64(RSAEncryptWithKey(apnstoken, ensPublicKey))

Response:

{  
responseCode = <response_code>
}

Update Device:

Task: REQUEST/POST Endpoint: /updatedevice Description: Based on userId and device token passed ENS will update corresponding record.

1. Get record and list of devices
2. Replace old token for new one
3. Update Record or delete record Params:

userid = sha256Hash( lowercase(email))  
token =  
base64(RSAEncryptWithKey(apnstoken, ensPublicKey))

Response:

{  
responseCode = <response_code>
}

### Get Subscription Status V1 and V2:
This service let's us "checkin" with ENS to keep our subscription active as well as help clients know if they need to resubscribe. This is also helpful for debugging purposes
Task: REQUEST/POST
Endpoint: /getsubscriptionstatus
Description: Based on userId passed, ENS checks what is the corresponding status for that account
Params:
```json
userid = sha256Hash( lowercase(email))  
lastMessageDate = string date "MM/dd/yyyy HH:mm:ss.SSS"

Endpoint: /getsubscriptionstatusv2 Description: Based on userId-devicetoken passed, ENS checks what is the corresponding status for that account. This was introduced so we could have more granularity and be able to better cleanup “stale” records for users who may not be using device anymore Params:

userid = emailhash string  
*subscriptionPayload = {  
pushToken: <apnsToken>  
lastMessageDate: <string date "MM/dd/yyyy HH:mm:ss.SSS"> ignoreLastMessageDate: <1 or 0>
}

*parameter is encrypted with publickey and encoded in base 64. base64(RSAEncryptWithKey(subscriptionPayload))  
= base64(RSAEncryptWithKey(apnstoken, lastMessageDate)) note: lastMessageDate format = "MM/dd/yyyy HH:mm:ss.SSS"

Update Sync Key:

Register devices so ENS starts monitoring its email Inbox Task: REQUEST/POST Endpoint: /updatesynckey Description: Based on information sent, we update the background sync key and expiration time of the system which is sent down to the device with each push notification. Params:

userid = sha256Hash( lowercase(email))

*info = {
apnsToken: <device apns token>,
backgroundSyncKey: <new background sync key>,
syncKeyExpirationTime: <expiration time of sync key in UTC "MM/dd/yyyy HH:mm:ss.SSS">
}  

*parameter is encrypted with publickey and encoded in base 64. base64(RSAEncryptWithKey(info))

Extra Header parameters:

On each request we can send some extra parameters which influences how ENS handles the subscriptions as well.

1. badge - Whenever we send this on the header of a request (with value 1) we know that this device needs to be badged and hence ENS will include the badge on every notification that it sends down
2. development - This header needs to be included (with value 1) to know that this request is coming from a development device/build. This helps in sending the push notification to the correct push notification environment among other things we do server side that are special to development devices.

Response Codes for applicable services:

• UserSubscriptionNotFound
• UserRecordPresentNotSubscribed • UserAlreadySubscribed
• UserUpdated
• UserSubscribed
• DeviceUnregistered • UpdateSuccess • UpdateFail

API Key Revocal/Refresh:

ENS server machines keep a cache of all API keys to speed up the process of validating every single call they receive. They refresh their keys on a schedule so that we can confidently remove api tokens and make them unusable

Encryption:

The ENS system uses asymmetric key encryption to keep all customer data transferred safe and invulnerable to various types of malicious attacks. Specifically, we use RSA Encryption to encrypt any crucial data passed around from client/server (see API/Services section for details).

A public key is created per user and is provided to the client upon authenticated request. Using this key, RSA encryption following PKCS1 standards needs to be performed on the client side for any services requiring it.

GPS History Table

• GPS Poll Time Interval - X mins (configured in AirWatch Console, for example, in Android Agent settings)
• Data Transmit Interval - Y mins

Privacy settings for “GPS Data” needs to be set to Collect and Display for required device Ownership type.

The samples which are transmitted by the Agent as per the Y interval are all stored in the database in the dbo.GPSLog table. To see if any samples have been reported for your specific device, use the following query:

SELECT * from dbo.LogSample (nolock) ls
inner join dbo.GPSLog (nolock) gl
on ls.logsampleID = gl.logsampleid
WHERE deviceid = ####

Under device details > Location tab on console, not all location data points will be reported:

• Consecutive duplicate samples will not be reflected
  Note

  Consecutive samples which are not at least 100 ft. apart will not be listed corresponding to the particular time-stamp as can be seen in the DB.

Insert GPS History directly

Copy GPS coordinates from one device to another:

select * from device where Name like '%User01%' --deviceID = 7
select * from device where Name like '%User02%' --deviceID = 52
  
Insert dbo.LogSample ([DeviceUid] ,     [IpAddress] ,[SampleTime],[TransmitTime] ,[DeviceID] )
select      
--[LogSampleID] [int] IDENTITY(1,1) NOT NULL,
             [DeviceUid] ,[IpAddress] ,[SampleTime],[TransmitTime] ,
             52--[DeviceID] ,
       from dbo.LogSample  ls where deviceId = 7 and LogSampleID =8
  
  
select *  from dbo.LogSample  ls where deviceId = 52 --LogSampleID =9
  
Insert dbo.GPSLog ( [LogSampleID] ,[SampleTime],[Latitude] ,[Longitude] ,[Elevation] ,[Speed] ,
[Note] ,[LatitudeInternal] ,[LongitudeInternal] ,[ElevationInternal] ,       [SpeedInternal]
--,    [RowVersion]
                    )
       select 9 ,--[LogSampleID] ,      
             [SampleTime],[Latitude] ,[Longitude] ,  [Elevation] ,[Speed] ,[Note] ,[LatitudeInternal] ,[LongitudeInternal] ,     [ElevationInternal] ,     
[SpeedInternal] --, [RowVersion]
       from dbo.GPSLog  gl where LogSampleID in (select  LogSampleID from dbo.LogSample  ls where deviceId = 7and LogSampleID =8)

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.

Memcached Activation

Activate usage of Memcached

DECLARE @myid uniqueidentifier
DECLARE @GroupOverrideID int
DECLARE @userID int
DECLARE @CacheEndpoint varchar(255)
DECLARE @EnabledOverrideID int
DECLARE @SolutionOverrideID int
DECLARE @EndpointOverrideID int
SET @myid = NEWID()
SET @userID = 52
SET @CacheEndpoint = '[
  {
    "name" : "mc_node1",
    "address" : "192.168.1.1",
    "port" : "11211"
  },
  {
    "name" : "mc_node2",
    "address" : "192.168.1.2",
    "port" : "11211"
  }
]
'
EXEC SystemCodeGroupOverride_Save @GroupOverrideID, 132, 7, NULL, 1, 1, 1, @UserID
IF (@GroupOverrideID IS NULL)
BEGIN
    SELECT TOP (1) @GroupOverrideID =  scgo.SystemCodeGroupOverrideID
    FROM dbo.SystemCodeGroupOverride scgo
    WHERE [SystemCodeGroupID] = 132
    AND LocationGroupID=7
    AND LocationID IS NULL
END
EXEC SystemCodeOverride_Save @EnabledOverrideID, 877, @GroupOverrideID, 'True', @userID
EXEC SystemCodeOverride_Save @SolutionOverrideID, 4147, @GroupOverrideID, '0', @userID
EXEC SystemCodeOverride_Save @EndpointOverrideID, 829, @GroupOverrideID, @CacheEndpoint, @userID
UPDATE dbo.DatabaseVersion set buildkey = CONVERT(varchar(255), @myid)

• Login into https://localhost/AirWatch/#/AirWatch/Installation/CacheAdmin and verify if cache is enabled.
• Deactivate usage of Memcached:

DELETE from dbo.SystemCodeOverride where SystemCodeid in (829,877,4147)
DELETE from dbo.SystemCodeGroupOverride where SystemCodeGroupID = 132

Check Memcached in SQL

SELECT * from dbo.SystemCodeOverride where SystemCodeID IN (829 , 827, 877, 4147)

Query Internal Apps

Check app versions

Find out the list and version of applications installed on the device by running the following SQL query:

select * from interrogator.applicationlist al
inner join interrogator.application a
on a.applicationid = al.applicationid
where al.deviceid = #affecteddeviceid

How to verify if devices have installed the latest provisioning profile for an internal application?

Search for devices who are reporting the application as installed, but do not have the updated provisioning profile installed

SELECT * FROM interrogator.ApplicationList (NOLOCK)
WHERE DeviceID in (
    SELECT iaa1.DeviceID From deviceApplication.InternalAppAssignment (NOLOCK) iaa1
    WHERE InternalAppID = {INTERNAL_APP_ID}
    AND iaa1.DeviceId NOT IN (
        SELECT iaa.DeviceID FROM deviceApplication.InternalAppAssignment (NOLOCK) iaa
        JOIN deviceProfile.DeviceProfileDevicePool dpdp
        ON iaa.DeviceID = dpdp.DeviceID
        WHERE iaa.InternalAppID = {INTERNAL_APP_ID}
        AND dpdp.DeviceProfileID = {DEVICE_PROFILE_ID}
    )
)
AND ApplicationID = {APPLICATION_ID}
AND IsInstalled = 1

The logic behind this script is:

• Check for devices reporting the provisioning profile as installed (deviceProfile.DeviceProfileDevicePool)
• Check against devices that are supposed to be assigned this application (deviceApplication.InternalAppAssignment)
• Check against devices that are reporting the application as installed (interrogator.ApplicationList)

To find the variables in this query:

• INTERNAL_APP_ID - This is found in the deviceApplication.Application database table, or in the URL when viewing the summary page for the internal application.
• DEVICE_PROFILE_ID - This is found in the deviceApplication.Application table as the ProvisioningDeviceProfileID.
• APPLICATION_ID - This is found in the interrogator.Application table as the ApplicationID.  To find this value you can use the following query:

SELECT ia.* FROM interrogator.Application (NOLOCK) ia
JOIN DeviceApplication.Application daa
    ON ia.Identifier = daa.PackageID
    AND ia.VersionHash = daa.VersionHash
WHERE daa.ApplicationID = {INTERNAL_APP_ID}

Verify if commands are in the command queue to update the provisioning profile

The following query can be used to identify if, for devices that have not yet installed the updated profile, commands have been queued to install it on the next device check in:

SELECT * FROM DeviceCommandQueue.DeviceQueue (nolock) dq
JOIN deviceProfile.DeviceProfileVersion dpv
    ON dq.DeviceProfileVersionID = dpv.DeviceProfileVersionID
WHERE dq.CommandID = 13 --CommandID for InstallProvisioningProfile
AND dpv.DeviceProfileID = {DEVICE_PROFILE_ID}

Search for application upload events

The following query is generated by HUB -> Reports and Analytics -> Events -> Console Events in the console, but the console only searches for Last Month, while manual procedure allows to search further back

exec eventLog.EventLog_Search @LocationGroupID=571,@SeverityID=NULL,@eventGroupTypeID=2,@Module=N'Apps',@StartDateTime='2018-06-01 15:32:34.843',@EndDateTime='2018-07-27 15:33:34.843',
@EventDirectionID=NULL,@EventTypeID=0,@EventActionID=0,@StartRowIndex=50,@MaximumRows=1000,@SortExpression=NULL,@SortAscending=1,@SearchText=NULL

Smart Groups Queries

SQL queries for Smart Groups

List all the smart groups present in that particular environment:

select * from smartgroup.smartGroup

Current version of the smart group being used can be found in the table generated by the query below:

select * from smartgroup.SmartGroupVersion where sgdm.SmartGroupId = ##

Details of devices associated with a smart group:

select * from SmartGroup.SmartGroupDeviceMap sgdm Inner Join smartgroup.SmartGroupVersion sgv on sgdm.smartGroupId = sgv.smartGroupID
and sgdm.SmartGroupVersion = sgv.SmartGroupVersion where sgdm.SmartGroupId = ##

Relationship between smart group and entity can be found from the table generated by the query mentioned below:

select * from smartGroup.AWEntitySmartGroupAssignmentMap where SmartGroupId = ##

The device ID of device to which the entity is assigned can be obtained from the tables generated by the queries mentioned below:

• Profile
• InternalApp
• ExternalApp

select * from deviceProfile.ProfileAssignment where ProfileID = ##
select * from [deviceApplication].[InternalAppAssignment] where InternalAppID = ##
select * from [deviceApplication].[ExternalAppAssignment] where ExternalAppID = ##

Devices associated with a smart group can be seen in the table generated by the query mentioned below:

select batchEvaluateLogic from smartGroup.smartGroupRuleLogic where smartGroupOverrideID = ##

Troubleshooting Smart Groups

Problem 1:

• After assigning a smart group to the application and selecting Save & Publish , the app assignment in application grid shows 0/0/0 even though the View Device assignment shows all devices in smart group.

Go to Deployment tab and check the deployment time and if possible change it to 12.00 AM same day. If the time is already set at 12.00 AM, check the admin user’s time zone and compare it with OG time zone. Also check if app wrapping is enabled and whether it is still in progress or not completed.

Problem 2:

• When a smart group is attempted to be deleted, the following error appears: " Deletion is unsuccessful. This smart group is currently being used in assignment. Please remove the smart group from the assignment, and try again."

Check if the smart group is currently assigned to any app, profiles, compliance etc. Check if there is any user group which is having the same name as the smart group which the smart group uses.

Problem 3:

• Certain apps are not being shown in App Catalog for some users.

Create a new smart group which contains these users. After that edit the app and add this smart group in exclusion list.

SQL Recovery

General

VMware AirWatch Installation Guide (9.2), page 17 recommends Recovery Model = Full.

Also, in AirWatch SQL FAQ we see a comment: When trying to login to the AirWatch console or perform actions within the console you may receive the error message “The transaction log for database ‘AirWatch’ is full.”

This typically means that there is no available space on your SQL server. The common cause for this is backups not being deleted even though newer backups are being done, or lack of transaction log backups. To fix this issue you should first consult your local database admin and make them aware of the issue. The fix that we recommend is to perform a full database backup and then switch the database to simple recovery mode. Your database admin should be familiar with this and know how to perform these actions.

Shrinking the Log File When Disk Space is Full

The log file cannot be shrunk until it is truncated and free space is available. If a log file has grown to the point that it is full or consuming all available disk space, truncate it immediately in order to restore normal database operations. (If the log file has reached the maximum size database property and free disk space is still available, increase the value of maximum size property or set it to ‘unlimited’. A shrink operation should not be required in this case.)

To truncate a log file immediately, set the recovery model to Simple then shrink log file. The following commands are examples of how to do this. They may require editing to tailor them to the customer’s environment.

-- look up the name of the log file
 SELECT name as log_file_name FROM sys.database_files WHERE type_desc='LOG'
ALTER DATABASE [AirWatch] SET RECOVERY SIMPLE; 
DBCC SHRINKFILE ('AirWatch_log',0,TRUNCATEONLY);

-- replace 'log_file_name' with the actual name of the log file
 ALTER DATABASE [AirWatch] MODIFY FILE ( NAME = AirWatch_log, SIZE = 10GB , FILEGROWTH = 128MB );
ALTER DATABASE [AirWatch] SET RECOVERY FULL;

-- Full backup
 BACKUP DATABASE [AirWatch] TO DISK = 'E:\MSSQL\Backup\AirWatch_20160123_full.bak' WITH STATS; 

Setting the recovery model to Simple erases all of the transaction log records. For this reason, it is essential to create a full backup of the database immediately afterward the shrink procedure to ensure full data recoverability in the event of a database failure.

Preventative Measures

Make sure that the regular database and transaction log backups are scheduled for the AirWatch database. There are various methods: SQL scripts, third party tools, or SQL Server maintenance plans, which can be used.

Full Recovery Mode caveats

Generally the reason why DB log file is large is because the required DB maintenance has not been implemented. Typically, the size of the transaction log file stabilizes when it can hold the maximum number of transactions that can occur between transaction log truncations that are triggered by either checkpoints or transaction log backups.

What Scenarios can cause the Log to Keep Growing?
There are many reasons, but usually these reasons are of the following two patterns

Recovery process overview… In SQL Server, there are three recovery models - Full, Bulk-Logged and Simple. We’ll ignore Bulk-Logged hybrid model: most people who are in this model are there for a reason and understand recovery models. The two we care about are Simple and Full.

Before we talk about Recovery Models - Let’s talk about recovery in general.
The transaction log file is there for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation It is the log’s job to say “hey.. this never really finished, let’s roll it back” during recovery. It is also the log’s job to see that you did finish something and that your client application was told it was finished (even if it hadn’t yet hardened to your data file) and say “Hey.. this really happened, let’s roll it forward, let’s make it like the applications think it was” after a restart. Now there is more but that is the main purpose.

The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an “oops” in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn’t always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.

Simple Recovery Model

In this model, you are telling SQL Server - I am fine with you using your transaction log file for crash and restart recovery (You really have no choice there.. Look up ACID properties and that should make sense quickly), but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file.

SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.

Full Recovery Model

With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available, or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.

If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in simple until you switch to Full Recovery Model AND Take your first Full Backup.

So, that’s the most common reason for uncontrolled log growth: Being in Full Recovery mode without having any log backups.

Upgrade Rollback

The rollback process contains two phases.  First, a backup of the database (that was taken prior to the upgrade) is restored and configured.  Next, either backups of any application servers (Console, Device Services, etc) are applied, or the previous version of the AirWatch application is simply reinstalled on those servers after uninstalling the current version.

Restoring a database backup

The following steps can be used to restore a database backup to an existing database server and apply the necessary configuration changes.  This process assumes that a backup has been created prior to any upgrades, when the environment was fully functional.

• Before working with the database, make sure that, for any AirWatch application servers (Console, Device Services, etc),  all AirWatch services are stopped.  Additionally, stop the World Wide Publishing Service.  Finally, make sure that IIS is stopped as well.
• Open Microsoft SQL Server Management Studio, right-click on Databases and select Restore Database.
• Under the General tab on the left and within the Source for restore section, select From device, then select ‘…’ button.
• From the Specify Backup page, select Add. Locate the backup, select it, and click OK. The database backup will display on the Specify Backup page. Click OK.
• Select the Restore check box.
• Under Destination for restore, select the To database drop-down list and select the AirWatch database name. Note: This should be the last database in the list, in the event you have multiple backups all named AirWatch and are unsure of which one to select. Click OK to start the database restoration.

On the old database: Next you need to note the user permissions of the old database’s AirWatch SQL Service Account. To do this:

• Open Microsoft SQL Server Management Studio
• Navigate to Security > Logins > to locate your DB User in the Object Explorer, and then right-click and choose Properties.
• Navigate to the Server Roles tab. Write down the roles listed
• Select User Mapping. Write down the user mappings listed and the role membership permissions

On the new database:

• Delete the AirWatch SQL Service Account, which was created when you restored.
• Create the new AirWatch SQL Service Account. To do this navigate to Security > Logins,  right-click, and select New Login.

•     Enter the following:

A.   Select whether to use Windows or SQL Server authentication. For SQL Server authentication, enter your user credentials. Note: The username needs to exactly match the username of the old database.

B.   Uncheck Enforce password policy.

C.   Select the AirWatch database as the Default database.

D.   For Server Role, enter the roles you noted previously.

E.   For User Mapping, enter the user mappings and permissions you noted previously. IMPORTANT: This should include all of the permissions that you copied for AirWatch, master, model, msdb, and tempdb.

Next you need to migrate any AirWatch-related jobs.

On the old database:

• Navigate to SQL Server Agent > Jobs, right-click <AirWatch_DB> - Purge Expired Sample Data, and select Script Job as > CREATE To > New Query Editor Window.
• Save as the query.
• Transfer the query to your new database.
• On your new database: Execute the query.

On the new database: Perform a test query. For example, one for device count, to ensure proper functioning. To do this:

• Right-click on the AirWatch database under Databases and select New Query.
• Enter the query as shown below.

• Select Execute.

Rename the old database, for example, to AirWatch_OLD. To kill all connections and rename the database, run the following script, replacing ‘AirWatch’ with the name of your old AirWatch DB and AirWatch_OLD with what you would like to rename the old database to.

Restoring a previous version of the application

With the database backup restored, the next step is to restore the proper version of the application.  Ideally, there will be a snapshot or backup of each application server from the same time as the database backup.  In this case, simply restore these backups in order to restore functionality.

However, if there are no available snapshots or backups of the servers, then the previous version of the AirWatch software must be reinstalled.

In these cases, identify which application servers have an updated version of the application (this will likely be all application servers, but may only be a subset of them).  For each of these, ensure that a copy of the AirWatch installer for the correct version has been installed on the server. Uninstall the current version of the software through Computer > Uninstall or change a program by selecting the AirWatch application.  With this uninstalled, open previous version of the AirWatch installer and proceed as normal.  When configuring the database connection, ensure the configurations used match those of the restored database.

Finally, when both the database and application has been restored properly, ensure that IIS and all AirWatch services (as well as the World Wide Web Publishing service) are properly started.

Changing the database connection string

In some cases, it may be necessary to change the database connection string of an application server without fully reinstalling the software.  For example, if a database migration has occurred, the name of the database may have simply changed.  Perform the following steps to update the database connection string on each AirWatch application server.  Note that this must be done on every application server so that they are pointing to the new database.

Steps:

• Navigate to AirWatch Root Folder on the application server.
• Navigate to AirWatch X.X\Supplemental Software\Tools\UpdateSQLServerInfo.
• Launch UpdateSQLServerInfo.exe.
• Update the Server Hostname, Database Name, Username and Password. If Windows authentication is being used, the password field may be blank.
• Make sure to restart IIS and all AirWatch services on each server after updating the SQL connection string.

Useful SQL Queries

SQL Database Health

Index Fragmentation Audit

This query provides you with the index fragmentation % for every table in the DB.

/**************************************************************
   You can use the following script to determine index 
   fragmentation by table. This can help to determine 
   that the database is causing performace problems.
**************************************************************/ 

SELECT OBJECT_NAME(object_id), index_id, index_type_desc, index_level, avg_fragmentation_in_percent, avg_Page_space_used_in_percent, page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'AirWatch'), null, null, null, 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

Table Size Audit

This query will provide physical sizing information of all the tables in the database.

/**************************************************************
    This query can help you determine what table is taking
    up the most disk space and potentially what tables have
    too many rows.
**************************************************************/ 

--If the temp table exisits drop temp table
IF EXISTS ( SELECT * FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'#Sizes') 
     AND type in (N'U') ) 
     BEGIN 
     DROP TABLE #Sizes; 
     END 

--Create temp table, #Sizes 
CREATE TABLE #Sizes 
( 
--Table name 
     table_name nvarchar(255), 
--Number of rows 
     table_rows char(11),
--Physical space table is using
     table_reserved varchar(18),
--Phyiscal space table data is using
     table_data varchar(18),
--Physical space idexes are using
     table_index_size varchar(18),
--Physical space reserved
     table_unused varchar(18)
)

EXEC sp_MSforeachtable @command1='INSERT #Sizes (table_name, table_rows, table_reserved,
table_data, table_index_size, table_unused) EXEC sp_spaceused ''?'''
--This query orders the results by actual phyisical table size
SELECT * FROM #Sizes ORDER BY CAST ( SUBSTRING (table_data, 0, LEN(table_data) - 2) AS InT) DESC
--This query orders the results by row count
--SELECT * FROM #Sizes ORDER BY CAST (table_rows AS int) DESC

Table Row Count Only

A more efficient script to get table row counts.

/**************************************************************
    This is a more efficient way to get Row Counts
    but will not include any Physical Sizing data
   
**************************************************************/ 

SELECT 
     sc.name +'.'+ ta.name TableName
     , SUM(pa.rows) RowCnt
FROM 
     sys.tables ta
     INNER JOIN sys.partitions pa
          INNER JOIN sys.schemas sc
          ON ta.schema_id = sc.schema_id
WHERE 
     ta.is_ms_shipped = 0 
      AND pa.index_id IN (1,0)
GROUP BY 
     sc.name
     , ta.name
ORDER BY 
     SUM(pa.rows) DESC;

Database IO Stalls

You can use this query to see which queries are causing IO stalls.

/**************************************************************
   The table valued dynamic management function, 
   sys.dm_io_virtual_file_stats provides a breakdown of SQL 
   Server reads, writes, and io_stalls for a particular
   database or transaction log file. IO_stalls is the total 
   cumulative time, in milliseconds, that users waited for
   I/O to be completed on the file since the last restart    of SQL Server. 
**************************************************************/ 

SELECT
     DB_NAME(fs.database_id) AS [DB Name]
     , fs.file_id
     , mf.physical_name
     , io_stall_read_ms
     , num_of_reads
     , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10 , 1)) AS 'avg_read_stall_ms'
     , io_stall_write_ms
     , num_of_writes
     , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10 , 1)) AS 'avg_write_stall_ms'
     , io_stall_read_ms + io_stall_write_ms AS io_stalls
     , num_of_reads + num_of_writes AS total_io
     , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
          + num_of_writes ) AS NUMERIC(10 ,
          1)) AS 'avg_io_stall_ms'
FROM
     sys.dm_io_virtual_file_stats(NULL , NULL) AS fs
INNER JOIN sys.master_files AS mf
          ON fs.database_id = mf.database_id
          AND fs.[file_id] = mf.[file_id]
ORDER BY
     avg_io_stall_ms DESC ;
GO

Identify Expensive Operations

/**************************************************************
   This query provides you with operations that are expensive
   from a database standpoint. This query is useful in
   determining what is causing performance problems on a server
**************************************************************/ 

SELECT TOP 25
     DB_NAME(qp.[dbid]) AS dbname
     , qp.[dbid]
     , qp.objectid
     , qp.number 
     --, qp.query_plan 
     --the query plan can be *very* useful; enable if desired 
     , qt.[text]
     , SUBSTRING(qt.[text] , 
          ( qs.statement_start_offset / 2 ) + 1 ,
          ( ( CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
     END - qs.statement_start_offset ) / 2 ) + 1) 
         AS statement_text
     , qs.creation_time
     , qs.last_execution_time
     , qs.execution_count
     , qs.total_worker_time / qs.execution_count 
         AS avg_worker_time
     , qs.total_physical_reads / qs.execution_count 
         AS avg_physical_reads
     , qs.total_logical_reads / qs.execution_count 
         AS avg_logical_reads
     , qs.total_logical_writes / qs.execution_count 
         AS avg_logical_writes
     , qs.total_elapsed_time / qs.execution_count 
         AS avg_elapsed_time
     , qs.total_clr_time / qs.execution_count
     nbsp;   AS avg_clr_time
     --, qs.total_worker_time 
     --, qs.last_worker_time 
     --, qs.min_worker_time 
     --, qs.max_worker_time 
     , qs.total_physical_reads
     , qs.last_physical_reads
     , qs.min_physical_reads
     , qs.max_physical_reads 
     --, qs.total_logical_reads 
     --, qs.last_logical_reads 
     --, qs.min_logical_reads 
     --, qs.max_logical_reads 
     --, qs.total_logical_writes 
     --, qs.last_logical_writes 
     --, qs.min_logical_writes 
     --, qs.max_logical_writes 
     --, qs.total_elapsed_time 
     --, qs.last_elapsed_time 
     --, qs.min_elapsed_time 
     --, qs.max_elapsed_time
     --, qs.total_clr_time 
     --, qs.last_clr_time 
     --, qs.min_clr_time 
     --, qs.max_clr_time 
     --, qs.[sql_handle] 
     --, qs.statement_start_offset 
     --, qs.statement_end_offset 
     --, qs.plan_generation_num 
     --, qp.encrypted 
FROM
     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
     --sample WHERE 
WHERE
     last_execution_time > '20120912 12:15'
     AND creation_time > '20130101'
     AND execution_count > 10
     --AND SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
     --((CASE statement_end_offset 
     --WHEN -1 THEN DATALENGTH(qt.text)
     --ELSE qs.statement_end_offset END 
     -- - qs.statement_start_offset)/2) 
     -- + 1)
     -- LIKE '%MyText%'

     --sample ORDER BY
     --ORDER BY qs.execution_count DESC --Frequency
     --ORDER BY qs.total_worker_time DESC --CPU
     --ORDER BY avg_worker_time DESC --CPU
     --ORDER BY qs.total_elapsed_time DESC --Durn
     --ORDER BY qs.total_logical_reads DESC --Reads 
     --ORDER BY qs.total_logical_writes DESC --Writes
     --ORDER BY qs.total_physical_reads DESC --PhysicalReads 
     --ORDER BY avg_worker_time DESC --AvgCPU
     --ORDER BY avg_elapsed_time DESC --AvgDurn 
     --ORDER BY avg_logical_reads DESC --AvgReads
     --ORDER BY avg_logical_writes DESC --AvgWrites
ORDER BY
     avg_physical_reads DESC --AvgPhysicalReads

General SQL Queries

Table search by column name

This query allows you to see what tables / procedures have a specified column name in them.

/**************************************************************
   Example(s) of common column names:
   DeviceId, LocationGroupId, ApplicationId, ProfileId, 
   CoreUserId
**************************************************************/ 

SELECT * FROM sysobjects WHERE id IN 
      (SELECT id FROM syscolumns WHERE name LIKE '%ColumnName%')

SP_WHO2 Advanced Query

This allows you to filter on SP_WHO2, which helps when backing up and restoring the AirWatch database.

/**************************************************************  
   This query may look complex but can really  
   narrow down if something is connected to   
   AirWatch or not. When you are upgrading  
   or restoring a database you need to ensure  
   that nothing is locking the databse. **************************************************************/   
CREATE TABLE #sp_who2(   
     SPID INT,   
     Status VARCHAR(1000) NULL,   
     Login SYSNAME NULL,   
     HostName SYSNAME NULL,   
     BlkBy SYSNAME NULL,   
     DBName SYSNAME NULL,   
     Command VARCHAR(1000) NULL,   
     CPUTime INT NULL,   
     DiskIO INT NULL,   
     LastBatch VARCHAR(1000) NULL,   
     ProgramName VARCHAR(1000) NULL,   
     SPID2 INT   
)   
  
INSERT INTO #sp_who2 EXEC sp_who2   
  
SELECT *   
FROM #sp_who2   
WHERE DBName LIKE '%AirWatch%'  
GO   
  
DROP TABLE #sp_who2   
GO

Get all devices from Location Group

This query can return all devices under one locationgroup tree. This is an especially helpful query when used with other queries.

/**************************************************************
   This script provides all devices residing under
   the parent location group. You can combine this
   query with other queries by using the WHERE column
   IN (SELECT query) filter (this script uses the
   same filter to filter the location groups)
**************************************************************/ 

SELECT *
FROM Device d (nolock)
INNER JOIN Location l (nolock)
ON l.LocationId = d.LocationId
INNER JOIN LocationGroup lg (nolock)
lg.DefaultLocationId = l.LocationId
WHERE lg.LocationGroupID IN 
(
   /*****************************************************
      The below sub query provides all children
      Location Groups under one Parent. This can 
      also be useful with other queries.
     ***************************************************/ 

   SELECT lgf.ChildLocationGroupID
   FROM LocationGroup lg (nolock)
   INNER JOIN LocationGroupFlat lgf (nolock)
   ON lgf.ParentLocationGroupID = lg.LocationGroupID
   WHERE lg.Name LIKE '%Customers LG%'
)

Event Log Search

This query allows you to search the event log. This is imperitive since the event log often times out from the console.

/**************************************************************  
   This script provides you with a list of modules in the  
   event log. These can be used to filter the following  
   query.  
**************************************************************/   
SELECT DISTINCT el.Module   
FROM eventLog.EventLog el (nolock)   
  
/**************************************************************  
   This query will select information from the event log   
   and allow you to filter on Modules. Modules provide you  
   with context of the event that occured. The Name is   
   the actual name of the event that occured. This will   
   only show you the last 30 days. You can decrease this   
   number to improve performance.  
     
   You can also filter on the LocationGroupID if you know   
   the location group name. Keep in mind that some modules   
   only report at Global.  
  
   The Username is the Admin user who performed the task.   
   sysadmin is the system user for running stored procedures   
   however some modules (like the device module) show   
   sysadmin even if a user performed the action.  
**************************************************************/  
  
SELECT cu.UserName, el.Module, e.Name, el.*  
FROM eventLog.EventLog el (nolock)  
INNER JOIN eventLog.Event e (nolock)  
ON e.EventId = el.EventId  
INNER JOIN CoreUser cu (nolock)  
ON cu.CoreUserId = el.ActionBy  
WHERE el.CreatedOn > DATEADD(DAY, -30, GETUTCDATE())  
AND el.Module LIKE 'Dashboard'  
--AND LocationGroupID IN (SELECT LocationGroupID   
--FROM LocationGroup WHERE Name LIKE '%AirWatch%')

Role compare script

This script shows you two roles side by side for comparison.

/**************************************************************
   Update the values in @Role1 and @Role2
   to compare two roles. If you need to find
   why a custom role is missing use the next
   script.
**************************************************************/ 

DECLARE @Role1 AS nvarchar(50);
DECLARE @Role2 AS nvarchar(50);
SET @Role1 = 'AirWatch Administrator';
SET @Role2 = 'System Administrator';

SELECT re.ResourceID, c.Name AS 'Module', re.Name, m1.Allow AS 'Role1 Allowed', m2.Allow AS 'Role2 Allowed'
FROM Resource re 
INNER JOIN Category c (nolock)
ON c.CategoryID = re.CategoryID
INNER JOIN Mode m1 (nolock)
ON re.ResourceID = m1.ResourceID
INNER JOIN Role r1 (nolock)
ON r1.RoleId = m1.RoleId
INNER JOIN Mode m2 (nolock)
ON re.ResourceID = m2.ResourceID
INNER JOIN Role r2 (nolock)
ON r2.RoleID = m2.RoleID 
WHERE r1.Name = @Role1 AND r2.Name = @Role2
ORDER BY Module, ResourceID

Missing Custom Role script

This script shows you two roles side by side for comparison.

/**************************************************************
   Update the values in the variables to check
   if resources are missing from the role who is
   supposed to have higher priviledges.
**************************************************************/ 

DECLARE @HigherRole AS nvarchar(50);
DECLARE @LowerRole AS nvarchar(50);
SET @HigherRole = 'Role with higher privileges';
SET @LowerRole = 'Role with less privileges';

SELECT m.ModeID, r.Name, re.Name, m.Allow 
FROM Mode m (nolock)
INNER JOIN Role r (nolock)
ON r.RoleId = m.RoleId
INNER JOIN Resource re 
ON re.ResourceID = m.ResourceID
WHERE r.Name LIKE @LowerRole AND m.Allow = 1 AND
       m.ResourceID IN
       (SELECT ResourceID FROM Mode m 
       INNER JOIN Role r
       ON r.RoleId = m.RoleId
       WHERE r.Name LIKE @HigherRole AND
       m.Allow = 0
       )

### Status of Events stuck in ‘Processing’ or ‘Failed’ to ‘Ready for Processing’

UPDATE adp.AdpExportTracking
    SET [Status] = 2 -- 2 = Completed Event
WHERE [Status] = 1; -- 2 = Pending Event
 
 
-- This script updates the status of Error Exports in adp.ADPExportTracking table
 
SET NOCOUNT ON;
 
BEGIN TRY
    IF  OBJECT_ID(N'adp.ADPExportTracking') IS NOT NULL
    BEGIN
        UPDATE adp.ADPExportTracking
            SET [Status] = 2
        WHERE [Status] = -1 -- -1 = Failed Status
    END
     
END TRY
BEGIN CATCH
    DECLARE    @error_severity        INT,
            @error_state        INT,
            @error_message        NVARCHAR(2048);
 
    SELECT    @error_severity = ERROR_SEVERITY(),
            @error_state = ERROR_STATE(),
            @error_message = ERROR_MESSAGE();
 
    RAISERROR(@error_message, @error_severity, @error_state);
 
END CATCH
GO