Tuesday, February 17, 2015

SCCM Computer Model SQL report

This is a sample report for HP Elitebook 8440p model. You can replace the expected model value. 

select distinct 
a.Name0 as 'Host Name', 
a.UserName0 as 'UserName', 
c.Caption0 as 'OS Detail', 
a.Model0 as 'Model' 
from v_GS_COMPUTER_SYSTEM a
inner join v_GS_OPERATING_SYSTEM c on c.ResourceID=a.ResourceID
where a.Model0 like 'HP EliteBook 8440p%'

Friday, January 31, 2014

Deploy Windows Live Photo Gallery & Movie Maker via SCCM

Microsoft Windows Live Photo Gallery & Movie Maker are one of the useful image & video management tools even in a professional environment. Having tried multiple ways to silently deploy this tool, I found a blog post of pcloadletter which greatly helped with the command line for the application (wlsetup-all.exe).

I had even tested the deployment script through SCCM on a test machine and it worked like a charm. Hence I thought I could share his post and the finding here.

Though the post says about Installing on Windows 8 OS, I had tested the same command line install on my Windows 7 machine. 

http://pcloadletter.co.uk/2012/08/22/deploying-windows-photo-gallery/

Also sharing here the link to TechNet forums page, in which a fellow SCCM admin had tried the command line for installing Windows Live - Photo Gallery:
http://social.technet.microsoft.com/Forums/systemcenter/en-US/b1cb271e-fbea-461c-a1f5-e3d52e62ce1d/sccm-distribution-of-windows-live-photo-gallery

Friday, November 8, 2013

Using SCCM Queries - step by step document

Here is an Excellent Document I found on the Internet shared by questnet.edu which details on how to use the 'Queries' feature in SCCM Console. 


Queries are run against SCCM site's database and retrieve specific data according to the criteria of the query. SMS provides many predefined queries, and if required, we can create additional queries.

Query-based collections are dynamic objects. If a resource no longer meets the collection's query, it is automatically removed from the collection. And if a resource that originally did not meet the collection's query has changed in a way that it now meets the collection's query, it is automatically added to the collection. This behavior greatly reduces and simplifies the administrative work of managing the clients.

Ref. Link: https://www.questnet.edu.au/download/attachments/20873328/Using+MS+SCCM+Queries.pdf

Wednesday, October 23, 2013

SCCM Roles applicable for Central, Primary & Secondary Sites

The below table from Microsoft will help us to identify the Site System roles that we can install at each type of site in a System Center 2012 Configuration Manager hierarchy, and whether the site system role provides functionality for its site only, or for the entire hierarchy. We can install any supported site system role on the site server computer or on a remote site system server at a central administration site or primary site. At a secondary site, only the distribution point is supported on a remote site system server.

Site system role
Central administration site
Child primary site
Stand-alone primary site
Secondary site
Site-specific or Hierarchy-wide option
Application Catalog Web Service Point
No
Yes
Yes
No
Hierarchy
Application Catalog Website Point
No
Yes
Yes
No
Hierarchy
Asset Intelligence Synchronization Point1
Yes
No
Yes
No
Hierarchy
Certificate Registration Point
Yes
Yes
Yes
No
Hierarchy
Distribution Point2,5
No
Yes
Yes
Yes
Site
Fallback Status Point
No
Yes
Yes
No
Hierarchy
Management Point2,3,5
No
Yes
Yes
Yes
Site
Endpoint Protection Point
Yes
No
Yes
No
Hierarchy
Enrollment Point
No
Yes
Yes
No
Site
Enrollment Proxy Point
No
Yes
Yes
No
Site
Out of Band Service Point
No
Yes
Yes
No
Site
Reporting Services Point
Yes
Yes
Yes
No
Hierarchy
Software Update Point4,5
Yes
Yes
Yes
Yes
Site
State Migration Point5
No
Yes
Yes
Yes
Site
System Health Validator Point
Yes
Yes
Yes
No
Hierarchy
Windows Intune Connector
Yes
No
Yes
No
Hierarchy

1 Configuration Manager supports only a single instance of this site system role in a hierarchy.

2 By default, when you install a secondary site, a management point and a distribution point are installed on the secondary site server.

3 This role is required to support clients in Configuration Manager. Secondary sites do not support more than one management point and this management point cannot support mobile devices that are enrolled by Configuration Manager.

4 When your hierarchy contains a central administration site, install a software update point at this site that synchronizes with Windows Server Update Services (WSUS) before you install a software update point at any child primary site. When you install software update points at a child primary site, configure it to synchronize with the software update point at the central administration site.

5 Prior to System Center 2012 R2 Configuration Manager, all site system roles at a secondary site must be located on the site server computer. The only exception is the distribution point. Secondary sites support installing distribution points on the site server computer and on remote computers. Beginning with System Center 2012 R2 Configuration Manager, the state migration point can also be installed on the site server computer or on a remote computer, and can be co-located with a distribution point.

Thursday, October 17, 2013

Remove Delete Special option from SCCM console

I found this TechNet forum post interesting on which the SCCM team wants to control their engineers from using Delete Special. Though you can set Role based permission to engineers, there are cases where mistake happens by experienced engineers or when people unwittingly delete machines. 

The 'Delete Special' option comes with the SCCM product, and here is how Christjan Schumann explained in the forum post with steps to remove the option from the SCCM console. 
  • Make a backup and then edit "SCCM\AdminUI\XmlStorage\ConsoleRoot\adminconsole.xml" file.
  • Search for lines containing: DisplayName="DeleteSpecialAction". You will find 3 matches. 
  • All of them are parameters of "ActionDescription" tag and 2 of those matches should be removed from XML (from start tag to end tag ).
  • To remove "Delete Special" option from context menu of root level collections: delete lines 7787 to 7807.
  • To remove "Delete Special" option from context menu of child level collections: delete lines 8529 to 8549.
The above line numbers are valid in original SP2 adminconsole.xml. They might differ if you've customized it before.


Ref. Original Post: http://social.technet.microsoft.com/Forums/systemcenter/en-US/6868c190-1aee-4690-8e23-6e019f8d7472/delete-special

http://social.technet.microsoft.com/Forums/systemcenter/en-US/4b467010-3c55-4fa3-9a61-127c9d846416/sccm-2007-delete-special?forum=configmgradminconsole

Custom SQL query to report MS-Excel and MS-Access

Here is a custom Report to list only MS-Excel (excel.exe) and MS-Access (msaccess.exe) with Office 2003, 2007 and 2010 versions. You can also use to query by editing it to fetch other components of Microsoft Office.

SELECT b.Netbios_Name0,
b.User_Name0,
CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
ELSE 'NULL'
END AS 'Office Version',
a.FileName,
a.FileVersion
FROM v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
WHERE (a.FileName = 'excel.exe' or a.FileName = 'msaccess.exe')
GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion
ORDER BY b.Netbios_Name0


Note that you may get repeated machine names if the machine has got multiple updates installed for the MS office versions. 


Friday, September 27, 2013

WQL Query to add bulk computers to SCCM collection

We use WQL queries to create dynamic SCCM Collections to list machines with a particular software, hardware, etc. But when it comes to adding a random machines from a given list and when we don’t use the SCCM Right-click tools, mostly we go the conventional manual method. Once during a conversation with a Microsoft tech, he helped me with this simple query which made most of my deployments easier. I can say this is faster and lighter than using Right-click tools. Sharing this query here.

We can add any number of Computer list to a Collection, but max 1500 hostnames per query. We can also remove any single machine from this customized collection.  

Select SMS_R_System.ResourceID,SMS_R_System.ResourceType,
SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client 
from SMS_R_System where SMS_R_System.name in
('Machine1','Machine2','Machine3')

Easy Steps to consolidate the computer list into the query with comma: 
* Add the machines to MS Excel by listing each machine on each row.
* Include a column (A) before machines list with the single code symbol  (').
* On the Columns C and D enter the comma symbol (,) and single code ('). 
* Drag the single code and comma symbols till the last before row of the machine list.
* Add a formula to Column E [=A:A&B:B&C:C&D:D].
* On Column E, Copy - Paste Special with Values.
* Copy the entire column E and paste in the query between brackets ( )
* It should look like  ('Machine1',

                               'Machine2',
                               'Machine3',
                               'Machine4')

* Click Ok and close the New collection window and we are done.