SQL Queries for SCCM Admins

Creating SQL queries for pulling out data from the SCCM Database is the integral part of day to day job of a SCCM Admin. Most of the time the requirements are pretty much straight forward and you can simply join few tables using the common columns and you are done, however not every query creation task end up in day. Sometimes the ask seems to be easy but the query creation needs little extra efforts to pull the desired data in the desired format.

I will use this post to publish the simple to complex SQL queries that I created while working on different requirements from my different customers.

SQL Query for Software Usage

This query was created as a result of some unique requirement. Requirement was only to see the most recent software usage record for a metered software on a system for a unique combination of user and computer.

There are some standard queries that could fit the bill but they provide multiple entries for the same user and computer combination.

Example:

SELECT

S.name0 'Computer Name',

MEU.UserName 'Logged-in User',

MEU.MeteredUserID,

FN.full_user_name0 'User Name',

SF.FileName 'Metered File Name',

SF.FileVersion,

MAX(MUS.LastUsage) as 'Last Usage Date',

DATEDIFF(day,MAX(MUS.LastUsage),Current_Timestamp) 'Days since last used'

FROM v_MeteredUser MEU Inner Join v_MonthlyUsageSummary MUS ON MEU.MeteredUserID = MUS.MeteredUserID

INNER JOIN v_GS_SoftwareFile SF ON MUS.FileID = SF.FileID

INNER JOIN v_r_system S on meu.username=S.user_name0

INNER JOIN v_r_user FN on meu.username=FN.user_name0

where sf.filename in ('Photoshop.exe')

and sf.FileVersion like '13%'

and FN.Full_User_Name0 like '%Gabriel%'

group by MUS.LastUsage, S.name0, MEU.UserName, FN.full_user_name0, SF.FileName, SF.FileVersion,  SF.FileID, MEU.MeteredUserID

HAVING (DATEDIFF(day, MAX(MUS.LastUsage), Current_TimeStamp) <60)

ORDER BY MUS.LastUsage

Output of the above query shows the multiple records for the same user and same computer combination for a single metered file.

The query that could meet the requirements is given below:

select Distinct V_R_System.Name0 'Computer Name',v_monthly usagesummary.ResourceID, v_MeteredUser.FullName,
V_MeteredFiles.FileName, v_MonthlyUsageSummary.UsageCount, v_MonthlyUsageSummary.UsageTime, v_MonthlyUsageSummary.LastUsage,v_MonthlyUsageSummary.MeteredUserID
from v_MonthlyUsageSummary, V_R_System, V_MeteredFiles, V_MeteredUser,
(select MUS.MeteredUserID, MAX (MUS.LastUsage) 'Last Usage'
from v_MonthlyUsageSummary MUS Group by MUS.MeteredUserID) M1
where v_MonthlyUsageSummary.MeteredUserID=M1.MeteredUserID and v_MonthlyUsageSummary.LastUsage=M1.[Last Usage]
and V_R_System.ResourceID=v_MonthlyUsageSummary.ResourceID
and V_MeteredFiles.MeteredFileID=v_MonthlyUsageSummary.FileID
and v_MonthlyUsageSummary.MeteredUserID=v_MeteredUser.MeteredUserID
and v_MeteredFiles.FileName = 'WINWORD.EXE'

Order by v_MonthlyUsageSummary.MeteredUserID, v_MeteredUser.FullName

Output of the above query look like this:

Second query gives the unique results (only the latest usage instance of the metered software) for the same user on different systems.

Hope the above query can help someone who might fall into the same situation.

Happy Querying !! ;)

PowerShell in Daily life

 Hello All,

This post will cover the small scripts that are used by the System Administrators/SCCM Admins/Desktop Administrator in their daily life. I am not a PowerShell expert and have just started learning it. This post is also a kind of repository/reference point for my work. 

Folder Existence and File copy script

Below script achieves the following:

• Check for the existence of a folder at the provided directory path.
• If the folder is found - required files are copied to it from the source location (in this example source location is the local folder where the script is running from). We can also add the -Path parameter and provide the actual source location of the file to be copied from.
• If the folder is not found, the script will create a folder and then copy the file to that folder.

Script: 

$Folder= 'C:\Users\Default\AppData\Local\Microsoft\Windows\WSUS'#Path of  'WSUS' folder - the folder that we are looking for.#

$Con= 'C:\Users\Default\AppData\Local\Microsoft\Windows' #Directory, in which the 'WSUS' folder will be created#

If (Test-Path -path $Folder) {

 copy-item "ABC.ini" -Destination $folder #ABC.ini is the example file - can be replaced by any file. -Path parameter can also be used if file not present in the local directory.#

} else {

New-Item -Path $Con -Name "WSUS" -ItemType "Directory"

copy-item "ABC.ini" -Destination $folder

}

    

         

Before script execution

After script execution

SCCM Client Agent Installation Error : 'Unable to connect to WMI on remote machine "Machine.mydomain.com", error = 0x800706ba.'

Issue:

Sometimes we encounter the below given error in the CCM.log, while installing the SCCM client agent using the Client Push method:

The error code 0x800706ba translates to 'RPC Server is unavailable'

Probing:

Generally, this situation should not arise if we have the following Inbound and Outbound Ports are open on the Windows Firewall:

Inbound Firewall Ports

Outbound Firewall Ports

I also tried to enable the ports using the predefined set of ports for required services but that also didn't work.

In my case, even when I had the above mentioned Inbound and Outbound ports already open on the firewall, I was still getting the error '---> Unable to connect to WMI on remote machine "Client1.Lab.mtayal.com", error = 0x800706ba'

To rule out any possibility I also tried to connect to the Client1's WMI remotely from the SCCM site server but still got the same error:

Solution:

To find the correct port that would make it work I manually enabled all the ports using the RPC Service on the client machine. And voila !! I could connect to the WMI on the client machine remotely.

I knew it's not recommended to leave all the RPC ports open on the client machine but also suspected that it could be more than one RPC ports that made it work.

To narrow down to the correct port/ports, I started disabling each RPC port and check the connection from the SCCM Server and finally concluded that it's the 'Remote Scheduled Task Management (RPC)' port which is making the difference.

As soon as the port was enabled a connection could be established and client installation could be completed. 

Hope it helps!!

Content Download Issue : 'CTM Job Suspended' -- 'Calling back with empty distribution point list'

Issue:

Today I stuck for a while during the Application Deployment to an user collection. Used all the default settings for the deployment. It almost worked until I saw an error in the contenttransfermanager.log:

Checked the locationservices.log and found the following entry in the log:
    calling back with empty distribution points list

Probing:

It was kind of surprise as many other deployment in past have worked without any issue.
While probing came across this small setting that could have been missed while creating the Deployment Type:

Deployment Option was left with the highlighted settings and that led to the above errors in contenttransfermanager.log and locationservices.log.

Solution:

Click on the drop drown option and select 'Download content from distribution point and run locally' :

Went back on the client and re-initiated the deployment and everything went well and the required application got installed without any further problems.

SQL Server Error while installing SCCM 2012/Current Branch Site

Issue: 

When we run the setup for the installation of SCCM 2012/Current Branch, Primary/Central Site we get the following error message in the Configmgrsetup.log

Cause:

This happens because the name of the Windows Server with the SQL Server instance installed was renamed before starting the installation of SCCM Site Installation.

Solution:

Open the SQL Management Studio on the SQL Server and login with the required credentials.

Run the following stored procedures to check the current name of the server in sys.servers of the SQL Servers.

Exec SP_Helpservers;

Go

If you find the old server name as a result of the above query then run the following procedures to first delete it from sys.servers and then update it with the current name.

Exec sp_dropserver <Old name>; (For default instance)

Go

Exec sp_addserver <New Name> ; (For default instance)

Go

                                           OR

Exec sp_dropserver <old name\instance name>; (for Named Instance)

Go

Exec sp_addserver <New Name\Instance name>;  (for Named Instance)

Go

After renaming the server name in sys.servers of the SQL servers run the following query to check if the name is changed or not

Select @@ServerName;

It will return the current name of the Server.

 Now restart the Instance of the SQL from the SQL Configuration Manager and reboot the server.

Once the server is up and running after the reboot run the  configmgr setup. The setup will first run to uninstall the Site components that  got installed in the last failed attempt.

After the Uninstall setup is finished run the configmgr setup once again to start the Site installation. This time the same error should not come up during the setup SQL Database step of the Site installation process. 

Error Code - 503 'Service Unavailable' - mpcontrol.log

Hello Everyone,

While working in my lab today I encountered one issue with the Management Point of a stand alone Primary Site. It's a fresh install of SCCM 2012 SP1 on Windows 2012 Server.

After installing the Management Point when I checked the mpcontrol.log I saw the below given error message in the log

Error: Http test request failed, status code is 503, 'Service Unavailable'. - mpcontrol.log

Tried to browse the Default Web Site but still got the same error message. It was sure that the problem is either with whole IIS installation or with some of its component.

Checked the Windows System Event logs and found a warning and  error related to the WAS (Windows Process Activation Service)

Warning:

Error:

Checked the WAS component of Web Server role and found that it's not installed. Installed the component  and restarted the Server but still could not get the Management Point working.

Did some research on Internet and found that it's an issue with the Application Pools. DefaultAppPool and SMS Management Point Pool were in Stopped state.

Started the Pools and checked the logs but still there is no improvement.

When checked back the pools found them in Stopped state again.

Explored Internet and found this useful Blog :http://mvolo.com/where-did-my-iis7-server-go-troubleshooting-503-quotservice-unavailablequot-errors/

The blog explains almost everything about the "Error 503 - Service Unavailable" in IIS.

Following the article looked into the Application Event Logs and found the below error events from IIS-W3SVC WP Source.

Searched for the modrqflt.dll (dll responsible for Request Filtering in IIS)  file but could not find it in the inetsrv folder

Then checked for the installed components through the Server Manager and found that the request filtering component of Web Server Role was not installed.

Installed the Request Filtering component, restarted the IIS and then the SMS Executive Service.

Finally MP completed a self check successfully.

Your opinions and views are more than Welcome. I will be very happy to have them added to the post !!

Thanks 

Manish Tayal