MPControl.log detected MP is not responding to HTTP Requests. Error code 12030

The day to day work was going on and as per the schedule i configured the patching activity for the month of November 2014 and went for the tea break. When I came back and though to check the status for my deployment I was shock to see that sccm reporting was not working. I was not able to digest this truth because in next 2 hours my patching was about to start and I was totally depended on the sccm reporting to check the status for the deployment.

When I starting digging the logs I found that MPcontroller.log was throwing error “Http test request failed with error code 12030” and will missing my code 200 L .

So my next step was to see what went wrong and I started checking IIS log and found that my IIS was “Refused the connections request” that was more confusing because I was not having any error to do googling.

Now I was totally dependent on Microsoft detective known as “Event Viewer” and that as expected it gave me the hint that 3GB switch is enabled in the Boot.ini file on the server at the same time when my sccm MP went down. I was quiet lucky that was having knowledge about this as I have read article on same in the past.

This issue occurs if less than 20 megabytes (MB) of nonpaged pool memory is available on the server. When less than 20 megabytes (MB) of nonpaged pool memory is available, the Http.sys kernel mode driver stops accepting new connections.

This issue may occur in situations in which the /3GB switch is enabled in the Boot.ini file on the server

Solution to Fix this is given below :

To work around this issue, add the EnableAggressiveMemoryUsage registry entry to the following registry subkey:.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP\Parameters

Then, set the EnableAggressiveMemoryUsage registry entry to 1. 

NOTE: Please take back up of the registry to be on the safe side

To do this, follow these steps:

1.       Click Start, click Run, type regedit in the Open box, and then click OK.

2.       Click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP\Parameters

3.       On the Edit menu, point to New, and then click DWORD Value.

4.       Type EnableAggressiveMemoryUsage, and then press ENTER.

5.       On the Edit menu, click Modify.

6.       In the Value data box, type 1, and then click OK.

7.       On the File menu, click Exit to exit Registry Editor. 

8.       Restart the HTTP service. To do this, follow these steps:

a.       Click Start, click Run, type cmd in the Open box, and then click OK.

b.       At the command prompt, type net stop http /y, and then press ENTER.

c.        At the command prompt, type iisreset /restart, and then press ENTER 

And my sccm was back and i completed my activity successfully.

Hope This Was Helpful!!!

Amarpal Singh Sandhu

Query to list all the systems that never ran an advertisement successfully

Here is a query that can be used in SCCM report to list all the systems that never an advertisement successfully.


select distinct count(v_ClientAdvertisementStatus.resourceid) as [Number of Adverts],sys.Active0,sys.Client0,sys.Client_Version0,sys.Netbios_Name0,
sys.Operating_System_Name_and0,sys.Obsolete0,
sys.Resource_Domain_OR_Workgr0,sys.User_Domain0,
sys.User_Name0  from v_R_System sys right join v_ClientAdvertisementStatus on sys.resourceid=v_ClientAdvertisementStatus.resourceid where sys.ResourceID not in (select  distinct ResourceID from v_ClientAdvertisementStatus where v_ClientAdvertisementStatus.LastStateName = 'Succeeded') group by sys.Active0,sys.Client0,sys.Client_Version0,
sys.Netbios_Name0,sys.Operating_System_Name_and0,
sys.Obsolete0,sys.Resource_Domain_OR_Workgr0,
sys.User_Domain0,sys.User_Name0

Query to list the hardware and software inventory dates of all the SCCM clients

SELECT DISTINCT SYS.Netbios_Name0, SYS.Operating_System_Name_and0,
  HWSCAN.LastHWScan, SWSCAN.LastScanDate, SWSCAN.LastCollectedFileScanDate 
FROM v_R_System SYS
LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID

WQL Collection queries to list Vista, Windows 7, Windows 2008 and Windows 2008 R2 systems

All Windows Vista Systems
Select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.0%"

All Windows 7 systems

select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%"

All Windows 2008 Systems

Select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server 6.0%"

All Windows 2008 R2 Systems

select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server 6.1%"

Create a collection to list all the systems with a particular KB

Use the following queries to build the collection to list all the systems with a particular KB.

For finding x86 systems:

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_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB123456%"

For finding x64 systems:

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_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB123456%" 

 

Replace KB123456 with the KB number of require security update.

SCCM SQL Query to list all the systems with their BIOS details

SELECT sys.Name0, BIOS.SMBIOSBIOSVersion0,BIOS.ReleaseDate0, BIOS.Manufacturer0
FROM v_r_system sys, v_GS_PC_BIOS BIOS
WHERE sys.ResourceID = BIOS.ResourceID

SCCM Report for 2 softwares registered in Add/Remove Programs

This report creates the output in the following format.

Computer 1	Software 1	Software 2
Computer 2	Software 1	Software 2
Computer 3	Software 1	Software 2
Computer 4	Software 1
Computer 5	Software 1	Software 2
Computer 6	Software 1	Software 2
Computer 7		Software 2
Computer 8	Software 1	Software 2

Query:

select sys.name0,(select distinct 'Software ARP1' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP1') ),(select distinct 'Software ARP2' from v_Add_Remove_Programs ARP where (ARP.ResourceID=SYS.ResourceID) and exists (select ARP.DisplayName0 where ARP.DisplayName0='Software ARP2') )  from v_r_system sys inner join  v_FullCollectionMembership on sys.ResourceID=v_FullCollectionMembership.ResourceID where  v_FullCollectionMembership.CollectionID=@CollID

Prompt Query: Create a prompt with the variable CollID using the following query.

select CollectionID,Name from v_Collection

Here, replace Software ARP1 and Software ARP2 with your application add/remove programs name.

SCCM SQL Query to list IP Subnets of all the system that are in a collection

select distinct v_r_system.Name0, v_RA_System_IPSubnets.IP_Subnets0 from v_R_System inner join v_FullCollectionMembership on v_FullCollectionMembership.resourceid= v_r_system.resourceID inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID=V_R_System.ResourceID where v_FullCollectionMembership.CollectionID='SMS00001'

SCCM - SQL Report to show Maintance Windows for a Collection of system

Select fcm.Name,fcm.CollectionID as [Member of Collection], sw.Name as 'CEPSWName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsGMT as 'StartTimeIsGMT', sw.IsEnabled as 'CEPSWEnabled',case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'

from v_ServiceWindow sw

join v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID

where fcm.name in (select  v_FullCollectionMembership.name from  v_FullCollectionMembership where  v_FullCollectionMembership.collectionid='SMS00001') and isnull(fcm.IsObsolete, 0)= 0

WQL Query for show the list of systems with two applications installed.

Query to list all the systems with Program A and Program B, use the following query.

Replace Program A and Program B with your application add remove programs display name.

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.ResourceId in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like '%Program A%') and SMS_R_System.ResourceId in (select  SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like '%Program B%')

SMS/SCCM SQL Query Netbios Name, Model , CPU name, Memory (Ram) and Sitecode

SELECT V_R_SYSTEM.Name0,v_RA_System_SMSInstalledSites.SMS_Installed_Sites0,v_GS_PROCESSOR.Name0,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_COMPUTER_SYSTEM.Name0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 from v_r_system inner join v_GS_PROCESSOR on v_R_system.ResourceID=v_GS_PROCESSOR.ResourceID inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_r_system.ResourceID inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID=v_r_system.ResourceID inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID=v_r_system.ResourceID

SMS/ConfigMgr SQL query to list the systems in specific IP subnets

SELECT SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0, AIP.IP_Subnets0, SYS.Client_Version0
FROM v_R_System  as SYS
JOIN v_RA_System_IPSubnets as AIP on SYS.ResourceID=AIP.ResourceID
WHERE AIP.IP_Subnets0 in ('192.168.0.0','192.168.1.0')
Order by SYS.Netbios_Name0

SMS/ConfigMgr SQL query to find the list of systems in a specific IP subnet

SELECT SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0, AIP.IP_Subnets0, SYS.Client_Version0

FROM v_R_System  as SYS

JOIN v_RA_System_IPSubnets as AIP on SYS.ResourceID=AIP.ResourceID

WHERE AIP.IP_Subnets0 = '192.168.10.0'

Order by SYS.Netbios_Name0

ConfigMgr SQL query to list hardware and add/remove programs inventory for a client

SELECT V_R_SYSTEM.Name0,v_RA_System_SMSInstalledSites.SMS_Installed_Sites0,v_GS_PROCESSOR.Name0,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_COMPUTER_SYSTEM.Name0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_PC_BIOS.SerialNumber0 from v_r_system inner join v_GS_PROCESSOR on v_R_system.ResourceID=v_GS_PROCESSOR.ResourceID inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_r_system.ResourceID inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID=v_r_system.ResourceID inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID=v_r_system.ResourceID inner join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID=V_r_system.ResourceID where v_r_system.Name0 like '@computername'

Select v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_Add_Remove_Programs.Version0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID

WHERE v_R_System.Netbios_Name0 = '@computername'

 

 

NOTE = Change the @computername' with your machine name for which you want data to be genrated.

ConfigMgr Report to show available Hard Drive space on machines with its client version

select

v_R_System.Name0, client_version0,v_GS_LOGICAL_DISK.Name0 as Drive,

v_GS_LOGICAL_DISK.Size0 as Size,

v_GS_LOGICAL_DISk.FreeSpace0 as FreeSpace, size0 as Used

from

v_R_System inner join v_GS_LOGICAL_DISK on v_R_System

.ResourceID = v_GS_LOGICAL_DISK.ResourceID inner join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID=v_R_System

.ResourceID

where

v_GS_LOGICAL_DISK.DriveType0 = 3 and v_FullCollectionMembership.CollectionID = 'SMS00001'

order

by FreeSpace

How to create an ConfigMgr/SCCM reports with prompts?

This is something simple to do and adds a lot more functionality SCCM report. I will walk you through the basics of creating a prompt for a SCCM report.

1) Go to Reporting hive and select Reports
2) Right Click and Reports and select New -> Report

3) Give your report a Name, Category and then click EDIT SQL Statement

4) Paste in your code for your SQL Statement.

 

5) Click on Prompts… button

A new window will appear. Clicking the Add button will bring up the Prompt Properties page. Complete the Name (used as @Name in your report SQL code), Prompt Text (the hint the user will see for the prompt), Default Value and if needs be click the Provide SQL Statement checkbox and provide the appropriate query. If a SQL statement is provided here, then when executing this report the user can click on "Values..." (see the last screenshot) and select a row from the output of this query. SCCM select the value of the first column of the row selcted by user and assignes that value to the variable defined in the Name field.   Your window should look something like this.

Click OK to close the window and repeat step 5 as many times as you need.

You have now successfully added prompts (variable(s)) to your SCCM report.

The following SQL query should list all the systems that are in a selected collection with IE7 installed. While this query is being executed the @Prompt variable will be replaced by the value that the end user provides or select by clicking "Values.." button.

select V_R_SYSTEM.Name0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.InstallDate0 from v_R_System left join v_Add_Remove_Programs on v_Add_Remove_Programs.ResourceID=V_R_SYSTEM.ResourceID left join v_ClientCollectionMembers on v_ClientCollectionMembers.ResourceID=V_R_SYSTEM.ResourceID where v_ClientCollectionMembers.CollectionID= @Prompt and v_Add_Remove_Programs.DisplayName0='Windows Internet Explorer 7'