Dec 1, 2010

How get the rid of systems which has WMI issue(rebuild WMI Repository)

Had several systems which had wmi issue in doing client /application installation.This is most common issue which we face if something happens to the system.To get solved,use the script which can be run remotly using psexec tool.
Note : The below script tested only on Windows XP ,not tested on higher versions like Windows 7
Download the psexec tool from microsoft .Here are the basic instruction in doing it.
1.copy the psexec.exe tool on to new folder (G:\script) and create 3 new files(wmifix.bat,computers.txt and run.bat).Each file script has given below.


wmifix:
@echo off
REM  WMi Repair

Title WMI Repair
%windir%\system32\wbem\winmgmt /clearadap
%windir%\system32\wbem\winmgmt /kill
%windir%\system32\wbem\winmgmt /unregserver
%windir%\system32\wbem\winmgmt /reserver
%windir%\system32\wbem\winmgmt /resyncperf

net stop winmgmt /y
if exist %windir%\system32\wbem\repository.old rmdir /s /q %windir%\system32\wbem\repository.old
ren %windir%\system32\wbem\repository repository.old

regsvr32 /s %systemroot%\system32\scecli.dll
regsvr32 /s %systemroot%\system32\userenv.dll

for /f %%s in (‘dir /b /s %windir%\system32\wbem\*.dll’) do regsvr32 /s %%s
for /f %%s in (‘dir /b /s %windir%\system32\wbem\*.mof’) do mofcomp %%s
for /f %%s in (‘dir /b %windir%\system32\wbem\*.mfl’) do mofcomp %%s

net start winmgmt
%windir%\system32\wbem\wmiprvse /regserver

run.bat
@echo off
cd G:\script
G:
psexec @computers.txt -c G:\script\wmifix.cmd
computers.txt
Add list of computers to the txt file which you have trouble.

Monthly Patch statistics reports in SMS/SCCM to show up to the management in a simplified manner

Long back created a report for the monthly Patch statistics which can be found in http://www.windows-noob.com/forums/index.php?/topic/1764-patch-management-report-in-sms-2003/#entry6281
Below all the reports have been created using the last state messages.Even you can create different type of quiries based on this.



I was referring to the Patch process and found an image which gives the statistics for the listed patches in a good viewable way.so thought of creating such a report and can be linked to other report to get preferable colums which are necessary.May be i can show it to the management team for the patch activity on monthly basis.This basically requries to create 3 reports( like 1,2,3 ) out of which 3 is linked to 2 and 2 is linked 1.It is just simple that you can run only one report which is linked to other reports which gives u a report like below for the given bulletin ID’s.
Note:The below report is filterd with language swedish,if you want to get status for English/other language patches,you can customise it.The below report is called 1)Patch Management summary

To Build this report and to link to other reports ,you will have to create 3 reports which i named it like :1)Patch Management summary 2)Status of Each bulletin ID 3)Status of each bulletin ID with distribution status
Create new reports for each with the below query.
3)Status of particular bulletin ID with selected distribution status:
select distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, fcm.SiteCode, ws.LastHWScan,
DATEADD(ss,@__timezoneoffset,ps.LastStatusTime) as LastStatusTime,ps.LastStatusMessageIDName, ps.LastExecutionResult
from v_R_System sys
join v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
join v_GS_WORKSTATION_STATUS ws on sys.ResourceID=ws.ResourceID
join v_GS_PatchStatusEx ps on sys.ResourceID=ps.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
       ps.UpdateID=summ.UpdateID
where (
ps.LastStateName=@status and summ.ID=@Title) and
(summ.Type = ‘Microsoft Update’) and (summ.product NOT LIKE ‘Windows Server 2003′)
group by Netbios_Name0, user_Domain0,user_Name0,SiteCode,LastHWScan,LastStatusTime,LastStatusMessageIDName,LastExecutionResult
order by Netbios_Name0

Click on the “Prompts Button
Create a new prompt with the following Name: “status
Give it a prompt text for ex: Select the Status
 Provide the following sql statement to the prompt for status with the given syntax

  select distinct LastStateName from v_GS_PatchStatusEx
Create another prompt value for Title with the sytax query :
  Select Title,ID,Product from v_GS_PatchStatusEx
Create report 2 called Status of Each bulletin ID
declare @n float
select @n = count(distinct ps.ResourceID)
from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
       ps.UpdateID=summ.UpdateID
where (
ps.ID=@Title or ps.QNumbers=@Title or ps.Title=@Title) and
       (summ.Type =’Microsoft update’) and (summ.product NOT LIKE ‘Windows Server 2003′)

if IsNULL(@n,0) = 0 return
select @Title as Title, ps.LastStateName, count(distinct ps.ResourceID) as ‘Totals’,
 ROUND(100.0 * count(distinct ps.ResourceID)/@n,2) as ‘Percentage %’

from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on ps.UpdateID=summ.UpdateID
where (
ps.ID=@Title or ps.QNumbers=@Title or ps.Title=@Title)
       and (summ.Type = ‘Microsoft update’ ) and (summ.product NOT LIKE ‘Windows Server 2003′)
group by ps.LastStateName

Prompt for Title:   select Title,ID,QNumbers from v_GS_PatchStatusEx
Once you create the report,just right click on the report  and choose properties ,Choose the “Links” tab , Choose link Type: “link to another report“choose the report the one which you created above(report 3 in this case). make sure you have selected the correct columns which are marked in red circle
almost we come to an end by creating last report called  1) Patch Management summary
select summ.ID,summ.QNumbers as ‘Q Number’,
COUNT(distinct ps.ResourceID) as ‘Requested’,
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)  as ‘Installed’,
ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End) /count(distinct ps.ResourceID),2) as ‘Success %’
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on ps.UpdateID=summ.UpdateID
where (summ.ID=’MS10-006′ or summ.ID=’MS10-007′ or summ.ID=’MS10-008′ or summ.ID=’MS10-013′) and (summ.Type=’Microsoft Update’) and (summ.product NOT LIKE ‘Windows Server 2003′) and (summ.language=’Swedish’)
group by summ.ID,summ.QNumbers
order by summ.ID

If you want to get the information from particular collection,then you can limit the Above report on a specified collection ,here is the one to go.
select summ.ID,summ.QNumbers as ‘Q Number’,
COUNT(distinct ps.ResourceID) as ‘Requested’,
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)  as ‘Installed’,
 ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)
 /count(distinct ps.ResourceID),2) as ‘Success %’
 from v_GS_PatchStatusEx ps
JOIN v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx summ on
   ps.UpdateID=summ.UpdateID
    where (summ.QNumbers=’975562′ or summ.QNumbers=’978695′ or summ.QNumbers=’979482′ or summ.QNumbers=’980195′ or summ.QNumbers=’982381′) and
            (summ.Type=’Microsoft Update’) and (summ.product NOT LIKE ‘Windows Server 2003′) and(fcm.CollectionID
=@collID)
group by summ.ID,summ.QNumbers

order by summ.ID
You would need to create promot collId given below:
begin
 if (@__filterwildcard = ”)
  select CollectionID, Name from v_Collection order by Name
 else
  select CollectionID, Name from v_Collection
  WHERE CollectionID like @__filterwildcard
  order by Name
end
The above report will generate status for specific bulletin ID’s for swedish language in brief.If you want to generate report for other languages or you want to get patch status irrespective of Laguage,you can simply delete it.

Once you create this report,right click and select properties.Choose the “Links” tab,Choose link Type: “link to another report” ,choose the report that you have created above(report 2 inthis case).ensure you have the correct columns fields like below otherwise you will mislead the report.
 you have done now,reports are ready for you.

Report for Particular Bulletin ID ,click on MS10-007

click on failed status,which gives you all machines

Hope it helps you insome way.The same reports are still work in SCCM in similar way but before doing it SCCM,change the bulletin ID numbers and language(in mycase it is Swedish)
Note: when you copy and paste the quiries to your SMS/SCCM server ,you might see some errors because of copy and paste.All these quiries are present in notepad attached here  Status report quiries
All the reports are working well in SCCM environment but you will have to remove a part of syntax called “and (summ.product NOT LIKE ‘Windows Server 2003′)” from the reports which you use since in SCCM,the product value is NULL.If you use the above quiries without modifying,you may see blank report.

Nov 30, 2010

Show All “Succeeded” Advertisement Counts for the Past 30 Days

We rely on Run Advertised Programs (RAP) quite heavily at Dell – we have approximately 400 applications available in RAP, available to almost all systems.  I was asked to provide a quick report that shows the number of successful installs for each Advertisement over the past 30 days, so I thought I would share with you!

SELECT     COUNT(*) AS Count, v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName
FROM         v_ClientAdvertisementStatus LEFT OUTER JOIN
                      v_Advertisement ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID
WHERE     (v_ClientAdvertisementStatus.LastStateName = 'succeeded') AND (v_ClientAdvertisementStatus.LastStatusTime > DATEADD(day, - 30, GETDATE()))
GROUP BY v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName
ORDER BY v_Advertisement.AdvertisementName

Enjoy!

SCOM R2 Gateway Server not communicating with the SCOM Management Group: EventID 20070 on the GW server and EventID 20000 on the RMS

Normally when a SCOM Gateway is installed and all prereqs are met, things run like clock work. In the years that I work with SCOM I have installed many SCOM GWs, all without any real issues what so ever. And when something was amiss, it turned out to be something simple like a firewall blocking some traffic or an incorrect certificate or a missing certificate chain. With just a few mouse clicks, all was fine and life was good again.


Until last week that is. I bumped into a GW that wouldn’t work. AT ALL! I could reproduce it as well with another GW, installed in total different environment. Strangest thing was that another SCOM R2 GW server was already installed and fully functional. So what was happening? And more over, how to solve it?
The Situation:
The SCOM R2 GW is installed and everything is in place (certs, SCOM GW Approval Tool has been run, firewalls have been configured and the lot). So there is a connection from the GW to the MG.
However, the GW throws EventID 20070 with the message ‘…Check the event log on the server for the presence of 20000 events, indicating that the agents which are not approved are attempting to connect ’:
image
On the RMS side of things, EventID 20000 is shown, telling that the SCOM R2 GW tries to connect but isn’t recognized as part of this Management Group (A device which is not part of this management group has attempted to access this Health Service. Requesting Device Name : …):
image
Things we tried: Wow! We did many things in order to get it all up & running:
  1. Of course, we checked the firewalls, routers and switches;
  2. Even installed Network Monitor on the RMS;
  3. Renewed the certs on the GW side of it all, reinstalled the SCOM GW;
  4. Reran the GW Approval Tool many times;
  5. Flushed the Health Service State on the RMS and the MS which the GW should report to in order to get a fresh config file (~:\Program Files\System Center Operations Manager 2007\Health Service State\Connector Configuration Cache\\OpsMgrConnector.Config.xml);
  6. Installed the SCOM GW on total new server;
  7. Renamed the SCOM GW to see whether the computer name was causing it all;
  8. Ran some verbose logging on the RMS, MS and GWs which only showed EventID 20000 happening and nothing more;
  9. Deleted the SCOM GW and its SITE entry from the SCOM DB, waited until they were groomed out and started all over totally CLEAN;
  10. Ran some good tracing on the firewalls involved as well, showing us the connection was closed by the RMS (EventID 20000).
All to no avail. Nothing solid came out of it.
So I installed a new SCOM GW in total different Forest. And experienced the same issue! And all that time, the GW server which was installed some weeks ago was running just fine.
Dive Dive!: So it was time for a deep deep dive. We copied the file OpsMgrConnector.Config.xml of the RMS and MS to another location and started to take a deep dive into them. Soon we noticed a difference: the file from the RMS contained the Connector information for the fully functional GW server, while the MS didn’t.
That’s strange! Since that GW server was installed by me using the GW Approval Tool, telling SCOM that the GW server should report to the MS and not the RMS. So this entrance should be found in the file located on the MS, not the RMS! I checked my installation document for that particular environment and indeed, I referred to the MS, not the RMS….
Time to run a PS-cmdlet which shows to WHAT MS the GW server is primarily talking to: Get-GatewayManagementServer | where {$_.Name -like '< GW SERVER NAME>'} | Get-PrimaryManagementServer.
And the output really puzzled me: the functional GW Server wasn’t talking to the MS but the RMS. Also the people running the firewall (TMG) told me that ONLY the RMS was being published, not the MS!
Now it all hit home! Wow!
The Solution: I stopped the Health Service on the problematic test GW server, removed the GW server from the SCOM R2 Console, reran the GW Approval Tool, this time I referred to the RMS as the Management Server, adjusted the registry on the GW server in order to reflect the RMS and not the MS and restarted the Health Service on the GW.
BINGO!
All was working now!
Did the same for the problematic production GW server and hit the jackpot there as well!
However, some additional work needs to be done but that will be planned for the days to come:
  1. Publish the MS instead of the RMS on the TMG;
  2. Reconfigure the GWs to talk to the MS and not the RMS (some simple PS-cmdlets will do the trick here);
  3. Adjust the registry entries on the GWs in order to reflect the changes.
Why? It is not good to have servers reporting to the RMS.
Puzzled: Yes, I am still puzzled. WHY does the first functional GW server talk to the RMS instead of the MS, while I have ran the GW Approval Tool in such a manner that it should talk to the MS? Got the screen dumps showing it. Really felt stupid and taken by surprise. Also learned a valuable lesson: How to troubleshoot SCOM R2…