December 23, 2010

SQL query to get packages which are advertised without updating DPs

SELECT dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.CollectionID,

dbo.v_Advertisement.PresentTime, dbo.v_Advertisement.ExpirationTime, dbo.v_Advertisement.SourceSite, dbo.v_Package.PackageID,
dbo.v_Package.Name, dbo.v_Package.Version, dbo.v_Package.SourceVersion, dbo.v_Package.SourceDate, dbo.v_Package.LastRefreshTime,
dbo.v_DistributionPoint.ServerNALPath, dbo.v_DistributionPoint.SiteCode, dbo.v_DistributionPoint.SiteName,
dbo.v_DistributionPoint.LastRefreshTime AS Expr1, dbo.v_DistributionPoint.Status, dbo.v_PackageStatusDistPointsSumm.ServerNALPath AS Expr2,
dbo.v_PackageStatusDistPointsSumm.SourceVersion AS Expr3, dbo.v_PackageStatusDistPointsSumm.SiteCode AS Expr4,
dbo.v_PackageStatusDistPointsSumm.State, dbo.v_PackageStatusDistPointsSumm.LastCopied, dbo.v_PackageStatusDistPointsSumm.SummaryDate,
FROM dbo.v_Advertisement INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_DistributionPoint ON dbo.v_Package.PackageID = dbo.v_DistributionPoint.PackageID INNER JOIN
dbo.v_PackageStatusDistPointsSumm ON dbo.v_DistributionPoint.PackageID = dbo.v_PackageStatusDistPointsSumm.PackageID
WHERE dbo.v_PackageStatusDistPointsSumm.state=1

Monitor your packagers ;)

November 29, 2010

When to use: Citrix, Med-V and App-V?

Microsoft and Citrix have introduced Virtual Desktop Infrastructure (VDI) which has below benefits for an enterprise- 
  1. Integrated Management
  2. Enhanced security and compliance
  3. Anywhere access from connected devices
  4. Increase business continuity
The Microsoft VDI Suites can especially provide tremendous benefits for customers that want to optimize desktop deployments for the following use cases-
  • Contractor devices/ third-party devices: provide managed and secured desktops to unmanaged PCs.
  • Remote Offices with excellent connectivity: centrally manage and easily deploy desktops to multiple remote and branch offices, thereby reducing IT efforts at those locations.
  • Task workers: offer choice of either session-based or virtual desktops to task workers, onsite or offshore.
  • Regulatory compliance: VDI desktops are locked behind the datacenter, thereby inherently complying with strict regulations in industries such as financial services, government, and healthcare.

I am just in review phase and can conclude main difference between Citrix XenApp, App-V and Med-V as below-
Citrix XenApp 
  1. Specially designed for session virtualization (with remote desktop services)
  2. Resolves application incompatibility with Windows upgrades
  3. User profiles are created on Citrix servers and user can easily access authorised applications.
  4. Applications need to be installed on citrix server. It does not need applications to be installed on user's machine.
  5. Users can access applications from anywhere (thru VPN) -- I don't know if it's a benefit or limitation? benefit as it supports mobility and maintains security ; limitation as it's not supported to offline mode.
  6. Integrated with AD to manage applications by groups.
  7. license cost would be applicable per user
  8. supporting limited number of sessions
  9. not applicable to desktop/application virtualization
  10. No specific reporting to licenses or total application usage by users.
  11. It requires less hardware than VDI
  12. most cost effective than VDI
  1. Specially designed for desktop virtualization
  2. Resolves application incompatibility with Windows Vista or Windows 7. MED-V delivers applications in a virtual PC that runs a previous version of the operating system (for example: Windows XP).
  3. It helps deploy, provision, control, and support the virtual environments.
  4. It can be easily integrated with SCCM
  5. Reporting limitations as we need to check out logs from Med-V server for each machines (during multicasting OS deployment for large organization; it's difficult to track on)
  6. Centrally managed via a MED-V management server
  7. It does not work on a virtualized operating system
  8. It creates a package with a full instance of Windows
  9. It runs two environments on a single PC
  10. It provides a mechanism for automating the first-time setup of virtual machines at the endpoint, including assignment of a unique computer name, performing initial network setup, and joining the virtual machine to a corporate domain.
  11. It provides central database of client activity and events facilitating monitoring and remote troubleshooting.
  12. It provides Web browser redirection of administrator-defined domains (such as the corporate intranet or sites that require an older version of the browser) from the endpoint browser, to a browser within the virtual machine.
  13. It offers a unique method for managing an easy to support virtual desktop environment. It takes advantage of hardware independence enabled by virtualization, and maintains the exact same image across multiple endpoints. All user changes to applications or the OS are discarded once the virtual PC session ends, and the virtual machine reverts to the original image, as packaged and delivered by the administrator. This can significantly simplify management, support, and troubleshooting for virtual machines. Updates, patches, new applications, and settings changes are applied to the master virtual image, tested by the administrator, and uploaded as a new version of the virtual image to the MED-V image repository. The new version is delivered to all endpoints using Trim Transfer technology, removing the need to update each endpoint separately. 
  14. MED-V provides a first-time customization process for every deployed virtual image, where the administrator can choose to join the virtual machine to an Active Directory domain. This way, administrators can patch, update, deliver applications, and apply policies using existing tools.
  15. It supports offline mode (Offline work permissions may be limited by the administrator to a predefined period of time, after which the user must reconnect to the management server and re-authenticate. This ensures users are kept up to date with the most recent policy and permissions, and enforces expiration and de-provisioning settings on end users).
  16. It maintains high availability (MED-V client operates independently of MED-V servers. If the management server is malfunctioning or has stopped responding, all clients already running a MED-V workspace may continue working. New attempts to start a MED-V workspace will run in offline mode. Only online authentication, policy changes, and image updates are unavailable, and client events are aggregated at the client side until the server is available again).
Note: there's alot features available in Med-V which are binding me to love it and apply it to production, Thanks to Microsoft for adding values... :-)

  1. Specially designed for Application virtualization
  2. Ability to sequence true 64-bit applications
  3. Multiple delivery options including dynamic streaming
  4. policy based application management including microsoft group policy
  5. It creates a package of single application and isolates from all other applications
  6. It resolves conflicts between applications and reduce testing
  7. It simplifies application delivery (eliminate install)
  8. Interoperable with SCCM
  9. Applications do not get installed or alter OS
  10. Applications are virtualized per instance (incl system files, registry, fonts, .ini, com/dcom objects, services, namespaces,etc)
  11. Multiple versions of same apps can be deployed together without fear of conflict
  12. Virtual apps do not permanently occupy HD space if you reset them after use
  13. some applications can not be sequenced; i.e Microsoft Office, Adobe Acrobat Standard/Pro.
  14. Some apps should not be sequenced; i.e. CS3 and AutoCAD 
  15. All workstations should have App-V clients.
Finally, I can reliable on Med-V until some more additions come to App-V.

Thanks :)

November 26, 2010

Troubleshooting Tips: Non SCCM & Unhealthy Client Machines

Troubleshooting Tips: Non SCCM & Unhealthy Client Machines

Sometimes the most challenging part of the Configuration Manager 2007/SMS 2003 deployment phase can be ensuring that the client successfully reports to the site server. We occasionally see these issues here in support, typically either as cases for clients not reporting after the client installation, or maybe where it’s noticed that the client count is decreasing from the collection.

When we look at the SMS/SCCM console collection, there is an entry for the client status that indicates either Yes or No. Assuming everything is installed and configured properly, a client installed on a system should automatically report as Yes, but sometimes that does not turn out to be the case. The reason could be that the client has not yet reported to the SCCM\SMS server, or it was reporting previously but has now stopped. Managing the client in the collection is a continuous task and for a healthy environment the client should be continuously reporting to the SMS\SCCM server.

There are various reasons why a client may not be able to report to even if the SMS\SCCM agent is installed on a machine. A few of these reasons are discussed below:

The first thing to check is whether the client is on the network, and if it’s not on the network, does the system even exist? It’s possible that represents a stale record from AD.

Systems NOT on the network: If the system is not actually on the network, check if it is shutdown, and if so if it’s been shut down for long time. If yes then first restart the system and then initiate the discovery cycle from the control panel agent properties action TAB.

Stale Entries: When you use AD discovery, the DDRs are created for the computers that reside in the AD container that we have requested to be queried by the discovery process. If that container has the stale records for the resources, then client records may be created for systems that don’t actually exist, thus they will never report.

There is a Maintenance task that will clear the inactive records but if the discovery process runs again and the AD container still has these entries then they will simply show up again.

Resolution: For the stale records you need to make sure that the AD container is cleared of these stale records and scavenging is done for the computers container in AD regularly. Once this is done you can either make use of the maintenance task or you can create a collection for the NON SMS CLIENTS and then do a delete special to the collection so that the entries will be removed permanently from the SMS\SCCM database. Then a discovery can be run which will bring back only the active systems in the collection.

Once the agent is available on the network and the client is installed, the client goes through the following actions as part of the reporting process:

1. Client location services identify the site code and the MP it is supposed to connect to.

2. The client connects to the Management Point and downloads the policies.

3. Once the policies are downloaded it sends the heartbeat record to the server.

4. Once the server receives this heartbeat record these are converted in to DDR and processed. This will set the client flag to 1 which will make the client status display as Yes in the console.

5. On a regular basis the agent will send the heartbeat and if no heart beat or inventory shows up for a length of time then the client flag will be marked as 0 by the client flag maintenance task, setting the client status to No.

So only if this process is completed and it continues to happen will the client remain reporting to the server. This is why I said earlier that client management is a continuous task. There can be a variety of reasons why this process might fail, and I’ve outlined a couple of them below:

The Boundaries of the Agent are not specified in the site server

If the client is not assigned in the console or the client is unable to discover the site code, make sure that the AD site or the IP subnet is added in the boundary list. The server will only allow those clients within its boundary to download the policies, so if you have not specified the boundaries the client will not be authorized and the policies will not get downloaded. For boundary issues you can use this as a reference:

In the client if you check the location services.log (log location: C:\Windows\System32\CCM\Logs), you can get the information of the site assigned to it as well as the MP it is reporting to. If it is not able to report properly, you need to make sure that the agent can communicate over the network to the site server successfully.

Unable to get the site code

If the client is not able to get the site code, you need to check first the boundaries as above, and also verify that the site information is published in the AD. You can check the last part of the sitecomp.log after you start the site component manager which will say that the components like the MP, SLP etc successfully published or updated. If you are unable to see that and you get access denied errors, make sure that the computer account has read\write permission to the system container in AD. Make sure the permission is flowing to the objects within and the objects below. If you are not publishing the information in AD then you need to make sure that the SLP is configured and working.

The client itself is not installed in the Agent

To confirm this, try checking ccmexec.log file from client machine or check ccm.log from server end.

Make a list if you find any of these issues-

1. Newly discovered client computers are not assigned to the current site

2. Advanced Client Push Installation is not enabled at the appropriate site

3. The SMS Client Configuration Manager cannot connect to the client Admin$ share or to the Remote Registry Service (IPC$)

4. The SMS Advanced Client Push Installation account is configured incorrectly or is missing or is locked out

5. The SMS Advanced Client cannot access the installation file on the SMS site server

6. The SMS Advanced Client cannot access the management point during an upgrade

7. The SMS Advanced Client displays a site assignment but does not appear as installed

8. The Client computer appears in collections with the following values:

Site Code Client Assigned Client Type

This occurs when one or more of the following conditions are true:

a) The collection information has not been updated. Collection updates usually run on a daily or weekly schedule. In this case, you must make sure that the collection information has been updated. You can manually update the collection membership, and then update the collection view.

b) The client computer shares the same SMSID with another client computer. This issue can occur when you use a disk image to install the SMS Advanced Client. Duplicate SMSIDs are also referred to as duplicate GUIDs. You must determine whether duplicate SMSIDs exist on the client computers. For more information about how to detect duplicate GUIDs and how to use Tranguid.exe to create a New SMS GUID for the affected clients.

c) The SMS Advanced Client is assigned. However, the SMS Advanced Client is not installed. You must verify that the SMS Advanced Client is installed successfully and is assigned to the site that you are viewing.

d) The Network Discovery method is enabled. When you use the Network Discovery method in Systems Management Server (SMS), it populates the IsClient fields in the database by using a Null value. If other discovery methods are enabled, the computer will appear in the collection as Assigned with no client installed even though the client is installed. To resolve this issue, disable the Network Discovery method. Also, verify that the Heartbeat Discovery method that is enabled by default has not been disabled. Then, wait for the specified Heartbeat Discovery polling interval to pass. When the clients send up new discovery data, the database is updated to reflect the correct values.

Note Only the Heartbeat Discovery method will set the client installation status to Yes. The Active Directory System discovery method does not update the IsClient field in the SMS database.

e) Heartbeat Discovery has not reported since the client was installed.

There is a name resolution issue in the Client.

Make sure that the client is able to communicate to the SMS\SCCM server using the FQDN as well as the NetBIOS name. Use Nslookup or ping to check the name resolution. If you can’t ping the server using the FQDN then you will have problems.

The client is behind a firewall

If clients are behind a firewall, it may be restricting it from contacting the SMS site server. Check if the necessary ports are opened.

MP not working as a result of which the policies are not getting downloaded

You first need to check to see whether the MP is working. For that you will need to check the mpcontol .log (Log location: \SMS\logs in SMS and \program files\Microsoft Configuration Manager\logs in SCCM). If it is showing a 200 OK status code then that means the MP is working.

If the MP is working fine and the client is unable to contact and download polices, you will have an error on download in the policyagent.log file on the agent (Log location: C:\Windows\System32\CCM\Logs). Before checking this though, check if the locationservices.log has the correct MP information. If it does have the correct MP information, make sure that the BITS service is started on the client. You can try the following URLs to verify that this is working:




Client is unable to download policy

You may also have issues downloading policies if the client agent has WMI corruption. If you suspect this to be the cause of your issue, if it is a XP client then follow these steps:

1. Uninstall SCCM client agent. Use the ccmsetup.exe /uninstall

2. Troubleshoot or rebuild WMI.

When to rebuild WMI : SCCM Client is not able to install on machines.

When to repair WMI : SCCM Client is installed on machines but inventory data is not reporting to SCCM database.

3. Restart the system and install the agent.
Server unable to process DDR

Once you find that the client is able to send the heartbeat data to the server, you next need to check on the server to see if these are getting processed successfully.

Clients going to NO after it had reported

1. The first reason for this is that the heartbeat discovery is enabled and that the DDRs are not reaching the server.

2. The second is that Clear Install Flag is running.

Solution: Initiate Discovery data collection cycle manually from client and update collection after few minutes.


November 24, 2010

Dynamic collection query to get machines in which specific advertisement has been failed

This dynamic query will help admin to list out machines in which specific advertisement has been failed and he can readvert it to dynamic collection.

SMS_R_SYSTEM.ResourceID not in (select

SMS_ClientAdvertismentStatus.ResourceID from
SMS_ClientAdvertisementStatus where
SMS_ClientAdvertisementStatus.AdvertisementID = "ADVxxxxx" and
SMS_ClientAdvertisementStatus.laststatusmessageID in (10009))

pls specify respective advertisement id to the query.

Client installed but showing as 'No' to SCCM console - troubleshooting tips

It generally happens during upgrade or restructuring of SMS/SCCM infrastructure. Anyway, if it happens with you, try below steps-

1. list out all these clients or make collection of these clients
2. run script to trigger discovery data collection cycle on listed machines or use right click tools to initiate discovery data collection cycle on machines listed in collection
3. once you have finished with above two actions, try update collection membership and refersh then.
4. Check status and make yourself happy.

Pls find link to download right click tools.

and here's script to initiate discovery data collection cycle on affected clients-

'copy below code to notepad and save it as discovery.vbs
'and run remotely on clients with help of psexec.exe utility

actionNameToRun = "Discovery Data Collection Cycle"

Dim controlPanelAppletManager
Set controlPanelAppletManager = CreateObject("CPApplet.CPAppletMgr")
Dim clientActions
Set clientActions = controlPanelAppletManager.GetClientActions()
Dim clientAction
For Each clientAction In clientActions
If clientAction.Name = actionNameToRun Then
End If
wscript.echo "Executed: " & actionNameToRun     ' if you want to get message

these are some more actions which can be used in above script as and when required-

'Software Metering Usage Report Cycle

'Request & Evaluate Machine Policy
'Updates Source Scan Cycle
'Request & Evaluate User Policy
'Hardware Inventory Collection Cycle
'Software Inventory Collection Cycle
'Software Updates Assignments Evaluation Cycle
'Peer DP Maintenance Task
'Machine Policy Retrieval & Evaluation Cycle
'MSI Product Source Update Cycle

Happy troubleshooting!

Packages stucked to copy on DP: 'Install Pending'

There might be different scenarios so apply fix as per need-

1. Packages are not copied to DP due to lack of permissions, pls check the necessary rights.
2. Check for package on affected DP whether it's present or not.
3. If not, check distmgr.log file on affected DP and manually copy .pck file from primary server to affected DPs and use PreloadPkgOnSite.exe tool to replicate package information to SCCM database.
here's info regarding this tool-
4. If package is present on DP but not updated to database or SCCM console; refresh DP again.
5. If still DPs not updated, try run these queries for affected DPs through central server-

update pkgstatus set Status = 2 where id = ' ' and sitecode = ' ' and type = 1

update pkgstatus set SourceVersion = 0 where id = ' ' and sitecode = ' ' and type = 1

6. After running above queries, refresh DPs again.

Happy troubleshooting!

SQL query to get patch compliance reports

ps.Bulletin AS Bulletin_No,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed - ps.Verified AS Unpatched,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed AS 'Total with Status',
ROUND((100 * (ps.Verified + .00000001)) / (.00000001 + ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed), 0) AS '% Compliant',
ps.Verified, ps.NoStatus, ps.Retrying, ps.PreSuccess, ps.Uninstalled, ps.PendReboot, ps.Failed,, ps.CollectionID
SELECT fcm.CollectionID,
pse.ID AS Bulletin,
SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / 2 AS Verified,
SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN pse.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
v_ApplicableUpdatesSummaryEx INNER JOIN
v_GS_PatchStatusEx pse ON v_ApplicableUpdatesSummaryEx.UpdateID = pse.UpdateID RIGHT OUTER JOIN
v_FullCollectionMembership fcm ON pse.ResourceID = fcm.ResourceID
(pse.QNumbers NOT LIKE 'None')
AND (pse.ID NOT LIKE 'None')
AND (fcm.CollectionID = 'SMS000ES' )
, v_ApplicableUpdatesSummaryEx.Type
, fcm.CollectionID
(v_ApplicableUpdatesSummaryEx.Type = 'Microsoft Update')) ps
WHERE (Language0 = 'English' Or LocaleID0 In ('0','9'))
AND ID0 <> 'none'
AND Type0 = 'Microsoft Update'
AND Severity0 = '10') As PatchList
ON ps.Bulletin = PatchList.ID0
(SELECT CollectionID, COUNT(ResourceID) AS total
FROM v_FullCollectionMembership
GROUP BY CollectionID
HAVING (CollectionID = 'SMS000ES' )) real_total
ORDER BY ps.Bulletin DESC

-- specify collectionID to get respective compliance rate

SQL query to get patch summary report for specific collection

declare @Total int, /* total count collection membership */

@SMSInstall int, /* count installed by SMS */
@OtherInstall int, /* count installed externally */
@Missing int, /* count missing patch */
@NotRequired int, /* count not requiring patch */
@Required int, /* count requiring patch */
@Outstanding int /* count outstanding */
/* count non-obsolete clients */
select @Total=count(*)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
where IsNull(sys.Obsolete0,0)=0 and sys.Client0=1
and fcm.CollectionID='IN000061'  /* specify collectionID here */
/* patches installed by SMS */
/* patches installed by others */
/* patches required by systems */
/* v_GS_PatchStatusEx already filters out obsolete clients */
select @SMSInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is not null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is not null and ps2.LastState=105 then ps2.ResourceID
else null end),
@OtherInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is null and ps2.LastState=105 then ps2.ResourceID
else null end),
@Missing=count(distinct case
when ps1.LastState is not null and ps1.LastState!=105 then ps1.ResourceID
when ps1.LastState is null and ps2.LastState is not null and ps2.LastState!=105 then ps2.ResourceID
else null end),
@Required=count(distinct case
when ps1.ResourceID is null then ps2.ResourceID else ps1.ResourceID end)
from (select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID='ms08-067' and QNumbers=958644 and
UniqueUpdateID is not null) ps1
full outer join
(select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID='ms08-067' and QNumbers=958644 and
UniqueUpdateID is null) ps2
on ps1.ResourceID=ps2.ResourceID
join v_FullCollectionMembership fcm
on (ps2.ResourceID is null and ps1.ResourceID=fcm.ResourceID) or
(ps1.ResourceID is null and ps2.ResourceID=fcm.ResourceID) or
(ps1.ResourceID=fcm.ResourceID and ps2.ResourceID=fcm.ResourceID)
where fcm.CollectionID='IN000061'
/* not requiring patch */
select @NotRequired=count(distinct fcm.ResourceID)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
join v_GS_SCANPACKAGEVERSION spv on fcm.ResourceID=spv.ResourceID
join (select upkg.PackageID, max(upkg.PackageVersion) as PackageVersion
from v_ApplicableUpdatesSummaryEx us
join v_UpdatePrograms upkg on us.UpdateID=upkg.UpdateID
where us.ID='ms08-067' and us.QNumbers=958644 and upkg.PackageType=1
group by upkg.PackageID) updpkg
on spv.PackageID0=updpkg.PackageID and spv.PackageVer0>=updpkg.PackageVersion
left join (select ResourceID
from v_GS_PatchStatusEx
where ID='MS08-067' and QNumbers=958644) ps
on fcm.ResourceID=ps.ResourceID
where fcm.CollectionID='IN000061' and
ps.ResourceID is null and IsNull(sys.Obsolete0,0)=0 and sys.Client0=1
/* outstanding computers */
Select @Outstanding=@Total-(@NotRequired+@Required)
select @Total as 'Computers in collection'
select @Required as 'Computers requiring update', 100*@Required/@Total as '% of Total'
select @SMSInstall as 'Computers updated by SMS', 100*@SMSInstall/@Total as '% of Total'
select @OtherInstall as 'Computers updated by external means', 100*@OtherInstall/@Total as '% of Total'
select @SMSInstall+@OtherInstall as 'Total computers updated', 100*(@SMSInstall+@OtherInstall)/@Total as '% of Total'
select @Missing as 'Computers missing update', 100*@Missing/@Total as '% of Total'
select @NotRequired as 'Computers not requiring update', 100*@NotRequired/@Total as '% of Total'
select @Outstanding as 'Outstanding computers', 100*@Outstanding/@Total as '% of Total'

--outstanding computers are the computers that have not ran that scan yet to know if they need the patch.


SQL query to get patch status report of production servers

-- It provides information about servers and their patch status as per MS bulletin ID and Qnumber.

select distinct a.name0,a.user_name0,b.id0,b.qnumbers0,
'b.severity0' = Case
When b.severity0 = 10 Then 'Red'
When b.severity0 = 8 Then 'Amber'
When b.severity0 = 6 Then 'Green'
else ' '
from v_r_system a,v_GS_PATCHSTATEEX b
where a.resourceid=b.resourceid
and b.id0 in ('MS08-003','MS08-005','MS08-006','MS08-007','MS08-008','MS08-010',
and b.qnumbers0 not in ('951746','955069','954459','954606')
and status0 like 'Applicable'
and a.operating_system_name_and0 like '%server%'

-- bulletinid and qnumbers are provided by server team. I pulled reports of servers which required these patches as per requirements.

Hope, It will help you to someway!

SQL query to get computer names which do NOT have specific file installed

-- It returns all computer names which do NOT have specific file installed:
FROM v_R_System
WHERE Netbios_Name0 NOT IN
(SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName = 'filename.exe')
ORDER by Netbios_Name0

Query to get machines with specific exe

SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe'

-- it returns machines with specific file name. You can change file name as per your  requirements.

SQL query to get untraceable laptops information

-- This query gets serial nos and retrieve information as machine name, user name and respective serial no.

SELECT a.name0, a.user_name0,
b.serialnumber0 from v_r_system a,
v_GS_PC_BIOS b where a.resourceid=b.resourceid
and b.serialnumber0 in ('x', 'y')

-- x,y are serial nos.
-- you can specify as much serial nos you want.

How App-V and SCCM Integration works? Architecture View

App-V and SCCM Integration Architecture

Plan for today: SCCM Administration Tips

Now onwards, I will start blogging on few SCCM Administration tips on daily basis. It would be very specific and would be helpful to all my community members.

Thanks :)

Ports configurations for SCOM


ACS forwarder to ACS collector

Agent to Root Management Server

Agent-less management
Uses RPC

Operations Console to Reporting Server

Operations Console to Root Management Server

SQL Server 2005 (Default Instance)

Web Console to Web Console server
51908, 445

August 30, 2010

What are different Software Inventory File types- .sid, .sic, .sis?

Just get a chance to look over different types of software inventory files and listing their brief descriptions-

.SID - Software Inventory Delta (used during delta software inventory)
.SIC - Software Inventory Complete (used during Full software inventory)
.SIS - Software Inventory of application used for Symbian OS.


June 16, 2010

SCCM is supported on SQL Server 2008

A clean installation of ConfigMgr RTM on SQL Server 2008 is not supported. You should upgrade instead. If you upgrade the site server database to SQL 2008 you should apply the following hotfix:
For ConfigMgr RTM -

A clean installation of ConfigMgr SP1 is supported but you should install the following hotfix:
For ConfigMgr SP1 -

what's new in OpsMgr R2?

Below are the new enhanced features of OpsMgr'07 R2-

1. Extends end to end monitoring of distributed applications to any workload running on Windows, Unix and Linux platforms.

2. Maximize availability of virtual workloads with integration with System Center Virtual Machine Manager 2008.

3. Improved management of applications in the data center . Delivers on the scale requirements of URL monitoring of your business.

4. Meet agreed service levels with enhanced reporting showing application performance and availability.

5. More efficient problem identification and action to resolve issues.

6. Increased speed of access to information and functionality to drive management . Faster load times for views and results.

7. Improved and simplified management pack authoring experience The Operations Manager 2007 R2 beta integrates the functionality delivered
within the Cross Platform Extensions Beta. New betas of the Interoperability Connectors will available shortly through the Operations Manager R2 Connect program.

Timeframe Decided for ConfigMgr R3

TAP Nominations Open - September 2009
Beta - January 2010
TAP Nominations close - February 2010
TAP Program commences - March 2010
RTM - December 2010

May 3, 2010

Configuration Manager 2007 R3 is coming by Q2

What's New in Configuration Manager 2007 R3

The following features are new and apply only to Configuration Manager 2007 R3:
Power Management. Provides a set of tools to allow the site administrator to configure standard Windows power settings across computers
Operating System Deployment Enhancements. Prestaged Media is a way to integrate with OEM factory imaging in order to leverage imaging at the OEM to speed up new hardware deployments
Dynamic Collection Evaluation. Allows you to more rapidly evaluate a collection membership by adding only newly discovered resources.
Delta Discovery. Performs an intermediate discovery cycle adding only new resources to the Configuration Manager 2007 database.
Collections. Allows you to search for and add resources to the specified collection.
Desired Configuration Management. Allows you to easily create collection of compliant or noncompliant computers in desired configuration management.
Supported Clients Per Hierarchy. Configuration Manager 2007 R3 supports up to 300,000 clients per hierarchy when using the default settings for all Configuration Manager 2007 features. This increase in supported clients is the result of improvements to the Active Directory synchronization and Collection Evaluation processes.

March 16, 2010

Be a part of Cloud Summit – a Virtual TechDays special on Cloud Computing!

Join us on Day 1 – March 17, 2010 to get in-depth insights from Microsoft Experts.
Block your calendar today for these invigorating Sessions on Cloud Computing and Virtualization.

register yourself:

February 19, 2010

ITMU Functionalities: How it works?

Advertisement Begins
Check %windir%\system32\ccm\logs\execmgr.log – All advertisements executed by the SMS client are written to this log. You should be able to find the AdvertisementID for the Scan. Also, look for Requesting content from CAS for package version ## – ## should be the current package source version for the Microsoft Updates Scanner. Finally, you should see the command line used (which contains “Scanwrapper.exe”), the process created, and the Raised Program Started Event for AD: .. At this point, ScanWrapper.exe has been launched.

ScanWrapper Begins
Check %windir%\system32\ccm\logs\ScanWrapper.log – This log is generated by ScanWrapper.exe – Use the Date/Time column to find the most recent Software Updates Scan Tool Started entry. It will perform checks for Windows Update Version, Client Version, etc. ScanWrapper.log will also show the “Source Directory” and “Cache Directory” for the CAB file. ScanWrapper then launches SMSWusHandler. *Note: Scanwrapper.log is also used for other Scanning tools, such as the Extended Software Update Inventory Tool (ESUIT).

SMSWusHandler Begins
Check %windir%\system32\ccm\logs\SMSWusHandler.log – This log is generated by SMSWusHandler.exe, and is used to initiate actions on the Windows Update Agent. Use the Date/Time column to find the most recent SmsWusHandler Started entry. After performing a Windows Update version check, you will see an entry that reads similar to this: ScanPackage serviceID being used for this search is {78cc3df0-6ae3-4990-ab7c-87aeffb4b7fc}. The log will pause on this entry for a few minutes, because SMSWusHandler has handed off the scan to the Windows Update Agent.

WindowsUpdate Begins (and Completes)
Check %windir%\WindowsUpdate.log – This log is generated by the Windows Update (Automatic Updates) agent, which is used for patch scan and installation. Use the Date/Time stamp (located at the beginning of every row) to find the most recent Logging Initialized entry. Follow the log for Added Update entries. **Some entries in this log may appear as errors, but are actually “normal” – review the help link for more information.

SMSWusHandler Completes
SMSWusHandler continues after the completion of WindowsUpdate, listing each potential update, and states whether “Applicable” or “Installed”, writes the data to and .xml file in the cache directory (e.g., C:\WINNT\system32\VPCache\\Results.xml), and finishes the log with SmsWusHandler Terminating.

ScanWrapper Completes
ScanWrapper continues after the completion of SMSWusHandler, by reading the results.xml file – (e.g., Patch information from C:\WINNT\system32\VPCache\\Results.xml). The log then writes the details of each potential update, (including MS KB and security bulletin ID), and states whether the patch is applicable or installed. Next, it writes the data to Win32_PatchState_Extended. Finally, the information in Win32_ScanPackageVersion is updated, and Scanwrapper exits

Advertisement Completes
Finally, execmgr.log completes with a message similar to the following: Execution is complete for program Microsoft Security Updates. The exit code is 0, the execution status is Success.

What is Binary Differential Replication in SCCM?

Binary Differential Replication, sometimes known as "delta replication," is used by Configuration Manager 2007 to update package source files with a minimum of additional network traffic.

When Configuration Manager 2007 updates the source files for a package, and the source files have already been distributed, it sends the parts of the package that have changed since the last time the package was sent (originally, as an update, or as a refresh). This minimizes the network traffic between sites, especially when the package is large and the changes are relatively small. A file is considered to be changed if it has been renamed, moved, or its contents have changed.

The originating site keeps the differences between the current version of a package and the previous five versions. If a child site or distribution point has one of the previous five versions of the package, the originating site will send the appropriate changes to that site. If the child site has an older version of the package, the originating site will send the entire package.

If the originating site sends the changed files for a package but the receiving site no longer has the package, or the package has been altered at that site, the receiving site will send a status message to the originating site reporting the problem.

In order for Configuration Manager 2007 to use binary differential replication, all receiving sites must first have received at least the initial version of the package. Until all receiving sites have the initial version, Configuration Manager 2007 will not use differential replication.

Care should be taken when distributing changes to a package's source files. If the path to a receiving site is closed, it is important that you not attempt to update the distribution point multiple times before the site address is again available. Each update will include the files from the previous update because the receiving sites will not yet have the previous update. As a result, the updates will include multiple redundant files, wasting network bandwidth.

The processing time for large packages can take an extended period of time (20-30 minutes in some cases or even longer, depending on the size of the package). During this package compression/decompression and hashing/signature-creating process, distmgr.log might appear to be idle, even though the process is continuing.

February 17, 2010

General SMS Console access tips

If you can’t add a site server name to your SMS console try adding an entry to your machines hosts file located at C:\WINDOWS\system32\drivers\etc\hosts.

Also, it may help to add the servers’ domain to your machines list of DNS entries.

To do this on the Windows XP based computer that is running the SMS Administrator console, follow below steps:

1. Click Start, click Run, type dcomcnfg.exe, and then click OK.
2. Locate the Console root node, expand Component Services, expand Computers, and then click My Computer.
3. Right-click My Computer, and then click Properties.
4. In My Computer Properties, click the COM Security tab.
5. In Access Permission, click Edit Limits.
7. In Permission for ANONYMOUS LOGON, click Allow setting for Remote Access.
8. Click OK two times.
9. Restart your computer.

February 16, 2010

WMI commands to refersh SMS Policy on machines

To Request policies:
WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000021}" /NOINTERACTIVE

To Evaluate(Apply) policies:
WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule "{00000000-0000-0000-0000-000000000022}" /NOINTERACTIVE

February 10, 2010

Query to get SQL Server version and edition

Pls use the below query to get the SQL server version and edition-

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

How SMS gets status report for custom MIF file?

SMS looks for the MIF file to be placed in either the machines temp directory (%TEMP%) or the system root directory (%SYSTEMROOT%). SMS also ensures that the MIF file was created after the program execution was started to make sure that it does not accidentially import incorrect status information.

Once SMS finds a matching MIF file, it will parse the file and, if valid, it will transmit the data back to the SMS server. It will then delete the MIF file from the file system.

If SMS does not find a matching MIF file, then it does not delete any files (MIF or otherwise) and returns a SUCCESS value if the exit code from the executed process was 0. Any other value results in a FAILED value being returned up to the SMS server.

The status MIFs generated on the clients must be saved in either the system %temp% or %Windir% directories. %Windir% is used if the user has sufficient privileges to write to that folder; otherwise the files are placed in the %temp% folder. The preprogrammed status MIF generation tools will automatically place status MIFs in these directories. If you generate status MIFs by using other techniques, you must ensure the status MIFs are placed in these directories.

February 2, 2010

How to obtain 100 % ConfigMgr Client Installation?

To ensure that all systems that are intended and targeted for the ConfigMgr client installation. The best client deployment method is using AD GPO that will apply 3 settings.

1. the ccmsetup parameters are place in the registry
2. the WSUS URL is place in the registry.
3. applies the ADM Client Assignment template.

Enable WSUS/SUP Client Installation. In the GPO add the WSUS URL for your SUP Site Role. As clients join the domain or connects to the network, the Windows Update Agent will scan against your WSUS server and the ConfigMgr client will be detected as not installed and WSUS will install the client treating the client as if it was a critical update.

The installation will start about 2 to 3 minutes after the client is detected as not installed. This method will also upgrade a client that is lower than the published version in WSUS. If a client is already installed with the right version, the client will be re-assigned to your site if not already assigned. Also as part of this GPO you will want to add the ADM Client Assignment template. This is comes with ConfigMgr 2007. This template will keep clients assigned to the site of choice.

Two things will happen automatically for you. If the client is un-installed for any reason, WSUS will re install the client on the next WSUS scan. If the client is re assigned to another site, the ADM template with automatically reassign the client back to the originating site code immediately.


Daily SCCM Administrative logs: ConfigMgr'07 Inboxes to Monitor

Listed here is a list of the ConfigMgr 2007 inboxes that should be checked on a regular basis to ensure that your site(s) function as expected.

A backlog of files can indicate problems accessing the site database.

A backlog of files can indicate problems accessing the site database.

A backlog of files can indicate a network corruption problem or a problem with the DDM

A backlog of files can indicate that the Software Inventory Processor cannot connect to the site database or that too many files were received.

A backlog of files can indicate problems with specific clients, with management points, or with the network that could cause data corruption.

A backlog of files can indicate that the Component Status Summarizer cannot process the volume of messages.

A backlog of files can indicate problems accessing the Systems Management Server (SMS) database

A backlog of files can indicate a bad custom MIF file or that a client computer cannot transfer the file correctly.

A backlog of files can indicate a bad DDR is preventing other DDR’s to process.

A backlog of files can indicate a network corruption problem or a problem with the DDM

A backlog of files can indicate a performance problem that is caused by a large number of messages.

A backlog of files in the folder indicates that the policy provider component is not running.

A backlog of files can indicate that the Scheduler is backlogged or is already processing files of the same priority

A backlog of files can indicate that the Sender cannot connect to or cannot transfer data to another site.

A backlog of .srq files indicates that the sender cannot process the number of jobs scheduled for that sender or that the sender cannot connect to or transfer data to another site.

A backlog of files can indicate that many send requests are not completed or that the Scheduler has not yet deleted the files.

A backlog of files can indicate that the Software Inventory Processor cannot connect to the site database or that too many files were received.

A backlog of files can indicate problems with specific clients, with management points, or with the network, causing data corruption.

A backlog of files can indicate a performance problem. Examine status messages for the Site System Status Summarizer for possible problems.

A backlog of files can indicate that some site systems' clocks are not synchronized with the site server.

A backlog of files can indicate a problem with the Status Manager or that the component is trying to process too many messages.

A backlog of files can indicate problems with the connection to the computer that is running SQL Server.

A backlog of files can indicate a problem with the Status Manager or that the Status Manager is trying to process too many messages

A backlog of .sum and .sur files can indicate that the Software Metering Processor component cannot connect to the SMS database.

What is BranchCache? How SCCM supports BranchCache?

Microsoft introduced a new terminology in Windows7 and Windows Server 2008 R2 called BranchCache to reduce traffic load on wide area network called BranchCache. Network enabled with BranchCache cache data in branch and subsequent request to same data is served by cached stored in WAN branch. BranchCache optimizes traffic flow between Windows Server 2008 R2 servers and BranchCache-enabled clients; Windows Server 2008 R2 servers and computers running Windows 7 can be configured as BranchCache clients.

BranchCache operates in one of two modes:

1. Distributed Cache: In Distributed Cache mode, BranchCache-enabled clients cache copies of files downloaded from content servers across the WAN and send them directly to other clients when requested. Distributed Cache mode is especially beneficial for branch offices that do not have a local server.
2. Hosted Cache: In Hosted Cache mode, a Windows Server 2008 R2 server, known as the Hosted Cache, acts as the host for the cached content. BranchCache-enabled clients cache data that they have requested and downloaded from content servers locally and use the Hosted Cache to retrieve data that is not available from their own local cache. Clients know the identity of the Hosted Cache and retrieve data from the Hosted Cache. For data not available from the Hosted Cache, the client downloads the data from the content server and offers it for caching to the Hosted Cache. Hosted Cache mode is beneficial in organizations that want to audit access to content in the local cache, or larger branch offices that have local servers.

BranchCache Hosted and Distributed cache modes
BranchCache improves the performance of applications that use one of the following protocols:

a.Hypertext Transfer Protocol (HTTP) and Hypertext Transfer Protocol Secure (HTTPS). The protocols that Web browsers and many other applications (such as Microsoft Internet Explorer®, Microsoft Windows Media Player®, and more) use.
b.Server Message Block (SMB), including signed SMB traffic. SMB is the protocol used for shared folders on Windows networks.
c.Background Intelligent Transfer Service (BITS). BITS is used to transfer files asynchronously between a client and a server. BITS is the protocol that System Center Configuration manager (SCCM) and Windows Server Update Services (WSUS) use.

February 1, 2010

What's new in ConfigMgr'07 R3?

1. Scale & Performance Improvements: Collections in R3

a. Microsoft is focusing on evaluating new systems in R3 and will implement new collection setting called 'Fast evaluation' which populates newly discovered machines.
b. Full evaluations are still processed in the same way.
c. A new collection needs a full evaluation to show existing clients.

How it works:
- Collections are evaluated by periodically executing a query
- results are inserted into a temporary table
- this table is then merged into the master collection results table (Collection Members)
- If there is no change in results, master results table not changed
- If onlya few resources have changed, evaluation process faster due to only processing changed resources.

2. Scale & Performance Improvements: Delta AD Discovery

a. Each AD discovery query has 2 tasks:
1. Discover any changes to any users or machines, based on the query, that would likely affect targeting (default is 5 minutes)
2. Perform a periodic "full scan" to capture users and machines last logged time, ensuring active users and machines are not made obsolete.

b. On an individual query basis, select to run "discovery now" for a full scan.

3. Scale Improvements: R3 supports 3,00,000 clients when using the default settings for all ConfigMgr 2007 features.

Note: No change to other site and site role supported numbers.

4. Sharepoint based ConfigMgr dashboard
- compliance metrics related to SUM, SWD, DCM, Licensing and OSD sections for a particular time period.
- sharepoint based authentication: customize dashboards based on User Roles.

What's new in ConfigMgr'07 Service Pack 2?

SCCM 2007 SP2 supports below platforms-
-Windows 7
-Windows Server 2008 R2
-Windows Vista SP2
-Windows Server 2008 SP2

New features added to SCCM 2007 SP2 are:
1. Managed Client Support - Client can be a target for apps, inventory, updates and more.

2. Site role host control - Servers can host all site infrastructure roles.

3. Improved Client Policy Evaluation -

a. Faster Policy Processing: before SP2, policy download was queued locally for 2 minutes before processing. This 2 mins delay has been removed in SP2.
b. Most efficient software distribution configured to run at user logon:
before SP2 user policy requests were not downloaded for 2 minutes after user logon event. This caused a delay is user/group targeted advertisements.
c. A common scenario is an App-V distribution environment where user/security group targeting is used.
d. this 10 mins delay has been removed in SP2 and user/group targeted advertisements are instantly available after user logon in SP2.

4. Branch cache support:
a. Integration enables configmgr organizations to
-significantly reduce WAN traffic
-reduce transfer loads on DPs.
b. Clients that are Branch Cache enabled will transfer content from peers if available before hitting DP.

5. SP2 will also continue to deliver new support for x64 architectures including:
a. x64 support for OpsMgr'07 client agent
b. Update to OpsMgr MP for x64 OS
c. x64 performance counters
d. Remote Control support added for x64 windows XP and x64 Windows Server 2003.
e. App-V x64 Client Support.

6. Asset Intelligence Certificate Requirement Removal:
ConfgMgr'07 SP1 introduced Asset Intelligence v1.5. With SP1, Asset Intelligence could be configured to use as online synchronization for updates. With SP2, the requirement to have the certificate has been removed.
-The initial release required a certificate.
Software Assurance is not required for this functionality, including SP1.

7. Intel vPro Technology: Integration Enhancements in SP2-

a. Wireless Profile Management
b. 802.1x support
c. Non volatile memory or third party data store (3PDS)
d. Access Monitor: Audit log
e. Remote Power Management: Power State Configuratio from SCCM console.

system center power management phases:
Monitor-> Plan-> Apply-> Check-> Report (saving in power consumption and costs and environmental impact)

8. OS deployment:

a. Multiselect and delete driver catalog drivers from the SCCM console
b. task sequence UI displays package names as in the SCCM console.

9. Better feedback on AD extension success/failure.

The "Windows Operating System" column has a "Not monitored" state for a new agent

Reason: This issue occurs because the new agent has the same NetBIOS name as a previously installed agent. When the agent is deleted from Operations Manager, the grooming of the deleted agent is hard coded to occur after two days. Therefore, the agent is not immediately groomed out of the database completely.

Solution: To work around this issue after the agent was deleted from the console, wait three days, and then add the new agent to Operations Manager. Or, make sure that two full days have passed, and then add the new agent to Operations Manager.

Package sending priority in Advertisement Properties

Sending priority
The priority of this package when sent to distribution points in child sites. Packages can be sent with High, Medium, or Low priority. The default setting is Medium priority. If a package has High priority, it will be sent before packages with Medium or Low priority. If a package has Low priority, it will be sent after packages with higher priority settings.

Note: A Package will be sent in the order in which they were created in the SMS Administrator console.

January 31, 2010

Package processing thread in distmgr.log file?

When I was troubleshooting the copy package issue as mentioned in my earlier post, I got some some messages "package processing thread in queue".

I started to find out the exact meaning of this and found that the threads are used for copying packages to distribution points. If we distribute more packages at one time than the number of threads then the package will be putting on queue. The retry count is used when a copy fails.

Note: In legacy version of SMS (without SPs), the number of distribution points that could be effectively managed by a site server is small because SMS allocates a single thread per package. This results in SMS copying content to one particular distribution point, and when successful, moving to the next distribution point.
From SMS 2003 SP1, it copies content to multiple DPs in parallel. Because of this change, the failure of a single DP does not halt software distribution. This change improves both reliability and response time for package deployment, and effectively allows a single site to support a much larger number of distribution points.

The following improvements and benefits have resulted from this change:

1. Less time for package distribution to all DPs of the site
2. A single site can support more distribution points
3. Site hierarchy can be simplified to replace some secondary sites with distribution points in some of scenarios.
4. Faster Software and Patch distribution.
5.Lower hierarchy deployment costs, which results in fewer site servers

Lower maintenance costs, because it is easier to manage a distribution point than a site

January 29, 2010

Failed to hash file, Win32 error = 64: Package not copying to DP

I got this error on one of my DP while I was trying to copy package on all the DPs.
I checked the distmgr.log file from the primary server from where i was copying it to the DPs and got the error.

As a resolution step, I removed package from that DP and copy it again.

It worked well.

January 27, 2010

Why MS integrated SQL Reporting Service with Configuration Manager 2007 R2?

With Configuration Manager 2007 R2, a new site role called "Reporting Services Point" was introduced that facilitates reporting using SQL Reporting Services 2005/2008. This is accomplished via a conversion wizard that ships with Configuration Manager 2007 R2 and allows the user to convert all the Configuration Manager reports that currently exist on that site server to SQL Reporting Services based reports and deploy them to the SQL Reporting Server.

Site Role Installation and Configuration

The following outlines the overall workflow in getting a SQL Reporting Services based reporting point up and running:

1.Pre-requisites: Any machine having a valid SQL Reporting Server 2005/2008 instance running on it.
2.Run the site role wizard and install the "Reporting Services Point" on the SQL Reporting Server. The site role wizard asks for a root folder name which is basically the folder on the reporting server under which all the reports will be deployed.
3.Once the site role wizard is completed successfully, you should see the server appearing under the Reporting Services node under the Reporting node in the administration console.
4.Right click on the server and launch the "Copy Reports Wizard"
5.Run through the "Copy Reports Wizard" and select all the reports that you want to convert to SQL Reporting Services based reports.
6.The wizard will then go through the selected reports, convert them into SQL Reporting Services based reports and deploy them to the reporting server under the folder specified in step 2. above.
7.The copy reports wizard groups all the reports based on report categories creates a folder for each report category and deploys the reports under the respective report category folder.
8.Once all the reports are deployed, you can see all the report folders in the administration console and run any of the reports from any of the folders. You have the option of running the reports from within the administration console or run the reports directly from SQL Reporting Services using the SQL Report Manager (web UI). The SQL Reporting server report manager URL has the following naming convention:
For the default SQL Reporting Server instance the URL to access the report and report folders would be:


For named SQL Reporting server instances the URL would be:


Other functionalities provided within the Configuration Manager administration console

1.Report subscription wizards to create subscriptions for any of the Configuration Manager reports

2.Report authoring tools:

Model based report wizard
The Configuration Manager 2007 R2 release ships two out-of-the-box report models one for Client Health Reporting and the other for Software Updates Management. The model based report wizard facilitates users to create custom reports using these report models.

SQL Based report wizard
The SQL based report wizard facilitates SQL savvy users to specify SQL queries and generate reports off of these queries. The wizard presents the users with a list of all available Configuration Manager database views and the corresponding columns to facilitate users to formulate SQL queries more easily and make the process less prone to errors and typos.

What are the benefits of SQL reporting services in SCCM?

Benefits of using SQL Reporting Services:

1.Ability to export reports to any other formats like Word, Excel, PDF etc.
2.Ability to create report subscriptions that can be scheduled to run at specific times and send out reports to interested people. A good user scenario around this would be to create a report subscription for the Software Updates reports and schedule them to run late on Tuesday night or early Wednesday morning after all the "patch Tuesday" updates are applied to all systems.
3.Report authoring experience is very much enhanced with the tools that come with SQL Reporting Services like SQL Report Designer. You could either create report models or create SQL-based reports and run them off of the SQL Reporting Server.
4.Timeouts can be configured on a per-report basis depending on which reports potentially return large amounts of data.
5.Since the reports are standard SQL Reporting Services reports, they can be easily imported and exported from one SQL Reporting server to another.
6.A common request from customers is to be able to run reports off of a Configuration Manager database replica before enabling them on the production environment. This is a gem of a functionality that can be easily accomplished by simply making the data source for the reports point to any valid Configuration Manager database; in this case point the data source of the reports to the database replica and once they have been verified just change the data source to point to the actual production database. This proves to be very useful for benchmarking environments.
7.Report branding is another frequently requested functionality by many customers. This commonly entails customizing the look and feel of reports by changing fonts, font sizes, custom logos etc. With the ability to create custom reports using SQL Reporting Services, customers can now apply their own report branding to the reports.
8.SQL Reporting Services provides the functionality to enable report caching to facilitate lower execution times on subsequent report execution requests. The cache timeout value can be configured appropriately depending on how often you expect the report data to change.
9.Report snapshots that are an alternative to report caching and can be scheduled to execute at specific times. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database, and shows the data and layout that were current for the report at the time the snapshot was created.

SCCM Reports: Useful Microsoft links

Below are the Microsoft links which helps to understand SCCM reports.

Reports home page:

How to manage reports:

Technical reference for reporting:

January 26, 2010

What is difference between Obsolete and Inactive Clients?

I had to understand the difference as it was asked by the management and every administrator should know it.

Obsolete Clients

Obsolete client s are those that have been replaced by new ones. This usually happens during refresh OS deployments where the hardware stays the same and thus the hardware id is the same but the SMS GUID changes because the OS has been reloaded or the GUID is regenerated for another reason but the hardware remains the same.

Reasons - 
1. hard disk swapping
2. Renaming machines
3. Reimage OS
4. Reinstalling SMS/SCCM agent on the machines without proper uninstall.  

Inactive Clients

Inactive client s are those that have not been discovered recently by the heartbeat discovery. The definition of recently is defined in the delete task as a number of days. Please note that obsolete client s are also marked inactive. 

1. Offline machines
2. Machines having DNS issue/No name resolution
3. Machines are in inventory stock

Note: While I was trying to figure out why the some of the machines come under no status or waiting state, the above difference has helped me a lot.
I am putting some scenario here-

I have 100% healthy sms clients in the company's infrastructure and perform the activities like deleting obsolete clients, removing AD stale objects on daily basis. Inspite of this, I used to get some machines in "no status" and "waiting" category.

The reasons, I figured out, were:
offline machines/no name resolution machines were in "waiting" category and machines which were in IT stock or were inactive for a period of time, listed under "no status" category.

January 22, 2010

Reasons to malfunctioning of SMS/SCCM Clients

SMS Client Malfunctioning: Possible reasons

1. Machines are not in network.
2. AD Stale Objects
3. Machines not coming under the specified site boundaries
4. Site Code Not Assigned to the machines properly
5. Name Resolution Issue
6. Firewall restrictions: Relevant Ports used by SMS/SCCM should be opened
7. Machines with duplicate GUIDs
8. Policies are not getting downloaded from MP (Management Point)
9. Management Point not functioning well
10. The SMS Client Configuration Manager cannot connect to the client Admin$ share or to the Remote Registry Service (IPC$)
11. The SMS Advanced Client Push Installation account is configured incorrectly or is missing or is locked out
12. Advanced Client Push Installation is not enabled at the appropriate site
13. Newly discovered client computers are not assigned to the current site
14. The SMS Advanced Client Network Access Account is configured incorrectly or is missing or is locked out in a non-Active Directory environment
15. The SMS Advanced Client cannot access the installation file on the SMS site server
16. Clear Install Flag is running: As a result, sms client will reported as NO.

Explanation on SCCM Discovery Methods - What exactly they discover?

1. Heartbeat discovery
It pings all the machines existing in the network, i.e domain and workstation group machines are discovered automatically and shown under "All Systems" collection. If we enable Heartbeat Discovery, the discovery data is refreshed on a schedule that you determine. If we disable Heartbeat Discovery, the discovery data is refreshed only when another discovery method is invoked or run on a schedule.

2. Windows User Account Discovery and
It discovers all user accounts in same domain.

3. Windows User Group Discovery
Windows User Group Discovery is useful for creating group-based collections for software distribution

4. Network Discovery
Network Discovery discovers the client operating system only if the computer is configured to share resources. It discovers the following-
Topology and client
Topology, client, and client operating system

5. Active Directory User Discovery
It discovers the following:
User name
Unique user name (includes domain name)
Active Directory domain
Active Directory container name
User groups (except empty groups)

Note: We should run Active Directory User Discovery only on primary sites.

6. Active Directory System Discovery
It discovers the following:
Computer name
Active Directory container name
IP address
Assigned Active Directory site

Note: Polling performed by Active Directory System Discovery can generate significant network traffic (approximately 5 KB per client computer).

7. Active Directory Security Group Discovery
It discovers the following:
Domain Local Security groups
Domain Global Security groups
Universal Security groups

Note: We can run Active Directory Security Group Discovery only on primary sites. It polls Active Directory for all system resources in its database, including those discovered at child sites, and including secondary sites. Because Active Directory Security Group Discovery does not contact the computers directly, the computers do not have to be turned on to be discovered.

8. Active Directory System Group Discovery
It discovers the following:
Organizational units
Global groups
Universal groups
Nested groups
Non-security groups

Note: We can run Active Directory System Group Discovery only on primary sites. It polls Active Directory for all system resources in its database, including those discovered at child sites, and including secondary sites. Because Active Directory System Group Discovery does not contact the computers directly, the computers do not have to be turned on to be discovered.

January 21, 2010

How to Manage Obsolete Clients?

If you want to get accurate deployment success rate(%), it's required to manage obsolete client on daily basis.

What I do normally:
Open SMS Administrator Console-> Expand Site Hierarchy-> Expand Site Settings->Expand Site Manitenance-> Select tasks-> open "Delete obsolete Client Discovery data" properties and enable it.

You need to delete data older than 1 days.

After that click on the "All Systems" collection and check whether any client still shows as obsolete. You can delete those client from the console itself.

Note: During the deployment, package can be targeted to non obsolete clients only.

Troubleshooting Management Point Issue : Steps to be taken

MP Issues Desription:
Failed to send http request /SMS_MP/.sms_aut?MPLIST. Error 12029 SMS_MP_CONTROL_MANAGER 1/11/2010 4:51:40 PM 3924 (0x0F54)

Http verification .sms_aut (port 80) failed with no header received SMS_MP_CONTROL_MANAGER 1/11/2010 4:51:40 PM 3924 (0x0F54)

How to Handle:
Within IIS, a virtual directory is added under the default website during the Management Pointinstallation. The virtual directory is called “SMS_MP” (without the quotes). This virtual directoryis how the advanced clients are able to communicate with the MP and ultimately via theISAPI’s convert the data transmitted to the MP to files and information for insertion into the SMS database.

Need to mention that the MPControl is a self-checking component of the Management Point. In case it’s giving error messages first you need to check if the functionality is working at all.

A good test would be to check if a given client talking to that MP can send up HW inventory (you can check in resource explorer) AND if the client can get policy (policy spy on the client)

In order to send a Full HW Inventory you need to fire this vbs on the client and the trigger a HW Inventory cycle

Dim oLocator
Set oLocator = CreateObject("WbemScripting.SWbemLocator")
Dim oServices
Set oServices = oLocator.ConnectServer( , "root\ccm\invagt")
' Delete the specified InventoryActionStatus instance
x = "{00000000-0000-0000-0000-000000000001}"
oServices.Delete "InventoryActionStatus.InventoryActionID=""" & x & """"

If the functionality is ok, most likely only the self tests are wrong. In this case you need to check with the MP troubleshooter or with the URL’s. The cause could most likely be network related

If the functionality is wrong we need to check
IIS permissions (clients have anonymous access? Is the IUSR and the IWAM account locked?)

The SMS Management Point and SMS Agent Host service consist of several COM objects. TheSMS Agent Host service usually runs under the context of LocalSystem, so increased DCOMsecurity does not often cause a problem for the Advanced Client. The SMS Management Point, however, runs under the identity of the IWAM account, so any additional restrictions on DCOMsecurity can prevent the MP from functioning. If the MP does not start under the IWAM identitiy, but uses either a copy of this account or an entirely new account, then default permissions may not be enough to start the MP.

SQL (Has the MP account a “clear way” through the OS and SQL permissions to the SQL tables? Use SMS groups on the site servers!!)

Status Message Codes in IIS
If the client’s request does appear in the web service log, the next field to look for is the status code. The three digit return code of an http request will consist of two parts. The first digit will indicate the general status.

General Status Codes in IIS
First Digit General Status
2xx Success
3xx Redirection
4xx Client Error
5xx Server Error
The second two digits will give a more descriptive explanation of the status. In some
instances, such as a 401 or 403 error code, there will be a sub code, such as 401.1 or 403.4
A complete list of IIS status codes can be found in the following article:

294807, “HOW TO: Turn Off the Internet Explorer 5.x and 6.x "Show Friendly HTTP Error
Messages" Feature on the Server Side”;EN-US;294807

UrlScan version 2.5 is a security tool that restricts the types of HTTP requests that Internet Information Services will process. By blocking specific HTTP requests, the UrlScan security toolhelps prevent potentially harmful requests from reaching the server.URLSCan is an ISAPI filter that was designed to block extremely long or incorrectly formatted
web requests, which are common means of expoiting buffer overflows. It also can block avariety of verbs and commands in web requests that can exploit security holes orconfiguration errors.
URLScan 2.5 consists of URLScan.dll, the ISAPI filter, and URLScan.ini, the configuration file. The SMS 2003 toolkit has a modified version of the URLScan.ini file that allows theManagement Point ISAPI extensions to pass through. Any previous version of this ini file will cause URLScan to block client communication with the management point. Clients will be able to download packages for advertisements they already know about, but they won’t be able to get policy updates or upload inventory. An incorrect version of URLScan on an SMS MP will show up in the IIS logs as:

2005-02-04 17:03:48 GET /ccm_system/request - 80 - ccmhttp 404 0 2
2005-02-04 17:03:48 GET /ccm_system/request - 80 - ccmhttp 404 0 2
2005-02-04 17:03:50 GET /ccm_system/request - 80 - ccmhttp 404 0 2

NTFS Permissions for IUSR
This section will talk about the standard default NTFS permissions in a typical SMS environment. In a typical SMS environment, you will have a Management Point, a Reporting Point; BITS enabled Distribution Point, and a Server Locator Point. Each of these SMS site components requires a virtual directory within IIS and subsequently NTFS permissions for each of those virtual directories.
Below is the default breakdown for those SMS components for reference.
Management Point (SMS_MP virtual directory)
○ Default path: c:\SMS_CCM\SMS_MP
○ Default NTFS Permissions:
■ Administrators-Full Control
■ Interactive-List Folder Contents
■ IUSR account-List Folder Contents
■ IWAM account-List Folder Contents
■ SYSTEM-Full Control
Management Point (CCM_Incoming virtual directory)
○ Default path: c:\sms\ccm\incoming
○ Default NTFS Permissions:
■ Administrators-Full Control
■ IUSR account-Special:
□ Traverse Folder/Execute File
□ List Folder/Read Data
□ Read Attributes
□ Read Extended Attributes
□ Create Files/Write Data
□ Create Folders/Append Data
□ Delete subfolders and files
□ Read Permissions
■ IWAM account Special:
□ Traverse Folder/Execute File
□ List Folder/Read Data
□ Read Attributes
□ Read Extended Attributes
□ Create Files/Write Data
□ Create Folders/Append Data
□ Delete subfolders and files
□ Read Permissions
■ SYSTEM-Full Control
Management Point (CCM_Outgoing virtual directory)
○ Default Path: c;\SMS\CCM\Outgoing
○ Default Permissions:
■ Administrators-Full Control
■ IUSR Account-Read
■ IWAM Account-Read
■ SYSTEM-Full Control
Management Point (CCM_SYSTEM virtual directory)
○ Default Path: c:\SMS\CCM\ ServiceData\System
○ Default Permissions:
■ Administrators-Full Control
■ Interactive-List folder contents
■ IUSR Account-List folder contents
■ IWAM Account-List folder contents
■ SYSTEM-Full Control
Reporting Point (SMSReporting virtual directory)
○ Default Path: C:\inetpub\wwwroot\SMSReporting_
○ Default Permissions:
■ Administrators-Full Control
■ SMS Reporting Users
□ Read & Execute
□ List Folder Contents
□ Read
■ SYSTEM-Full Control
BITS Distribution Point (SMS_DP_SMSPKGC$)
○ Default Path: C:\SMSPKGC$
○ Default Permissions:
■ Administrators-Full Control
■ Guests
□ Read & Execute
□ List Folder Contents
□ Read
■ Users
□ Read & Execute
□ List Folder Contents
□ Read
Server Locator Point (SMS_SLP virtual directory)
○ Default Path: C:\SMS\BIN\I386\SMS_SLP
○ Default Permissions:
■ Administrators-Full Control
■ Everyone
□ Read & Execute
□ List Folder Contents
□ Read
■ SYSTEM-Full Control
Resetting the Password for IUSR
This section will describe how to perform a manual IUSR reset if the issue arises where the
IUSR becomes out of sync via either a attempted manual removal of IIS or a failed attempt to
reset the password via the AD Users and Computers or local user interface if a member
1. Reset the IUSR Password via the local user reset password option or use AD Users and
Computers if the machine happens to be a domain controller.
2. Reset the IUSR Password in the metabase.xml or metabase.bin file using the Metabase
Explorer tool which can be downloaded from the below URL link:
a. Open metabase explorer on the target machine where the password will be reset.

- A good plan is also to take a network trace from traffic between client – MP and MP – server

SMS Query to get Server Inventory Report

For the server inventory, pls run the below query on SQL server management studio.

select distinct a.name0,a.user_name0, a.operating_system_name_and0, a.ad_site_name0,
from v_r_system a,v_GS_COMPUTER_SYSTEM b,v_GS_PC_BIOS c,
where (a.resourceid=b.resourceid and b.resourceid=c.resourceid
and c.resourceid=d.resourceid and d.resourceid=e.resourceid
and e.resourceid=f.resourceid and f.resourceid=g.resourceid
and g.resourceid=h.resourceid)and a.operating_system_name_and0 like '%server%'

For any further help, pls leave a comment.

SMS Query to get Asset Inventory Report of all the workstations

I created this sms query to get the TCO report for the management:(Which now being used as Asset Inventory Report)

select distinct a.name0,a.user_name0, a.operating_system_name_and0, a.ad_site_name0,
from v_r_system a,v_GS_COMPUTER_SYSTEM b,v_GS_PC_BIOS c,
where (a.resourceid=b.resourceid and b.resourceid=c.resourceid
and c.resourceid=d.resourceid and d.resourceid=e.resourceid
and e.resourceid=f.resourceid and f.resourceid=g.resourceid
and g.resourceid=h.resourceid)and a.operating_system_name_and0 like '%workstation%'

After getting the data, you need to keep these data in excel sheet and apply the advanced filter as some of the time, we get duplicate data after running query.

For any further help, pls mail me or leave a comment.

January 19, 2010

TechNet Webcast: Technical Overview: System Center Configuration Manager 2007 SP2 and R3 (Level 200)

Here's the link below:

Let's enhance our skills.

What's New in Microsoft System Center Operations Manager 2007 R2 ?

Microsoft System Center Operations Manager 2007 R2 delivers end-to-end service management of applications and IT services running across your datacenter fabric, providing you greater control and insight into the health and performance of your Microsoft, UNIX, and Linux servers, and the workloads running on them. With Operations Manager 2007 R2, you can reduce the cost of managing your datacenter, and assure delivery of IT services to expected and agreed levels.

Download Operations Manager 2007 R2:

Enhances application performance and availability across platforms in the datacenter through cross platform monitoring, delivering an integrated experience for discovery and management of systems and their workloads, whether Windows, UNIX or Linux.

Download the Service Level DashboardEnhances performance management of applications in the datacenter with service level monitoring, delivering the ability to granularly define service level objectives that can be targeted against the different components that comprise an IT service.

Increases the speed of access to monitoring information and functionality with UI improvements and simplified management pack authoring. Examples include an enhanced console performance and dramatically improved monitoring scalability (e.g., over 1000 URLs can be validated per agent, allowing scaling to the largest of web-based workloads)

Basic difference between .MSI and .MST File

Packages (.MSI files)

This is the file that contains the instructions for MSIEXEC.EXE to install the application. The MSI file is a Database file format and is now the preferred application packaging format for the windows platform. Sometimes the MSI file gets too big and some or all of the files are placed in a .CAB file.

Transforms (.MST files)

In the MSI world, if you didn't create the MSI file, you want to keep the MSI file from the developer intact. To make changes beyond what the original MSI does you use a transform. The transform is applied at the time that the MSI package is installed.If you would repackage an application and it would fail, the Original Developer of the application would sometimes refuse to support it since repackaging strips out their installation logic.

If you create your own MSI packages you can also use transforms to change some parameters for each department of your company. That way you have only one package to maintain and nobody can accuse you of doing a better package for one department vs. another.

Note: Transform files are in fact MSI files with a different file extension. The contents of both files are merged together at install time. They are not supposed to add files to the package but there are way. Wise Package Studio does allow adding files using Transforms but they create a CAB file to bring files in without breaking the MSI rules.

MSI Authoring Tools

MSI Authoring Tools: The below are tools which can be used to edit MSI packages-

•InstallShield Developer
•Wise for Windows Installer
•InstallShield Express
•InstallShield DevStudio
•Instyler EX-it!
•MaSaI Editor
•Wise for Visual Studio.NET

InstallShield Command Line Parameters

The following are the InstallShield Command Line Parameters:

/v Passes parameters to MSI package.
/s Causes setup.exe to be silent.
/l Specifies the setup language.
/a Performs administrative installation.
/j Installs in advertise mode.
/x Performs setup uninstall.
/f Launches setup in repair mode.
/w Setup.exe waits for the installation to finish before exiting.
/qn A Windows Installer MSI parameter that causes everything but setup.exe to be silent. This sets the user interface level to zero.