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
Med-V
  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... :-)


 App-V
  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:

http:///sms_mp/.sms_aut?mplist

and

http:///sms_mp/.sms_aut?mpcert

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.


Thanks.

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.
http://myitforum.com/cs2/blogs/rhouchins/0401ConfigMgrTools.zip

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
clientAction.PerformAction
End If
Next
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-
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C36FCDA8-9336-4D44-9568-5530FF7635DD&amp%3Bdisplaylang=en
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

SELECT DISTINCT
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, real_total.total, ps.CollectionID
FROM (
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
FROM
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
WHERE
(pse.QNumbers NOT LIKE 'None')
AND (pse.ID NOT LIKE 'None')
AND (fcm.CollectionID = 'SMS000ES' )
GROUP BY pse.ID
, v_ApplicableUpdatesSummaryEx.Type
, fcm.CollectionID
HAVING
(v_ApplicableUpdatesSummaryEx.Type = 'Microsoft Update')) ps
INNER JOIN
(
SELECT DISTINCT ID0
FROM v_GS_PATCHSTATEEX
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
CROSS JOIN
(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
 
Thanks!

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.

--Outstanding=@Total-(@NotRequired+@Required)

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.language0,b.product0,b.reboottype0,b.scanagent0,
'b.severity0' = Case
When b.severity0 = 10 Then 'Red'
When b.severity0 = 8 Then 'Amber'
When b.severity0 = 6 Then 'Green'
else ' '
End,
b.status0,b.type0,b.title0,b.timeapplied0,b.timeauthorized0
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',
'MS08-020','MS08-021','MS08-022','MS08-031','MS08-032','MS08-033','MS08-034','MS08-035',
'MS08-036','MS08-037','MS08-045','MS08-046','MS08-047','MS08-048','MS08-049','MS08-050',
'MS08-051','MS08-052','MS08-053','MS08-058','MS08-061','MS08-062','MS08-063','MS08-064',
'MS08-065','MS08-066','MS08-067','MS08-068','MS08-069','MS09-001')
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:
SELECT DISTINCT Netbios_Name0
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

Name
TCP/IP Port

ACS forwarder to ACS collector
51909

Agent to Root Management Server
5723

Agent-less management
Uses RPC

Operations Console to Reporting Server
80

Operations Console to Root Management Server
5724

SQL Server 2005 (Default Instance)
1433

Web Console to Web Console server
51908, 445