SCCM Client Approved N/A - Best SQL Command that will Auto Approve all Clients

This is a good day! I have managed to fix a thing that has been a big problem at some SCCM 2007 customers. Clients are getting Approved N/A status even if everything looks ok and should work.

I have seen big complicated scripts that will change the status but today I found the most powerful and simple way to fix it.

First, go to your SCCM site where the clients are and create a new SQL Command.

Name:
ApproveAllClients

SQL command:
UPDATE v_CM_RES_COLL_SMS00001
SET IsApproved=1
FROM v_CM_RES_COLL_SMS00001
INNER JOIN v_R_System
ON v_CM_RES_COLL_SMS00001.ResourceID = v_R_System.ResourceID
WHERE v_CM_RES_COLL_SMS00001.IsApproved<>1

Log status to:
\c$\temp\ApproveAllClients.log">\\<siteserver>\c$\temp\ApproveAllClients.log

I found this solution at MyItforum so thank you sooo much!! Link:
http://myitforum.com/myitforumwp/2011/11/10/configmgr-auto-approve-clients-the-easy-way/

SCCM Client Approved N/A - Best SQL Command that will Auto Approve all Clients

This is a good day! I have managed to fix a thing that has been a big problem at some SCCM 2007 customers. Clients are getting Approved N/A status even if everything looks ok and should work.

I have seen big complicated scripts that will change the status but today I found the most powerful and simple way to fix it.

First, go to your SCCM site where the clients are and create a new SQL Command.

Name:
ApproveAllClients

SQL command:
UPDATE v_CM_RES_COLL_SMS00001
SET IsApproved=1
FROM v_CM_RES_COLL_SMS00001
INNER JOIN v_R_System
ON v_CM_RES_COLL_SMS00001.ResourceID = v_R_System.ResourceID
WHERE v_CM_RES_COLL_SMS00001.IsApproved<>1

Log status to:
\\<siteserver>\c$\temp\ApproveAllClients.log

I found this solution at MyItforum so thank you sooo much!! Link:
http://myitforum.com/myitforumwp/2011/11/10/configmgr-auto-approve-clients-the-easy-way/

Check CCMEXEC service State / Startmode with WMIC script

Sometimes you want to see which servers are running the SMS Agent Service and which ones has the service disabled.

Create a CMD file with this line (one line):

wmic /node:@CCMEXEC.txt /output:CCMEXEC.csv Service where name="CcmExec" Get Name,State,Startmode /format:csv

Put the hostnames into CCMEXEC.txt, one hosname on each line

Run the CMD as Admin and the output will be saved into a nice CCMEXEC.CSV

Install KB2783466 with SCCM to fix red cross in SUP packages

Last month we got problems! A lot of patches were missing from our packages and the solution was then to just re-download them all and update the distribution points.

Now Microsoft has released a patch for this "KB2783466" and I just deployed it using SCCM.

Create a normal software package and add the file and this commandline:

SCCM2007-SP2-KB2783466-ENU.msi /passive /norestart

Advertise it to some pilot servers and install it manually, since the switches are /passive you will see the installation but it's still automatic which gives you some control.

Check "add/remove programs" to see that it's installed ok.

Put in a mandatory installation time to the advertisement and rock on with the rest of your SCCM servers.

Use Wmic to Get Hotfix installation dates from Windows 7 and 2008 servers

The default setting in Def_Mof does not collect hotfix installation dates from Windows 2008 servers so you can't use SCCM to query add/remove programs table to get a installation date when SUP is giving strange dates.

So I searched and modified a powerful wmic command for this:

wmic /node:@serverlist.txt /output:c:\temp\qfe_output.csv QFE where hotfixid="KB2655992" GET CSName,HotFixID,InstalledON /format:csv

1. Put all servers into the "serverlist.txt" file.

2. Run the command as a user who gets local admin rights to all systems in the serverlist.txt.

3. Outpul is created as CSV but you can change the formal to HTML by changing to: "/format:html"

Worked very well for me!

Who or what restarted the SCCM system

I get this question quite often and in big SCCM environments it can be a lot of things that restarts a system but this is how I troubleshoot:

1. First I check the "RebootCoordinator.log" and try to find if it's a package or user that initiated the restart.

2. If it was a package and the reboot was in a bad time check it MW is set in any of the collections where the system is shown.

3. Next option is that a user has done the restart and this is a little more tricky but impossible or hard, no! :)

Check the "smscliui.log" and look for the SID for the user who accepted a reboot after Software Updates for example.

Download the PsGetSid tool from here (link to Microsoft).

Run the command with the sid as parameter and you wil get the username who restarted the system in cleartext.

Create Dynamic SCCM 2007 Collection with all N/A and Not Approved Clients

Even if you have the "Approve all" site setting in SCCM 2007 there are still some clients that are "N/A" or "Not Approved" by different reasons.

Step1: At my customer I created a dynamic collection that pulled all resources from ALL SYSTEMS collection that had N/A or Not Approved status..

Create a new collction and built a query based rule

Paste these lines into the "Query statement:" field and save/close it.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId where SMS_CM_RES_COLL_SMS00001.IsApproved not like '1'

Now you have a dynamic SCCM collection with all systems that has some issue with boundaries or just needs to be manually approved. Select all systems in the collection, right click and approve them.

Step2 is to automatically approve all these systems with a script but I am posting that later..

Query to Find Which Netbios Name uses the GUID

Often when you troubleshoot SCCM status message errors you get the non-readable GUID on the system reporting the error.

I created this simple console query that shows which netbios name that uses the GUID look for (prompted value):

select distinct SMS_G_System_SYSTEM.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.SMSUUIDChangeDate from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.SMSUniqueIdentifier = ##PRM:SMS_R_System.SMSUniqueIdentifier##

Feel free to use it! Create a new query, select "Show Query Language" and paste in the query in bold above!

I use this query every day to find the bad guys :D

Hardware Failure on Server holding Management Point - Here is an Easy Fix!

One Customer had a hardware failure on a really old SMS2003 server holding Management Point (MP) and Distribution Point (DP) roles.

Since the HW were really old and soon to be terminated I installed MP and DP roles on Primary Server and asked network team to change the DNS record for the broken server to point out the Primary Server.

Even if clients/agents should go to AD and check for the MP role I have seen that many clients need to be restarted before they do and above goes much faster than wait to all 10'000 clients to get restarted.

Just a small tip :)

Virtual Central Site Server CPU/RAM/Disc Load During Big Client migration

I want to show you some performance status from a real world migration when 5000 new SCCM clients were added to a hierarchy where all SCCM site servers are running on Virtual VWware platform.

All Primary Child Site Servers has 4 CPUs and 8 GB RAM and the Central site Servers where all the pictures below come from has 12 GB RAM.

SQL 2008 is only allowed to use 2 CPU's (read more).

Memory usage duing initial first discovery and HW/SW Inventory.

CPU usage duing initial first discovery and HW/SW Inventory.

And last picture shows Disc usage duing initial first discovery and HW/SW Inventory.

Virtual servers are doing a good job and this same Central site servers has now >40'000 active clients and is still in good shape.

Please use the below comment link if you want to ask something?

SCCM client installation fails - Error 1603

  I am right now doing a big server migration from SMS2003 to SCCM2007 and in some countries the installation fails with an general error code 1603.

To solve this I added this one line to my client installation cmd file right before ccmsetup.exe command line:

If exist %windir%\system32\atl.dll %windir%\system32\regsvr32.exe %windir%\system32\atl.dll /S

Enable 2 CPU's for SQL2008 on SCCM Central site

As default all available CPU's are managed by SQL and on one of my customers Virtual Windows 2008 R2 x64 server (Central site with 40000 clients) i have seen that CPU0 is running very high 80-90% and CPU1 around 50% and the last two barely at all.

Default setting as above!

After my change in SQL2008 Server Management Studio.

This was the dramatic change. CPU0 was down to normal and could execute more SCCM processes and CPU3 and 4 was forced to run SQL2008R2.

Despool.log, MP has rejected a policy request

On some SCCM sites I have seen thousands of errors in despool.log about rejected policy. In some cases these are caused by an non-approved client but on one customer site all clients were approved and there were still 3000-3500 errors in the status every morning.

All the errors came from one single GUID but in the log there was nothing about which Netbios name this client had so the solution was this query:

SELECT
   resourceID, netbios_name0, SMS_Unique_Identifier0
FROM
   dbo.v_R_System

Select all the results and paste them into Excel or something where you can make a search for the GUID you saw in the log.

I reinstalled the SCCM client on the error-burping server and the log was fine the day after!

Latest SCCM Client version is 4.00.6487.2157

The latest version of SCCM Client is right now 4.00.6487.2157 and to get there you need to grab the client installation files from a SCCM 2007 SP2 site and then add the R3 hotfix (KB977384).

I am right now pilot testing a huge enterprise deployment of KB977384 and it seems to go smooth and does not reboot the system during the patch/upgrade.

Technorati-taggar: Sccm,2007,sp2,r3,client,version,highest,latest,newest,what

Hardware Inventory fails after client is migrated from SMS to SCCM

When a client computer is upgraded from SMS 2003 to Configuration Manager 2007 the hardware inventory scan might fail to run on the upgraded client computer. Entries similar to the following appear in InventoryAgent.log on the client computer:

Unknown error encountered processing an instance of class CCM_System: 80041013
Collection: Failed to process mandatory data item for class: CCM_System. Cycle abandoned !!   InventoryAgent
Failed to process instances of CCM_System: 80004005

Solution: Restart the client computer after migration!

Technorati-taggar: Windows,2000,migration,no,hw,inventory,sms2003,sccm2007

Export SMS/SCCM data to CSV or TXT by using SQLCMD

I often do automation work and one nice tool when we talk about SMS or SCCM data is SQLCMD.exe.

So how do I use it?

1. First I build a SQL query that gives me the data I want. I user query editor to find the right fields and then modify with functions etc.

2. I then normally store the query as a SQL view to make it more easy to call for.

3. Then I create a CMD file with SQLCMD.EXE command that calls for the view I created in step 2. SQLCMD has a lot of nice switches to format the data. See above!

4. When I get out the data in the correct format and into the right place (like to a file share where some non sms/sccm team can reach it) I create a scheduled task to run the CMD file with a user that both has SQL access and write access to the output folder.

Questions? Leace a comment!

Technorati-taggar: SMS,SCCM,Automation,Export,Schedule,Automate,Data,SQL,View,txt,csv,Separator,SQLCMD,Tool,Guide,Help,Scheduled task,user,access

Deploy Windows 7/2008R2 SP1 with SCCM

There are at least two different ways to deploy Service Pack 1 for Windows 2008 R2 and Win7 by using SCCM.

[Scenario 1]
Use SCCM SUP (Software Update Point) to deploy SP1 as an normal update.

Fast and Easy way but I prefer to have better control over Service Pack installations than normal Hotfixes/Monthly updates so I do not use this method my self.

[Scenario 2]
Build a normal software package with the just the Service Pack Binary and install it using quiet and unattend switches. Let the SP binary do the reboot and make sure that the program can run even if no user is logged on. Also make sure that the installation is allowed to run 180 minutes – it takes at least ONE hour to install Service Pack 1 on a fast server!

Then build a query based collection with all the systems in scope for the Service Pack and advertise the package without any mandatory time against this collection. I prefer to use Download and Execute on Service Pack Advertisements even if this uses the double amount of space.

Then pick some pilot systems in the collection and trigger the advertisement manually to see if everything works. The unattended switch shows you progress on the system console screen so be sure to connect as first user and to Console-0.

After this you can plan and schdule a controlled mass deployment of Service Pack 1 to all your servers/workstations in scope!

If you have any questions, please write a comment below!

Technorati-taggar: SP1,Windows,2008R2,Win7,Deploy,Service Pack,How,To,Guide,Help,Ways,Scenario,Rollout,Install,Deployment,SUP

New Mobile Template and Anonymous Comments activated in Blogger

I checked the blog settings and saw some things I wanted to change.

From now on this blog is looking better when read from a smartphone as Iphone, Samsung or HTC. Please test it and comment?

The next this was the blog comment feature that is now allowing also anonymous writers and I also disabled the secret word check so now you are free to give me feedback as much as you want!

Best Regards from your SlowMovingSoftware Admin!

Bug! MP installation fails even if WebDav=Ok

I have been installing a lot of new SCCM 2007 sites lately and used my step by step guide. In my guide I configure Webdav settings needed by SCCM before starting Configuration Manager installation.

When SCCM tries to install Management Point (MP) it fails and in the setup log it says that the WebDav settings are wrong even if they are set correctly?!

Solution is to re-set all the four needed values, apply settings and restart IIS services. Next time MP installation will retry it will success..

Technorati-taggar: WebDav,SCCM,IIS,7.5,Bug,Error,Configuration,Manager

Corrupt GPO causes SCCM patch management to fail, solution here!

Some servers are having GPO problems and these problems makes SCCM unable to update the WindowsUpdate registry values so I made this Registry file and imported it to all systems having GPO issues.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate]
"WUServer"="http://yyour_sccm_sup_server.xxx.xxx.xxx:80"
"WUStatusServer"="http://your_sccm_sup_server.xxx.xxx.xxx:80"

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU]
"UseWUServer"=dword:00000001

When the file is imported SCCM detects that there are GPO values and then makes his updates making SCCM patch management to start working.

Technorati-taggar: SCCM,Patch,Management,WSUS,SUP,GPO,WUAhandler