What is the Datamining Report (DMR)?

The Datamining Report (DMR) is a core component of ManagementStudio that allows you to quickly pull data from different related parts of ManagementStudio into a single view. For example, a report of Apps and the USers that use them or a report of Defects and their associated users.


TABLE OF CONTENTS


How to use

A basic DMR is a single call to get info on a list of items (Apps, Users, Devices etc), where a list of Fields and/or Custom Form Fields are supplied and a grid of data is returned. This can be extended to add multiple tiers of data such as adding User to an Apps report. 


DRM API Object

The DRM report returns an object with several properties. 


$rpt = Get-MSUserMigrationDataminingReport -All
$rpt.Data | Out-GridView


  • $rpt.Status
    • Some metadata on the report
    • IsSuccess - Check this to see if report completed successfully on the server.
    • ErrorMessage - If the report failed to run this will have information on why.
    • StartTime - Time report was started
    • EndTime - Time report was completed
    • RunTime - The duration in seconds that it took to build the report
    • Rows - Count of rows in the report
    • Columns - Count of columns in the report
  • $rpt.Columns 
    • A dictionary of the columns alternate names and data types
    • This is used by the UI and generally not required in PowerShell
  • $rpt.Data
    • The main report table of data


Getting Data 

Various different ways to get data from for the DRM. 


Examples


# By Ids
$rpt = Get-MSUserMigrationDataminingReport -MigrationIds @(10384, 10403, 13624)

# From Pipeline
$rpt = Get-MSUserMigrations -ProcessName '4. Migrating' -IdsOnly | `

        Get-MSUserMigrationDataminingReport

# In Process
$rpt = Get-MSUserMigrationDataminingReport -ProcessIds @(41)

# In Blueprint
$rpt = Get-MSUserMigrationDataminingReport -BlueprintIds @(1, 2)

# All Users
$rpt = Get-MSUserMigrationDataminingReport -All


$rpt.Data | Out-GridView


Adding Basic Fields and Custom Fields

By default, if no fields are specified the DRM will return some starter fields. e.g. for Apps, they are AppVendor, AppName, AppVersion. To explicitly chose fields for the report use the -Fields or -CustomFieldIds params. 


Examples

$rpt = Get-MSUserMigrationDataminingReport -All `
-CustomFieldIds @(141, 142, 143, 144, 145)
$rpt.Data | Out-GridView


Adding Additional Tiers

Additional tiers allow information from other modules to be added to a data mining report. For example, running a User Migration report and adding in the User's Apps and Devices. Additional tiers are their own separate object and are created using the 'New-MSDatamingTier' function. These tiers have similar options as the data mining report, they have Fields, CustomFields, and Options parameters.  


Example Tiers

$tier1 = New-MSDataminingTier -Module Applications
$tier2 = New-MSDataminingTier -Module Devices
$rpt = Get-MSUserMigrationDataminingReport -All -AdditionalTiers @($tier1, $tier2)
$rpt.Data | Out-GridView



Adding Readiness Tiers

Readiness tiers use the Process Status of linked items to calculate the readiness of the parent items in the data mining reports. For example, a DM report of Users can have an App Readiness tier added to see the Users 'readiness' in relation to their Apps.  

Multiple readiness tiers can be added to the data mining report to get an aggregate readiness over multiple modules. For example, a user DM report can have Users, Apps, and Device readiness added to get a complete picture of readiness for your users. 



Each readiness tier adds five columns to the report, plus one additional column for the overall readiness across all tiers : Total, ReadyCount RemainingCount, IsReady, ReadyPercent, and OverallReadiness.

Example Readiness Tiers


$apps = New-MSReadinessTier -Module Applications
$device = New-MSReadinessTier -Module Devices
$rpt = Gett-MSUserMigrationDataminingReport -All -ReadinessTiers @($apps, $device)
$rpt.Data | Out-GridView



Interaction between Normal Tiers and Readiness Tiers


When a DM report is run on a group of Users and their Apps List it creates a one-to-many result of one row per User-App relationship. This means a 5 User report could have 50 rows of results. 

Contrastly, when doing a User and their Apps Readiness it creates a one-to-one result of the Users and their aggregate App readiness of all of their Apps. i.e. a 5 User App Readiness report will have only 5 rows in the results. 


What happens if we combine the two?


When a DM Report is run that has the same tier (e.g. Apps) in both the -AdditionalTiers and -ReadinessTiers ManagementStudio will not return the aggregate App readiness for the User but rather expand out the individual readiness per App. This allows the Apps that are holding up a User to be seen on a User by App basis. 



Combined Tiers adds the 'ModuleName_IsReady' and 'ModuleName_IsOutOfScope' columns to the report.



# Combined User-App List with Individual App Readiness
# Bonus: Filter results to Apps that are outstanding
$appReadiness = New-MSReadinessTier -Module Applications
$appData = New-MSDataminingTier -Module Applications

$rpt = Get-MSUserMigrationDataminingReport -All
-ReadinessTiers @($appReadiness) -AdditionalTiers @($appData) `
-FilterExpression "Applications_IsReady = 0 AND Applications_IsOutOfScope = 0"

$rpt.Data | Out-GridView



Filter & Sort Expression

There are two special parameters called 'FilterExpression' and 'SortExpression' these are applied to the server-side results after the data mining report has been built. Both use pseudo SQL as a way of building expressions. The filter works like an SQL 'Where' but without the 'Where' keyword and the sort works like an SQL 'Sort' without the 'Sort' keyword.  
Use the prefixed version of the column names when building expressions. The filter and sorting can only be applied to columns that are in the resulting report, an error will be added to the if columns that don't exist are filtered or sorted.


Filter & Sort Example


$rpt = Get-MSUserMigrationDataminingReport -All `
-Fields @( 'MigrationId', 'FirstName', 'LastName', 'Domain', 'EnteredProcessOn') `
-FilterExpression "UserMigrations_Domain = 'Global' AND UserMigrations_EnteredProcessOn > '2020-10-05' " `
-SortExpression "UserMigrations_LastName ASC, UserMigrations_LastName ASC"

$rpt.Data | Out-GridView


The FilterExpression is applied after the report has been built, it can not be used to make a report run faster. To make a DM report run fast as possible, filtering should be done first and only the Ids required for the report passed to Datamining function. 


Remove Columns


Some report options such as Readiness Tiers and Link Options add fixed columns to the report table. It's possible to remove these columns from the results with the -RemoveColumns parameter. 


It's important to note the columns are removed after the Filter and Sort expressions are run. This allows you to, for example, add the IsArchived column then use it as a filter, and then remove the column without returning to the client.


Note, the name of the column on the way in is not the same as the name of the column on the way out, to avoid collisions the module name is prefixed to the column name. This can be seen in the example below. 


Remove Columns Example

For a given list of AppIds, get a list of Applications, filter to Apps with a 'High' Priority, then remove the Priority Column and return the App Vendor and Name.

$rpt = Get-MSApplications -All -IdsOnly | Get-MSApplicationDataminingReport `
-Fields @("AppVendor", "AppName", "Priority") `
-FilterExpression "Applications_Priority = 'High'" `
-RemoveColumns @("Applications_Priority")



Adding Report Options

some text


Example



More Examples

# Defects DM Report with App and User info added

$appsTier = New-MSDataminingTier -Module 'Applications' `
-Fields @('AppId', 'AppVendor', 'AppName')

$usersTier = New-MSDataminingTier -Module 'UserMigrations' `
-Fields @('MigrationId', 'FirstName', 'LastName')

$rpt = Get-MSDefects -All -IdsOnly | Get-MSDefectDataminingReport `
-Fields @('DefectId' ,'BusinessImpact' ,'InstanceModule') `
-AdditionalTiers @($appsTier, $usersTier )

$rpt.Data | Format-Table -AutoSize

Report Options

Report options go into the -Options @() param of the DMR. These options can do all sorts of things like adding new fields of data (e.g. App Readiness, User Counts).


Common Options

Common options work regardless of the tiers in the report.

  • No_Data
    • This will build the report fully on the server but will not return any data to the client. This can be useful when saving report results server-side. 


Module Options

Module Options only work with specific Modules that need to also be included in at one of the report tiers. 


Applications

  • TODO

User Migrations

  • TODO


Devices

  • TODO


Mailboxes

  • TODO


Bespoke Module

  • TODO


Deployment Units

  • TODO


Defects

  • TODO


Surveys

  • Survey_Url
    • Will return the Survey URL, if there are multiple surveys defined then multiple rows will be returned. 
  • Survey_Url_X
    • Where X is the SurveyId of the URLs to retrieve
  • Survey_Url_NAME
    • Where NAME is the name of the Survey of the URLs to retrieve
  • SurveyId-XXX
    • Where XXX is the Id of the Survey to filter the results to
    • This can be especially useful if there are a lot of surveys and you need to show multiple survey results in different columns. 
    • Multiple Survey tican be added and then filtered by their SurveyId


When two or more modules such as Apps, Users, or Devices are selected using additional tiers they will automatically be linked together into a single report by ManagementStudio. However, there are additional options that can be used to control this linking or retrieve more info about the links. Use the -Options param of the DRM API call to activate these options. 


# Three-tier DMR with extra link options added

$tier1 = New-MSDataminingTier -Module Applications -Fields @('AppName', 'AppVendor')
$tier2 = New-MSDataminingTier -Module Devices -Fields @('Hostname')

$rpt = Get-MSUserMigrationDataminingReport -MigrationIds @(10384, 10403, 13624) `
-Fields @('MigrationId', 'FirstName', 'LastName' ,'SamAccount' ,'Domain', 'Process') `
-AdditionalTiers @($tier1, $tier2) `
-Options @('Link_AppInfo', 'Link_AppPending', 'Link_LastUsedInfo', 'Link_StatusInfo')

$rpt.Data | Format-Table -AutoSize


ManagementStudio supports complex linkages such as "John Doe used Adobe Reade on Laptop_0001 at 3 pm on April 5th". 

When using the DRM report to get a two-tiered report (e.g.  Apps --> Users or User --> Devices) the direct link is automatically used. However, for a three-tiered report (e.g. Apps, Users, Devices) a decision must be made on how to treat the links. This decision is controlled via the option keywords 'Link_FullChain' and 'Link_NoChain'. See below for how these options work. 



Case 1:
    Link_FullChain
    The Users, their Apps, and Device this User used App on
    Users -->  Apps --> Devices
    

Case 2:
    Link_NoChain
    The Users, their Apps, and the User's Devices regardless of Apps
    Users --> Apps
    Users --> Devices


Case 3:
    Not Possible
    The Users, their Apps, and any Devices the Apps were used on
    Users --> Apps
              Apps <--> Devices



Full Options List

  • Link_LinkId
    • Adds internal LinkId field

  • Link_FullChain 
    • This is the default if no Chain option is specified
    • The Users, their Apps, and Device this User used App on
    • Users -->  Apps --> Devices

  • Link_NoChain
    • Overrides the 'Link_FullChain' option
    • The Users, their Apps, and the User's Devices regardless of Apps
    • Users --> Apps
    • Users --> Devices

  • Link_AppInfo
    • Adds 'Link_AppStatus' field
      • Accepted, Pending, Rationalised, Rejected
    • Adds 'Link_AppAggregateStatus' field.
      • Expands the Rejected status into two possibilities: Rejected Link, Rejected App
      • Accepted, Pending, Rationalised, Rejected Link, Rejected App

  • Link_AppPending
    • Includes pending Apps in the list of App results.
    • Doesn't add any new fields.

  • Link_StatusInfo
    • Adds 'Link_LinkStatus' field.
      • 'Rejected' / 'Linked'
    • Adds 'Link_IsRejected' field.
      • True / False

  • Link_DeviceInfo
    • Adds 'Link_AffinityType' field.
      • The Affinity rank 1,2,3... etc.
    • Adds 'Link_AffinityLabel' field.
      • The 'friendly' Affinity name: Primary, Secondary, Tertiary

  • Link_LastUsedInfo
    • Adds 'Link_LastUsedDate' field. 
      • The date the item was lasted reported as being used (from SCCM, Snow, SysTrack etc). 
    • Adds 'Link_LastUsedDateLabel' field. 
      • The 'friendly' last used info, e.g. Today, Yesterday, Over a month.

  • Link_LastModifiedInfo
    • Adds 'Link_LastModifiedDate' field. 
      • The data the link in the DB was last updated. 
      • This is not the date it was last used by a User
    • Adds 'Link_LastModifiedBy' field. 
      • The account that updated the link in the DB.

  • Link_ArchivedInfo
    • Adds 'Link_ArchivedDeleted' field.
      • If this field is greater than zero then some item in the link has been either archived or marked for deletion. 

Help! Where are my devices!?
When doing a three tier report such as a User --> App --> Device report, if all the Apps for a Device are 'hidden' i.e. Rejected or in Pending then the Device linked to those Apps will also be hidden. To get around this the 'Link_AppPending' or 'Link_StatusInfo' option can be added. Another way to do it would be be to ensure that the Device as a direct link to the User that does not include an App as part of the link.



Defects


Defects Examples

# Defects DM Report with App and User info added

$appsTier = New-MSDataminingTier -Module 'Applications' `
-Fields @('AppId', 'AppVendor', 'AppName')

$usersTier = New-MSDataminingTier -Module 'UserMigrations' `
-Fields @('MigrationId', 'FirstName', 'LastName')

$rpt = Get-MSDefects -All -IdsOnly | Get-MSDefectDataminingReport `
-Fields @('DefectId' ,'BusinessImpact' ,'InstanceModule') `
-AdditionalTiers @($appsTier, $usersTier )

$rpt.Data | Format-Table -AutoSize


# App DM Report with Defect info added

$defectTier = New-MSDataminingTier -Module Defects `
-Fields @('DefectId', 'ShortDescription', 'Resolution')

$rpt = Get-MSApplicationDataminingReport -AppIds @(8071, 8073, 11552) `
-AdditionalTiers @($defectTier)

$rpt.Data | Format-Table -AutoSize


Defects Fields

DefectId, InstanceId, Module, InstanceTitle,  InstanceModuleId, InstanceModule, Title, ModuleId, ShortDescription, BusinessImpact, Description, Resolution, Email, Email2, Process, SubProcess, ProcessStatus, EnteredProcessOn, Priority,  RootCause,  Severity, CreatedOn, LegacyId, 

CustomDate1, CustomDate2, CustomDate3, CustomDate4, CustomDec1, CustomDec2, CustomDec3, CustomFlag1, CustomFlag2, CustomFlag3, CustomFlag4, CustomInt1, CustomInt2, CustomInt3, CustomProperty1, CustomProperty2, CustomProperty3, CustomProperty4, CustomProperty5, CustomProperty6, CustomProperty7, CustomProperty8, CustomProperty9, CustomProperty10, CustomList1, CustomList2, CustomList3, CustomList4, CustomList5, CustomList1Id, CustomList2Id, CustomList3Id, CustomList4Id, CustomList5Id,

DelegateTo1Id, DelegateTo1, DelegateTo1DisplayName, DelegateTo2Id, DelegateTo2, DelegateTo2DisplayName, CustomDataId, AssignedToId, AssignedTo, AssignedToDisplayName, CreatedById, CreatedBy, CreatedByDisplayName,  IsArchived, IsDeleted, IsLocked

ProcessId, SubProcessId, ProcessStatusId, PriorityId, RootCauseId, SeverityId,




Deployment Units


Deployment Units Examples

# Deployment Unit DM Report with App info added

$appsTier = New-MSDataminingTier -Module Applications `
-Fields @('AppId', 'AppVendor', 'AppName')

$rpt = Get-MSDeploymentUnits -All -IdsOnly | Get-MSDeploymentUnitDataminingReport `
-AdditionalTiers @($appsTier)

$rpt.Data | Format-Table -AutoSize


# App DM Report with Deployment Unit info added

$duTier = New-MSDataminingTier -Module DeploymentUnits -Fields @('TargetModule', 'DeployUnit')

$rpt = Get-MSApplicationDataminingReport -AppIds @(8839, 8840) -AdditionalTiers @($duTier)

$rpt.Data | Format-Table -AutoSize


Deployment Units Fields

Title, DeployUnitId, Id, ProjectId, TargetModule, TargetModuleId, ModuleId, DeployUnit, ShortLocation, Process, SubProcess, ProcessStatus, Email, Email2, EnteredProcessOn, ProcessId, SubProcessId, ProcessStatusId, UseGlobalSchedule, StartDate, StartDateIsAllDay, EndDate, EndDateIsAllDay, Description, IsBaseLineLocked, BaseLineLockedDate, IsSignedOff, SignedOffBy, SignedOffDate, IsCompleted, CompletionDate, PriorityId, Priority, StatusId, Status, LegacyId, CustomDataId, CustomDate1, CustomDate2, CustomDate3, CustomDate4, CustomDec1, CustomDec2, CustomDec3, CustomFlag1, CustomFlag2, CustomFlag3, CustomFlag4, CustomInt1, CustomInt2, CustomInt3, CustomProperty1, CustomProperty2, CustomProperty3, CustomProperty4, CustomProperty5, CustomProperty6, CustomProperty7, CustomProperty8, CustomProperty9, CustomProperty10, CustomList1, CustomList2, CustomList3, CustomList4, CustomList5, CreatedOn, CreatedById, CreatedBy, CreatedByDisplayName, AssignedToId, DelegateTo1Id, DelegateTo1, DelegateTo1DisplayName, DelegateTo2Id, DelegateTo2, DelegateTo2DisplayName, AssignedTo, AssignedToDisplayName, LocationId, CustomList1Id, CustomList2Id, CustomList3Id, CustomList4Id, CustomList5Id, PreventNewLinks, FontIcon, IsLocked, IsArchived, IsDeleted




Surveys

Surveys can not be a top-level part of the DMR. Surveys can be added to a DMR via an additional tier and will be applied to the top-level DMR item. If there are other modules in the additional tier list these will be ignored when calculating the survey results. 


Surveys can't be the top tier in a DMR and it only available as an additional tier.


Survey Example

# App DM Report with Surveys info added

$surveysTier = New-MSDataminingTier -Module Surveys `
-Fields @('SurveyId', 'SurveyName', 'CompleteResult', 'CompletedBy')

$rpt = Get-MSApplicationDataminingReport -AppIds @(7972, 8071, 8609) `
-Fields ('AppName') -AdditionalTiers @($surveysTier)

$rpt.Data | Format-Table -AutoSize


Survey Fields

SurveyId, InstanceId, ProjectId, ModuleId, SurveyName, IsEnabled, Comments, CompletedBy, CompleteResult, CompleteDate, FailReason, IsLocked, IsLockedDate, PageVisitCount, PageLastVisitDate, RequestEmailSentDate, RequestEmailOpenCount, RequestEmailLastOpenDate, ReminderEmailSentDate, ReminderEmailOpenCount, ReminderEmailLastOpenDate, CompleteEmailSentDate



Test Sign-Offs

Test Sign-Offs can not be a top-level part of the DMR. Test Sign-Offs can be added to a DMR via an additional tier and will be applied to the top-level DMR item. If there are other modules in the additional tier list these will be ignored when calculating the survey results. 


Test Sign-Offs can't be the top tier in a DMR and it only available as an additional tier.


Test Sign-Offs Example

# App DM Report with Test Sign-Off info added

$testingTier = New-MSDataminingTier -Module Testing -Fields @('SurveyId', 'TestType', 'ItemName', 'CompleteResult')

$rpt = Get-MSApplicationDataminingReport -AppIds @(7972, 8071, 8603) -Fields ('AppName') -AdditionalTiers @($testingTier )

$rpt.Data | Format-Table -AutoSize


Test Sign-Offs Fields

ProjectId, ModuleId, TestResultId, SurveyId, ContactId, InstanceId, FirstName, LastName, Email, Email2, TestType, Title, ItemName, IsGlobalSignOff, NotesToTester, TestWindowStart, TestWindowEnd, ResolvedDeviceName, IsModuleDevice, ModuleDeviceId, ModuleDeviceName, ManualDeviceName, Comments, CompletedBy, CompleteResult, CompleteDate, FailReason, IsLocked, IsLockedDate, AttachICal, AttachRdp, IsArchived, TestSignOffUrl, PageVisitCount, PageLastVisitDate, RequestEmailCentreId, RequestEmailSent, ReminderEmailCentreId, ReminderEmailSent, CompleteEmailCentreId, CompleteEmailSent, UpdateEmailCentreId, UpdateEmailSent, CancelEmailCentreId, CancelEmailSent, CreatedById, CreatedOn, CreatedBy, CreatedByDisplayName, RandomKey, ICalSubject, ICalLocation, ICalDescription





Dependencies

Dependencies can not be a top-level part of the DMR, therefore there is no Get-MSDependencyDataMiningReport API call. Dependencies can be added to a DMR via an additional tier and will be applied to the top-level DMR item. If there are other modules in the additional tier list these will be ignored when calculating the dependencies. 


Available Options

'Dependencies_Add' use with an Additional Tier to add the dependencies of that type to the report. E.g. In a User data mining report that has an Application Tier, if the '' option is added to the Application Tier then dependent Applications will be added to the Users App's list. 


'Dependencies_FirstLevel' will filter the returned dependencies to only dependencies directly linked to the report items and will not automatically resolve the dependencies of dependencies. 


Dependencies can't be the top tier in a DMR and it only available as an additional tier.


Dependency Examples 

Application DM Report with Dependencies info added

$dependsTier = New-MSDataminingTier -Module 'Dependencies' 
$rpt = Get-MSApplicationDataminingReport -All -AdditionalTiers @($dependsTier)
$rpt.Data | Out-GridView


User-App-Device Report with App dependencies added, but only top-level dependencies

$appsTier = New-MSDataminingTier -Module Applications `
        -Options @('Dependencies_Add', 'Dependencies_FirstLevel')
$devicesTier = New-MSDataminingTier -Module Devices
$rpt = Get-MSUserMigrationDataminingReport -AdditionalTiers @($appsTier, $devicesTier)
$rpt.Data | Out-GridView



Dependency Fields

ParentProjectId, ParentModuleId, ParentInstanceId, Project, Module, ItemName, Title, InstanceId, ModuleId, ProjectId, ProcessName, SubProcessName, ProcessStatus, ProcessId, SubProcessId , ProcessStatusId, IsLocked, IsArchived, IsDeleted, Depth, IsTopLevel



Tasks


Task Examples

// Tasks DM Report with App info added

$appsTier = New-MSDataminingTier -Module 'Applications' -Fields @('AppId', 'AppName')

$rpt = Get-MSTaskDataminingReport -All -AdditionalTiers @($appsTier)

$rpt.Data | Format-Table -AutoSize


// App DM Report with Tasks info added

$tasksTier = New-MSDataminingTier -Module Tasks -Fields @('TaskId', 'Subject', 'Process')

$rpt = Get-MSApplicationDataminingReport -AppIds @(8021, 7933, 8892) -AdditionalTiers @($tasksTier)

$rpt.Data | Format-Table -AutoSize


Task Fields

Title, ItemName, Id, ModuleId, ProjectId, TaskId, Subject, Description, Process, SubProcess, ProcessStatus, EnteredProcessOn, StartDate, DueDate, AssignedToId, AssignedTo, AssignedToDisplayName, Category, Priority, ProcessId, SubProcessId, ProcessStatusId, CreatedOn, CreatedById, CreatedBy, CreatedByDisplayName, PriorityId, CategoryId, IsArchived, IsDeleted, IsLocked , TagList, TagIdList



Contacts


Contact Examples

// Application DM Report with Contacts info added

$contactsTier = New-MSDataminingTier -Module 'Contacts' `
-Fields @('ContactType', 'FirstName', 'LastName')

$rpt = Get-MSApplicationDataminingReport -AppIds @(8024, 8603) `
-AdditionalTiers @($contactsTier)

$rpt.Data | Format-Table -AutoSize


// Contacts DM Report with Application info added 

$appsTier = New-MSDataminingTier -Module Applications `
-Fields @('AppVendor', 'AppName', 'AppVersion')

$rpt = Get-MSContactDataminingReport -All `
-Fields @('ContactType', 'FirstName', 'LastName') `
-AdditionalTiers @($appsTier)

$rpt.Data | Format-Table -AutoSize


Contact Fields

ContactType, ContactTypeId, Title, ProjectId, ContactId, ModuleId, FirstName, LastName, SamAccount, Domain, Organisation, JobTitle, PhoneWork, PhoneMobile, Email, Email2, CostCenter, OfficeSite, Address, City, Country, PostCode, Note, MigrationId, CreatedOn




Attachments

Attachments can not be a top-level part of the DMR. Attachments can be added to a DMR via an additional tier and will be applied to the top-level DMR item. If there are other modules in the additional tier list these will be ignored when calculating the survey results. 


Attachments can't be the top tier in a DMR and it only available as an additional tier.


Attachments Example

// App DM Report with Attachments info added

$surveysTier = New-MSDataminingTier -Module Attachments -Fields @('AttachmentType', 'FileNameOrig')

$rpt = Get-MSApplicationDataminingReport -AppIds @(11544, 8071, 8609) -Fields ('AppName') -AdditionalTiers @($surveysTier)

$rpt.Data | Format-Table -AutoSize


Attachments Fields

InstanceId, ModuleId, AttachmentId, AttachmentType, FileNameOrig, ContentType, FileExtension, FileSizeKb, LastModifiedBy, LastModifiedDate, FileNameDisk, RandomKey, AttachmentTypeId, LastModifiedById





Standard Field Lists


Application Fields

'AppId', 'Title', 'SupersededTo', 'ModuleId', 'SupersededBy', 'SupersededToId', 'AppVendor', 'AppName', 'AppVersion', 'AppEdition', 'CustomerId', 'IsCoreApp', 'AlsoKnownAs', 'Description', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'AppStatus', 'Email','Email2', 'PackageVersion', 'PkgVerMajor', 'PkgVerMinor', 'PkgVerPatch', 'Priority', 'PackagingSite', 'PackageType', 'Localisation', 'Language',  'Complexity', 'Architecture', 'CreatedOn', 'EndOfLifeDate', 'RequiredDate', 'WarrantyDate', 'LegacyId', 

'DeployUnitId', 'DeployUnit', 'DeployUnitStartDate', 'DuShortLocation', 'DeployUnitSlotStart', 'DeployUnitSlotEnd', 'SelfScheduleIsLocked', 'AllowExpiredSelfSchedule', 'IsSchedulingException',

'CustomDataId', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList1Id', 'CustomList2', 'CustomList2Id', 'CustomList3', 'CustomList3Id', 'CustomList4', 'CustomList4Id', 'CustomList5', 'CustomList5Id', 'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3',

'CreatedBy', 'CreatedByDisplayName', 'AssignedTo', 'AssignedToDisplayName', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2', 'DelegateTo2DisplayName', 'OwnedBy', 'OwnedByDisplayName', 'PackagedBy', 'PackagedByDisplayName', 'PreventNewLinks', 'IsLocked', 'IsArchived', 'IsDeleted', 

'ProcessId', 'SubProcessId', 'AppStatusId', 'ProcessStatusId', 'PriorityId', 'PackagingSiteId', 'PackageTypeId', 'LocalisationId', 'LanguageId', 'ComplexityId', 'ArchitectureId',

'CreatedById', 'AssignedToId', 'DelegateTo1Id', 'DelegateTo2Id', 'OwnedById', 'PackagedById', 


User Migration Fields

'MigrationId', 'Title', 'ModuleId', 'FirstName', 'LastName', 'SamAccount', 'Domain', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'AdSid', 'EmployeeId', 'Email', 'Email2', 'PhoneNo', 'Priority',  'Status', 'MigrationDate1', 'MigrationDate2', 

'DeployUnitId', 'DeployUnit', 'DeployUnitStartDate', 'DuShortLocation', 'DeployUnitSlotStart', 'DeployUnitSlotEnd', 'SelfScheduleIsLocked', 'AllowExpiredSelfSchedule' 'IsSchedulingException',

'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id', 

'DelegateTo1', 'DelegateTo1DisplayName',  'DelegateTo2', 'DelegateTo2DisplayName', 'CreatedBy', 'CreatedByDisplayName',  'AssignedTo', 'AssignedToDisplayName', 'MigrationType', 'PreventNewLinks', 'IsLocked', 'IsArchived', 'IsDeleted', 'Description',  ,'CreatedOn', 

'LegacyId', 'ProcessId', 'SubProcessId', 'ProcessStatusId', 'PriorityId', 'StatusId', 'CustomDataId','LocationId', 'MigrationTypeId',

'CreatedById', 'DelegateTo1Id', 'DelegateTo2Id', 'AssignedToId'


Device Fields

'DeviceId', 'Title', 'ModuleId', 'HostName', 'Domain', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'AssetTag', 'Make', 'Model', 'Processor', 'Memory', 'DeviceArchitecture', 'OperatingSystem', 'ProcessStatusId', 'ServicePack', 'BuildVersion', 'Description', 'DeskNumber', 'FloorNumber', 'Email', 'Email2', 'IpAddress', 'AdSid', 'SmBiosGuid', 'MacAddress', 'SerialNumber', 'DeviceType',  'Priority',  'Status',  'LegacyId', 

'DeployUnitId', 'DeployUnit', 'DeployUnitStartDate', 'DeployUnitSlotStart', 'DeployUnitSlotEnd', 'DuShortLocation', 'SelfScheduleIsLocked', 'AllowExpiredSelfSchedule', 'IsSchedulingException'

'CustomDataId', 'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id', 

'CreatedOn', 'CreatedById', 'DelegateTo1Id', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2Id', 'DelegateTo2', 'DelegateTo2DisplayName', 'CreatedBy', 'CreatedByDisplayName', 'AssignedToId', 'AssignedTo', 'AssignedToDisplayName', 'LocationId', 'MigrationTypeId', 'MigrationType', 'PreventNewLinks', 'FontIcon', 'IsLocked', 'IsArchived', 'IsDeleted', 'MigrationDate1', 'MigrationDate2', 

'ProcessId', 'SubProcessId', 'DeviceTypeId', 'PriorityId', 'StatusId',


Mailbox Fields

'MailId', 'Title', 'ModuleId', 'MailName1', 'MailName2', 'MailName3', 'Email', 'Email2', 'AdSid', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'Priority', 'Status', 'Description', 'LegacyId',

'DeployUnitId', 'DeployUnit', 'DeployUnitStartDate', 'DuShortLocation', 'DeployUnitSlotStart', 'DeployUnitSlotEnd', 'SelfScheduleIsLocked', 'AllowExpiredSelfSchedule', 'IsSchedulingException',

'CustomDataId', 'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id', 

'CreatedOn', 'CreatedById', 'DelegateTo1Id', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2Id', 'DelegateTo2', 'DelegateTo2DisplayName', 'CreatedBy', 'CreatedByDisplayName', 'AssignedToId', 'AssignedTo', 'AssignedToDisplayName', 'LocationId', 'MigrationTypeId', 'MigrationType', 'FontIcon', 'PreventNewLinks', 'IsLocked', 'IsArchived', 'IsDeleted', 'MigrationDate1', 'MigrationDate2', 

'ProcessId', 'SubProcessId', 'ProcessStatusId', 'PriorityId', 'StatusId', 


Bespoke Module Fields

'BespokeId', 'Title', 'ModuleId', 'BespokeName1', 'BespokeName2', 'BespokeName3', 'AdSid', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'ProcessId', 'SubProcessId', 'ProcessStatusId', 'Priority', 'PriorityId', 'Status', 'StatusId', 'Email', 'Email2', 'CreatedOn', 'LegacyId', 

'DeployUnitId', 'DeployUnit', 'DeployUnitStartDate', 'DuShortLocation', 'DeployUnitSlotStart', 'DeployUnitSlotEnd', 'SelfScheduleIsLocked', 'AllowExpiredSelfSchedule', 'IsSchedulingException'

'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id',

'DelegateTo1Id', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2Id', 'DelegateTo2', 'DelegateTo2DisplayName', 'CustomDataId', 'AssignedToId', 'AssignedTo', 'AssignedToDisplayName', 'CreatedById', 'CreatedBy', 'CreatedByDisplayName', 'LocationId', 'MigrationTypeId', 'MigrationType',  'PreventNewLinks', 'MigrationDate1', 'MigrationDate2', 'Description', 'FontIcon', 'IsLocked', 'IsArchived', 'IsDeleted', 


Deployment Unit Fields

'DeployUnitId', 'Title', 'TargetModule', 'TargetModuleId', 'ModuleId', 'DeployUnit', 'ShortLocation', 'Process', 'SubProcess', 'ProcessStatus', 'Email', 'Email2', 'EnteredProcessOn', 'ProcessId', 'SubProcessId', 'ProcessStatusId', 'UseGlobalSchedule', 'StartDate', 'StartDateIsAllDay', 'EndDate', 'EndDateIsAllDay', 'Description', 'IsBaseLineLocked', 'BaseLineLockedDate', 'IsSignedOff', 'SignedOffBy', 'SignedOffDate', 'IsCompleted', 'CompletionDate', 'PriorityId', 'Priority', 'StatusId', 'Status', 'LegacyId', 

'CustomDataId', 'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id',

'CreatedOn', 'CreatedById', 'CreatedBy', 'CreatedByDisplayName', 'AssignedToId', 'DelegateTo1Id', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2Id', 'DelegateTo2', 'DelegateTo2DisplayName', 'AssignedTo', 'AssignedToDisplayName', 'LocationId',  'PreventNewLinks', 'FontIcon', 'IsLocked', 'IsArchived', 'IsDeleted'


Defect Fields

'DefectId', 'Module', 'InstanceTitle', 'InstanceId', 'InstanceModuleId', 'InstanceModule', 'Title',  'ModuleId', 'ShortDescription', 'BusinessImpact', 'Description', 'Resolution', 'Email', 'Email2', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'ProcessId', 'SubProcessId', 'ProcessStatusId', 'Priority', 'PriorityId', 'RootCause', 'RootCauseId', 'Severity', 'SeverityId', 'CreatedOn', 'LegacyId', 

'CustomDate1', 'CustomDate2', 'CustomDate3', 'CustomDate4', 'CustomDec1', 'CustomDec2', 'CustomDec3', 'CustomFlag1', 'CustomFlag2', 'CustomFlag3', 'CustomFlag4', 'CustomInt1', 'CustomInt2', 'CustomInt3', 'CustomProperty1', 'CustomProperty2', 'CustomProperty3', 'CustomProperty4', 'CustomProperty5', 'CustomProperty6', 'CustomProperty7', 'CustomProperty8', 'CustomProperty9', 'CustomProperty10', 'CustomList1', 'CustomList2', 'CustomList3', 'CustomList4', 'CustomList5',

'DelegateTo1Id', 'DelegateTo1', 'DelegateTo1DisplayName', 'DelegateTo2Id', 'DelegateTo2', 'DelegateTo2DisplayName', 'CustomDataId', 'AssignedToId', 'AssignedTo', 'AssignedToDisplayName', 'CreatedById', 'CreatedBy', 'CreatedByDisplayName', 'CustomList1Id', 'CustomList2Id', 'CustomList3Id', 'CustomList4Id', 'CustomList5Id', 'FontIcon', 'IsArchived', 'IsDeleted', 'IsLocked'


Dependency  Fields

'ParentProjectId', 'ParentModuleId', 'ParentInstanceId', 'Project', 'Module', 'ItemName', 'Title', 'InstanceId', 'ModuleId', 'ProjectId', 'ProcessName', 'SubProcessName', 'ProcessStatus', 'ProcessId', 'SubProcessId ', 'ProcessStatusId', 'IsLocked', 'IsArchived', 'IsDeleted', 'Depth', 'IsTopLevel'


Task Fields

'Title', 'ItemName', 'ModuleId', 'ProjectId', 'TaskId', 'Subject', 'Description', 'Process', 'SubProcess', 'ProcessStatus', 'EnteredProcessOn', 'StartDate', 'DueDate', 'AssignedToId', 'AssignedTo', 'AssignedToDisplayName', 'Category', 'Priority', 'ProcessId', 'SubProcessId', 'ProcessStatusId', 'CreatedOn', 'CreatedById', 'CreatedBy', 'CreatedByDisplayName', 'PriorityId', 'CategoryId', 'IsArchived', 'IsDeleted', 'IsLocked ', 'TagList', 'TagIdList'


Contact Fields

'ContactType', 'ContactTypeId', 'Title', 'ProjectId', 'ContactId', 'ModuleId', 'FirstName', 'LastName', 'SamAccount', 'Domain', 'Organisation', 'JobTitle', 'PhoneWork', 'PhoneMobile', 'Email', 'Email2', 'CostCenter', 'OfficeSite', 'Address', 'City', 'Country', 'PostCode', 'Note', 'MigrationId', 'CreatedOn'


Survey Fields

'SurveyId', 'InstanceId', 'ProjectId', 'ModuleId', 'SurveyName', 'IsEnabled', 'Comments', 'CompletedBy', 'CompleteResult', 'CompleteDate', 'FailReason', 'IsLocked', 'IsLockedDate', 'PageVisitCount', 'PageLastVisitDate', 'RequestEmailSentDate', 'RequestEmailOpenCount', 'RequestEmailLastOpenDate', 'ReminderEmailSentDate', 'ReminderEmailOpenCount', 'ReminderEmailLastOpenDate', 'CompleteEmailSentDate'


Test Sign-Off Fields

 'TestResultId', 'SurveyId', 'ContactId', 'InstanceId', 'ModuleId','FirstName', 'LastName', 'Email', 'Email2', 'TestType', 'Title', 'ItemName', 'IsGlobalSignOff', 'NotesToTester', 'TestWindowStart', 'TestWindowEnd', 'ResolvedDeviceName', 'IsModuleDevice', 'ModuleDeviceId', 'ModuleDeviceName', 'ManualDeviceName', 'Comments', 'CompletedBy', 'CompleteResult', 'CompleteDate', 'FailReason', 'IsLocked', 'IsLockedDate', 'AttachICal', 'AttachRdp', 'IsArchived', 'TestSignOffUrl', 'PageVisitCount', 'PageLastVisitDate', 'RequestEmailCentreId', 'RequestEmailSent', 'ReminderEmailCentreId', 'ReminderEmailSent', 'CompleteEmailCentreId', 'CompleteEmailSent', 'UpdateEmailCentreId', 'UpdateEmailSent', 'CancelEmailCentreId', 'CancelEmailSent', 'CreatedById', 'CreatedOn', 'CreatedBy', 'CreatedByDisplayName', 'RandomKey', 'ICalSubject', 'ICalLocation', 'ICalDescription'


Attachment Fields

'InstanceId', 'ModuleId', 'AttachmentId', 'AttachmentType', 'FileNameOrig', 'ContentType', 'FileExtension', 'FileSizeKb', 'LastModifiedBy', 'LastModifiedDate', 'FileNameDisk', 'RandomKey', 'AttachmentTypeId', 'LastModifiedById'





Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.