:::: MENU ::::

Basic USMT Understanding with example of windows 7 to Windows 10 user profile migration

USMT is one of the Tool from ADK (Windows Assessment and Deployment Kit )

 

ADK_JPEG


 

 

User State Migration Tool (USMT) is a  tool that can be used to migrate user state, data and settings as part of a image build or outside of image.  It will process to transfer mapped drives,  favorites, and other settings listed below to a separate computer in hopes that it would help with machine refreshes and replacements.

 

With this USMT tool we are able to copy and restore the following:

  • Custom Map drives
  • STARTMENU configuration
  • QUICK LAUNCH settings
  • DESKTOP settings
  • Printers
  • User Accounts
  • PERSONAL
  • MYPICTURES
  • FAVORITES
  • MYVIDEO
  • MYMUSIC

Files with the following extensions:we can do customization if we want

  • .qdf, .qsd, .qel, .qph, .doc*, .dot*, .rtf, .mcw, .wps, .scd, .wri, .wpd, .xl*, .csv, .iqy, .dqy, .oqy, .rqy, .wk*, .wq1, .slk, .dif, .ppt*, .pps*, .pot*, .sh3, .ch3, .pre, .ppa, .txt, .pst, .one*, .vl*, .vsd, .mpp, .or6, .accdb, .mdb, .pub

Limitation of USMT ,Which it wont migrate following things :—

  • Files tagged with both the hidden and system attributes.
  • Files and folders on removable drives,
  • Data from the %WINDIR%, %PROGRAMFILES%, %PROGRAMDATA% folders.
  • ACLS for files in folders outside the user profile.
  • Local attached printers won’t migrate

How USMT will work :

USMT will work based on XML Files. USMT Package will have XML Files.Like MigApp.XML , MigUser.XML , MigDocs.XML , Config.xml etc. Each and every XML file has specific components to migrate.  If you want to read the XML file user the XML Editor for easy to understand.

I haven’t tried but you can also use the USMT GUI editor check out this .

so if you want to customize the XML file always better to create new XML file rather than editing the default XML file

in my example I’m attaching my XML file.

My requirement is user profile only requires to  restore .below are the points in detailed

  1. it should not restore any documents under C:\Drive (or any other folders under C drive).
  2. User desktop documents and user profile settings,shortcuts,Pin items,desktop wallpaper , favorites  , My pictures,my documents,etc
  3. Also under C:\temp\Printer\any files it should restore

download sample  XML files  it has my sample custom files Custom2.xml and Migration.xml  you needs to call at  Capture User Files and Settings in Task sequence and Restore User State

 

 


 

Sample SCCM Task Sequence :–

Assuming you already have the USMT package in place in SCCM console

Here Profile Capturing /Storing will be in 2 Ways

  • SMP (State Migration Point) : Its a role from SCCM. its kind of share location where we can use for profile storage

Advantages /Disadvantages : —

  1.   We should have enough space to store the user data . If your concern about space check out this to get some idea how much space you may requires Collecting USMT Estimates using ConfigMgr
  2.  With SMP It has it own mechanism for deleting the aged data or maintenance task
  3.  Also data will be encrypted and we can’t able to see the files. It will be stored in .MIG format
  4.  It will slow process to compare to Hard-Link process . Since data will be stored in remote location. Speed of the process will depends on the bandwidth
  • Local store / Wipe Mode (Hard-link migration) – It will create Links in the  Sectors of inside the Hard disk  and It will Wipe the HardDrive. When It restore calls.

Advantages /Disadvantages : —

  1. By using the linking mechanism from sectors it will restore the user data – So here We can’t  format 100% of the hard drive
  2. Since data will be stored under local hard drive.it will be faster to compare to SMP

 

 

Summary  of steps need to perform to migrate the StandAlone user profile [SMP]. :—

SCOPE :User  profile migration from Windows7 to Windows 10/other machine as your wish

  1. USMT Package should be ready with above Xml Files. Paste above XMl files under USMT folders
  2.  SMP Role should be in place
  3.  relate the old and new computers in Computer Association as shown 
  4. create a task sequence for capture in above case Task sequence 1
  5. create a task sequence for restore in above case Task sequence 2
  6. Create a Collection  called ” Capture ” and add the machine windows 7
  7. Deploy the Task sequence 1 on ” Capture “Collection. Make sure it should get success
  8. Create a Collection  called ” Restore ” and add the machine windows 10
  9. Deploy the Task sequence 2 on  ” Restore ”  Collection. Make sure it should get success

Above steps will explain in details in below :—

I have 2 machines windows 7 and Windows 10 / Or any other version windows7/8.1 etc

Now I want to transfer my user profile data from Windows7 machine to windows 10 machine

 

For this, we need 2 Task sequences  with below steps and  Computer Association

 

First we need to co-relate the machine relation with help of Computer Association.

computer association 1.

computer association 2

 

Here you can add specific user or all user etc settings

computer association 3

 

computer association 4

 

1 Task sequence — > For user profile Capturing – Here “Capture USMT “ Task Sequence Set will be use full as showing in below screenshots. This  Task sequence we will be run on Windows 7 machine

2. Task Sequence –>  To restore user profile.Here “Restore” Task Sequence Set will be use full as showing in below screenshots. This  Task sequence we will be run on Windows 10 machine

 


 

Task sequence 1 : ScanState [ For Capturing the User profile settings]

SCANSTATE1

SCANSTATE2

ScanState3

 

Task sequence 2 : LoadState [ For Restoring the User profile settings]

 

LoadState1

 

LoadState2

 


 

TrobleShooting Steps  and Logs info :—

  • You may face the issues in either capturing or Restoring stage which are related to USMT
  • At the time of running the task sequence it create a Folder “C:\_SMSTaskSequence” and download the USMT Package inside it and also it will create the folder “SMSTSLog”under C:\Windows\CCM\Logs\SMSTSLog
  • SMSTSLog folder contains the log at the time of running the task sequence .once task sequence completes it will move the logs to under CCM folder C:\Windows\CCM\Logs

For  capturing the user data below logs will help you

  1. SMSTS.log : General Task sequence execution info
  2. scanstate.log –  scans all user profiles and what files its scanned according to the XML defined
  3. scanstateprogress.log :  How much Percentage it completes

capturelog

For  restoring the user data below logs will help you

  1. SMSTS.log : General Task sequence execution info
  2. loadstate.log-  Loads all user profiles and what files it should restore according to the XML
  3. loadstateprogress.log :  How much Percentage it restored s it will shows

LoadStateLog1


 

Known Issues: —

 



 

  1. If you’re running multiple time on same machine irrespective of result . so might get certificate error.
  2. certificate error
  3.  Just delete the certificates from the  locations [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SystemCertificates\SMS\Certificates] It will resolve the issue 
  4. certificate

 



 

Failed to decrypt state encryption key

CA

sometime at the time creating the computer association it got sucked , hanged. Please remove it and recreate the computer association will fix above error

 

 



Most of the cases USMT will failed due to Invalid User Mapping or duplicate SID issue on Source machine while capturing.

This issue will occur due to domain migration when they are migrating the user profile with ADMT (Active Directory Migration Tool) They won’t delete the old domain SID’s

check out for more info http://networksteve.com/?p=5022

Please find the script which it will remove the Specified SID user profile SIDREMOVAL.vbs .It will takes the Registry back and removes the user profile if the user profile has duplicate SID.

 


List of Users to add in a SCCM User Collection

 

select SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain from SMS_R_User where SMS_R_User.UserName in (‘User1′,’User2’)



Software Updates Installed using SCCM or Manually

SELECT  sys.Name0,ui.BulletinID, ui.ArticleID,ui.Title,

CASE when (ucs.Status=2 and ui.IsDeployed=0 )then ‘Required_General’

when (ucs.Status=2 and ui.IsDeployed=1 )then ‘Required_ITICSDeploy’

WHEN (UCS.Status=3 and ui.IsDeployed=1 ) then ‘Installed_SCCM’

WHEN (UCS.Status=3 and ui.IsDeployed=0 ) then ‘Installed_Manual’

when UCS.Status=0 then ‘Unknown’  end as ‘Status’, case WHEN ui.severity=10 THEN ‘Critical’

WHEN ui.severity=8 THEN ‘Important’

WHEN ui.severity=6 THEN ‘Moderate’

WHEN ui.severity=2 THEN ‘Low’ WHEN ui.severity=0 THEN ‘AddOn’ end as ‘Severity’

FROM v_R_System sys

INNER JOIN v_UpdateComplianceStatus UCS ON   sys.ResourceID = ucs.ResourceID

INNER JOIN v_UpdateInfo UI ON   UCS.CI_ID = UI.CI_ID

WHERE –UI.IsDeployed=1 and

sys.Netbios_Name0=’computer1′

ORDER BY Status

 


Pull Distribution Points with Source Distribution Points SQL Query

SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [Source DP for Pull], dbo.v_DistributionPoints.IsPeerDP, dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [All Types of DP List], dbo.v_DistributionPoints.IsPXE, dbo.v_DistributionPoints.Description
FROM dbo.vPullDPFullMap INNER JOIN
dbo.v_DistributionPoints ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath


//////////******* List of Views /Tables with fields**********////////////

////////////////****************************** List of Tables with fields************************///////////////////////////

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +’/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE] FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C ON T.OBJECT_ID=C.OBJECT_ID JOIN SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE T.TYPE_DESC=’USER_TABLE’;

 

////////////////****************************** List of Views with fields************************///////////////////////////

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS


RDP Enable using PSEXEC

psexec \\XXXXXXXXXX reg add “hklm\system\currentcontrolset\control\terminal server” /f /v fDenyTSConnections /t REG_DWORD /d 0




SCCM Client Health Related Collections

Collection Name WQL Query
CCMEval – No Status select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result = 1 and DATEDIFF(day,SMS_CH_EvalResult.EvalTime,GetDate())<=7)
CCMEval – Failed Status select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result in (3,4,5) and DATEDIFF(day,SMS_CH_EvalResult.EvalTime,GetDate())<=7)
Client Installed but Not Approved for Use select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select ResourceID from SMS_FullCollectionMembership where IsApproved = 0 or IsApproved IS NULL) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System Where SMS_R_System.Client = 1)
Client Not Installed and Not Assigned* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1) and SMS_R_System.SMSAssignedSites is null  and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in (“SMS_AD_SYSTEM_DISCOVERY_AGENT”) and DATEDIFF(day,AgentTime,GetDate())<7)
Client Not Installed* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1) and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in (“SMS_AD_SYSTEM_DISCOVERY_AGENT”) and DATEDIFF(day,AgentTime,GetDate())<7)
Client Not Installed and Error 112 No Disk Space during Client Push* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name in (Select   InsStrValue from SMS_StatusMessage as stat   left outer join SMS_StatMsgAttributes as att   on stat.recordid = att.recordid   left outer join SMS_StatMsgInsStrings as ins   on stat.recordid = ins.recordid   WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)   AND MessageID = 3014  AND Win32Error = 112 AND DATEDIFF(day,stat.Time,GetDate())<7) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)
Client Not Installed and Error 5 Access Denied during Client Push* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name in (Select   InsStrValue from SMS_StatusMessage as stat   left outer join SMS_StatMsgAttributes as att   on stat.recordid = att.recordid   left outer join SMS_StatMsgInsStrings as ins   on stat.recordid = ins.recordid   WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)   AND MessageID = 3014  AND Win32Error = 5 AND DATEDIFF(day,stat.Time,GetDate())<7) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)
PFE Remediation Script – Agent Not Installed select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <14) and AgentName = ‘PFE Remediation’))
PFE Remediation Script – Outdated Script* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.PFE_ScriptVer != ‘14.7.3’ and ResourceID in ( select ResourceID from SMS_R_SYSTEM where DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <7 and AgentName = ‘PFE Remediation’)
HINV Missing Information select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model is null and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
HINV Missing Information select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like “%Windows%”) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Duplicate GUID* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_R_System.Name != SMS_G_System_SYSTEM.Name and SMS_G_System_SYSTEM.Name is not null )) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
HINV Last Scan Date Missing or Greater Than 30 Days* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where DATEDIFF(day,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate())<30) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Software Updates Compliance Status Missing select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from sms_r_system inner join SMS_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid = sms_r_system.resourceid   Join SMS_Softwareupdate on  SMS_Softwareupdate.CI_ID = SMS_UpdateComplianceStatus.CI_ID  where   SMS_UpdateComplianceStatus.Status = 0 or SMS_UpdateComplianceStatus.Status = 1 or SMS_UpdateComplianceStatus.Status = 2 or SMS_UpdateComplianceStatus.Status = 3) and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
HINV MIF Max File Size Exceeded select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name in (select SMS_StatusMessage.MachineName from SMS_StatusMessage LEFT OUTER JOIN SMS_StatMsgAttributes ON SMS_StatusMessage.RecordID = SMS_StatMsgAttributes.RecordID LEFT OUTER JOIN SMS_StatMsgInsStrings ON SMS_StatusMessage.RecordID = SMS_StatMsgInsStrings.RecordID where SMS_StatusMessage.MessageID = ‘2719’ AND  SMS_StatusMessage.Component = ‘SMS_INVENTORY_DATA_LOADER’ and DateDiff(dd,SMS_StatusMessage.Time, GetDate()) <7)
SINV Incomplete Inventory select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = “notepad.exe”) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
SINV Last Scan Date Missing or Greater Than 30 Days select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) < 30) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Client Heartbeat Missing or Greater Than 14 days* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select ResourceID from  SMS_R_System where (DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >14) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId NOT in (select ResourceID from  SMS_R_System where (DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <14) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)
Client Activity = Inactive* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0
CM Client Version Outdated* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ClientVersion < “5.00.8239.1000” and SMS_R_System.ResourceId in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1)
Clients Not Receiving Policy (Invalid Signature) select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_SYSTEM.SMSUniqueIdentifier in (select instr.insstrvalue from SMS_StatusMessage as sm join SMS_StatInsStr as instr on sm.recordid=instr.recordid where sm.messageid = 5448 and instr.insstrvalue like ‘GUID%’ and DateDiff(dd,sm.Time, GetDate()) < 7)
CI – DP Latency Threshold Exceeded select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – DP Latency Threshold” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – CCM Cache Size Configured Incorrectly select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – CCM Cache Size” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – CCMEVAL – No Recent Execution select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – CCMEVAL Recent Execution” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
Unsupported Domain or Workgroup select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup not in (“Energy”,”RegencyGas”,”SECHOU”,”ETC”,”PVR”,”EagleRock”,”PVRPartners”) and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=7) and AgentName = “Heartbeat Discovery”))
CI – Admin$ Share is Not Accessible select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Admin$ Is Accessible” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – BITS Throttling – Not Configured Properly select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId inner join SMS_CI_ComplianceHistory on SMS_CI_ComplianceHistory.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – BITS Throttling Policy” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_CI_ComplianceHistory.ComplianceStartDate,GetDate())<=7
CI – Group Policy Processing Issues select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – GPO Processing Check” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Pending Reboot (Pending File Rename Operation)* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – No Pending Reboot Due To File Rename Operation” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Pending Reboot (Software Updates)* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – No Pending Reboot Due To Software Updates” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Windows Update Scan Errors* select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Windows Update Scan” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Service Status – Microsoft Anti-Virus Not Running select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – Service Status – Microsoft Anti-Virus Running” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-CompliantCustomerSpecific” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – Bitlocker Encryption Not Enabled on C: select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – BitLocker Encryption Status” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Anti-Spyware State Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Anti-Spyware State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Anti-Virus State Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Anti-Virus State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Auto-Update Settings Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Auto-Update Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Firewall State Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Firewall State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Internet Settings Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Internet Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – User Account Control (UAC) Settings Not Recommended select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – User Account Control (UAC) Settings” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7
CI – WSC – Windows Security Center Service State Not Running select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.LocalizedDisplayName = “PFE – WSC – Windows Security Center Service (WSC) State” and SMS_G_System_CI_ComplianceState.ComplianceStateName = “Non-Compliant” and DATEDIFF(day,SMS_G_System_CI_ComplianceState.LastComplianceMessageTime,GetDate())<=7