Eswar Koneti's Blog

Configuration Manager Queries SQL Server views in the Configmgr site database when retrieving the information displayed in reports .Configmgr database contains large information about computer,User related resource and other components as well.

If you are looking for some particular information while creating reports and if you are not sure which table the information has,you will stuck there else you will have guess the table/view to get what is desired.

SQL views contain the information you need for your reports to make reporting easier.

I cont find any document or related resources for SCCM 2012 SQL views .

Russ from Microsoft replied on the forums to get SQL views in SCCM 2012.

Here is the SQL query to get list of views with its columns Available in Configuration Manager 2012 Database.

Select distinct table_name,COLUMN_NAME From Information_Schema.columns

where table_name LIKE (‘v_%’)

Order by table_name

Download the excel sheet exported with Pivot Table from here

Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/en-us/download/details.aspx?id=22052

How to add link URL or shortcut file to users desktop profile ? Easiest way to accomplish this task is with GPO(Group policy Object).

If you want to accomplish this task using Configuration manager(SCCM),you can create simple VB script that does this job.

Option Explicit
dim path
path=CreateObject(“WScript.Shell”).ExpandEnvironmentStrings(“%UserProfile%\desktop“)
dim objFSO
set objFSO=CreateObject(“Scripting.FileSystemObject”)

If objFSO.FileExists(path & (“\eskonr.url“)) = False Then

objFSO.CopyFile “\\servername\folder name\eskonr.url“, path & “\”

end if

Full Details http://social.technet.microsoft.com/Forums/en-US/configmgrswdist/thread/ba1647a1-0385-48a9-9e83-1fc5daa00976/#e9d4f330-500e-45db-b3e1-5e3954f50ea4

This is Continuation to previous post package archival process.up on the query results,I would not delete the packages from console directly rather i delete them from its assigned Distribution points and move them to Archive folder and let be there for sometime until for sometime incase if it is required.

If you have large number of packages,you may find difficult in moving the packages by identifying where they reside (if you have subfolders).If you have less packages <10,you may use the report to know the root path of the package  and then right click on the package Move to archive folder by appending Retired to the existing package name to know package is retired.

If you have multiple packages,moving one by one is not easy thus you require something like script or tools.

Moving package using script may require to get the Source folder ContainerID number of  the packages(Folder of the package)  and Destination Folder ContainerID(Archival folder ID).

Here is the SQL query for the list of moving packages to know their Source Folder ContanerID.

From the package clean up report,if you get multiple packages,just place them in IN condition separated by comma (,).

I have packageID cen00004 which i need to move to archive folder with containerID=4 from SQL query below

select  B.ContainerNodeID,B.NAME,A.InstanceKey from dbo.FolderMembers a,.dbo.Folders b where
a.ContainerNodeID=b.ContainerNodeID and b.Name like ‘%archive%’

Replace the folder name if you have other one.

Now we have list of packages with their source ContainerNodeID(which is nothing  but the package FolderID).

Create a VB script with below syntax with input of all package IDs separated by commas with their source folder and destination folder.

Note : Please replace quotes (‘”) as they are replaced by fancy.

Option Explicit
‘On error resume next

Dim strSMSServer, strSMSSiteCode, strPackageIDs
Dim intSourceFolder, intDestFolder, intObjectType
Dim loc, objSMS
Dim objFolder
Dim arrPackageIDs
Dim retval

strSMSServer   = "SCCM server Name"
strSMSSiteCode = "SiteCode"
strPackageIDs  = inputbox("Please input packageIDs separated by commas","List of packages to be moved")

intSourceFolder =inputbox("Please input Source folder ContainerID number", "Source ContainerID")  ‘This ID is what you see in above SQL query to move all packages from the same folder test =1.
intDestFolder   =4 ‘Destination Folder for Archived packages :In this case,My archive package folder is 4.

intObjectType   = 2  ’2=Package for type of object .This scripts works only for standard software distribution packages.

Set loc         = CreateObject("WbemScripting.SWbemLocator")
Set objSMS      = loc.ConnectServer(strSMSServer, "root\SMS\site_" & strSMSSiteCode)

Set objFolder = objSMS.Get("SMS_ObjectContainerItem")
arrPackageIDs = Split(strPackageIDs, ",")

retval = objFolder.MoveMembers(arrPackageIDs, intSourceFolder , intDestFolder , intObjectType)
If Err.Number <> 0 Then
    msgbox Err.Description
End If

MSGBOX "Script is completed"

You may get multiple packages with different source Folders.So you will have to run the script to move bulk packages from each source folder to Destination at one go.

It is better than moving one by one package each time.

Hope it helps!

Happy to say that ‘Microsoft Community Contributor Award’ is renewed for 3rd time in a Row since 2011 for my contribution towards Microsoft Technical forum on Configuration Manager.

Dear Eswar,
Congratulations! We are pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor badge.
This recognition is reserved for participants who made notable contributions in Microsoft online communities such as MSDN, TechNet and Microsoft Answers. The value of these online resources is greatly enhanced by participants like you who voluntarily contribute your time and energy to improve the online community experience for others.

Once again, congratulations!
The Microsoft Community Contributor Team

Problem : Someone has Created package/Package’s (bulk) and distributed to sites/DP’s and these packages are in large size(more than 1GB).

You get notification from network Team or other teams about bandwidth issues.You may have to identify what packages are in process of sending to DPs and should also check how much % leftover to take further actions.(Stop sending the packages now and initiate it later sometime during night). You can also Use Senderanalyzer http://eskonr.com/2012/09/troubleshooting-sccm-configmgr-packages/ tool but sometimes packageID will be blank.

There are couple of logs and inbox folders to verify this.

Lets look at logs file first and then go with inbox folders to stop the sending files.

From sender.log :

when you create packages and send to child sites,package will be decompressed into .PCK and send which you can see from sender.log something like below :

Attempt to write 1048576 bytes to \\inhydcmS01.eskonr.com\SMS_SITE\2000OKA1.PCK at position 285944832

Wrote 1048576 bytes to \\inhydcms01.com\SMS_SITE\2000OKA1.PCK at position 285944832

Where KA1(2000OKA1.PCK) is sitecode from which the file is being generated and sending.

  2000O(2000OKA1.PCK) is the random generated number.

with the help of PCK file name,we can find what is the package ID from schedule.log.

Open Schedule.log on the server and search with PCK file name (2000OKA1) .You get something like below :

Send Request 2000OKA1 JobID: 00000018   DestSite: HY1   FinalSite:      State:  Pending   Status:            Action:    None   Total size:   3259244k   Remaining:   3259244k   Heartbeat: 21:41   Start: 12:00   Finish:   12:00   Retry:         SWD PkgID: ESK00006   SWD Pkg Version:   1

With this ,you can take necessary action whether you want to stop sending the package or continue it.

If you want to terminate /stop the files which are being transferred /Queue ,you can simply delete the job from schedule.box and its corresponding send request files ( this is not recommended way of doing it).

More about Understanding Site to Site Communication in SMS/SCCM ,you can refer steve rachui’s blog http://blogs.msdn.com/b/steverac/archive/2010/07/16/understanding-site-to-site-communication-in-sms-sccm.aspx

Various methods to get the Uninstallation String for particular application.You can go with MOF changes to get uninstall string from registry(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall and HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall ) using Mark regkeytomof or You can use BDNA Normalize.

Now aDays ,most of the organizations are converting Applications to MSI with more customizations with unattended installation  except some applications which has msi inbuild with EXE file using Packaging Tools. More info about Packaging stuff,you can get from ttp://www.itninja.com/

You can uninstall particular application if you have MSI ID or product ID using simple Command msiexec /x {ProductID} .

If you have used MSI based application to install on the computer,this information is tracked in SCCM (ProdID is enabled in MOF file by default).

This report talks about specific application and its MSI product ID with count of machines installed on.

select distinct DisplayName0,’msiexec /x ‘+ProdID0,COUNT(*) as ‘Total’  from v_ADD_REMOVE_PROGRAMS where DisplayName0 =@Appname
group by DisplayName0,ProdID0

Prompt:

select distinct DisplayName0, Version0

I have added MSIEXEC /X to the existing product ID to save excel time

If you want to list all adobe products,replace DisplayName0 =@Appname with DisplayName0 like ‘%Adobe%’

Note: Please don’t forget to change the quotes as they are fancy.

SCCM Inbox Folders plays Vital Role in troubleshooting problems with different components. You should keep an Eye on the inbox folders for a backlog of files that wait to be processed.

There are various methods to monitor the inbox folders (tools/Scripts etc). This Post is going to be focus on Despooler.Box\Receive and clean up files residing older than 30 days.

Despooler.box\Receive stores the data that is received from a child site or a parent site and process it accordingly.Typically, files are processed and moved as soon as Configuration Manager 2007 receives the instruction file (.ins file).

In Short about what despooler does is  Despooler component receives the data and hands it off to Replication Manager component then Replication Manager hands the data to the appropriate component to process further.

But Due to some Reasons,Packages which are sending from hierarchy to child sites stuck and breaks the thread and it again starts sending from beginning.Because of this,files remains in Receive folder without being processed for longer time.

If you are in small environment (less than 10 sites or so) you can check it manually by looking at Despooler.Box\Receive what files are Old based on modified date and remove them.

Here is simple VB script that looks for the folder name and get file name,Size and modifieddate. If the Despooler is receiving any files from its parent, you get current date.

Set fso = CreateObject(“Scripting.FileSystemObject”)
Set objinputfile=fso.OpenTextFile(“C:\Scripts\Servers.txt”,1,True)
Set objoutputfile=fso.OpenTextFile(“C:\Scripts\Results.csv”,2,True)
Do While Not objinputfile.AtEndOfStream
strcomputer=objinputfile.ReadLine
Set recive = fso.GetFolder(“\\” & strComputer & “\Driveletter$\SCCM\inboxes\despoolr.box\receive“)
Set colFiles = recive.Files
For Each objFile in colFiles

objoutputfile.WriteLine strComputer & vbTab & objFile.Name & vbTab & objFile.size & vbTab & objFile.DateLastModified

Next
loop
msgbox(“done”)

Note : If you have sccm installed on different Drives with different folder names without unique ,this script may not be helpful to you.

Provide all the site names to servers.txt and results will be piped to results.csv file.

Once you get the results,you can sort it with File Size and Modifieddate to take further action.

I would prefer to delete the files from receive folder older than month.

Here you go with the script to delete files from despoolr.box\receive older than 30 days:

Set fso = CreateObject(“Scripting.FileSystemObject”)
Set objinputfile=fso.OpenTextFile(“C:\Scripts\desplooer-servers.txt”,1,True)
Set objoutputfile=fso.OpenTextFile(“C:\Scripts\despooler-results.csv”,2,True)
Do While Not objinputfile.AtEndOfStream
strcomputer=objinputfile.ReadLine

Set objWMIService = GetObject(“winmgmts:\\” & strComputer)
If Err.Number <> 0 Then
objoutputfile.WriteLine (strcomputer & ” is not Up or problem in connecting to WMI”)
Err.Clear
Else
strDays= 30
Set recive = fso.GetFolder(“\\” & strComputer & “\f$\SMS\inboxes\despoolr.box\receive”)
Set colFiles = recive.Files
For Each objFile in colFiles
If DateDiff(“d”, objFile.DateLastModified, Date) > strDays Then
fso.DeleteFile(objFile)
objoutputfile.WriteLine strComputer & vbTab & “Deleted”
end if

Next
end if
loop
msgbox(“done”)

Until then !

SCCM 2007 has three basic types of distribution points i.e Standard, Server Share and Branch Distribution Points.  Each role had benefits, but there was not always one type that met all the administrator requirements.

This concept is now changed in SCCM 2012 and had come with one Role which is standard distribution point which merges Standard, Branch and Server Share distribution points.

This Distribution Point now has a new storage format called the Content Library.  The Content Library replaces SMSPKG shares as the default folder structure to host content.  The Content Library now stores all content on the distribution point in a single instance storage, this means each unique file is only stored once on the distribution point, regardless of how many times it is referenced by a package.  It also stores the file once on the distribution point even if it is contained in multiple packages!

For more information about Distribution Point Role changes in configmgr 2012 http://blogs.technet.com/b/inside_osd/archive/2011/03/28/configuration-manager-2012-distribution-points-and-pxe-services.aspx

How to install Distribution Point Role on Windows 8 workstation in Configmgr 2012  .

Before you proceed with installing and configuring role on windows 8 machine,ensure you all prerequisisites.

Add the site system Computer (Primary or secondary site) as local administrator on Windows 8 Computer to install DP role.

From SCCM configmgr 2012 SP1 console,right click on Servers and Site system roles

Choose the Right Site here and this site computer name should be added member of Local administrators group workstation.

You have only Distribution Role to configure.

since we do not have WDS on windows 8 workstation,nothing to do in PXE Settings

also No Multicast settings

if you need to validate the Content on a Schedule basis ,configure it

Add the Windows 8 workstation to right Boundary Group. More information about Boundary Group http://technet.microsoft.com/en-us/library/gg699359.aspx

Start monitoring the Distribution Point role from monitoring Node–>Distribution Status—>Distribution Point configuration Status

also you can start monitoring the distmgr.log from secondary site Since the Win8 is assigned to Secondary site.

Which you can also monitor from Details summary on the DP.

also you can verify on Windows 8 Machine if the Folders are created or not.

To Distribute the Content to win8 DP,you can either add the Site to Distribution Point Group so that it takes all the packages which are part of this DP group will be distributed.

To monitor the package status on windows 8 machine,take a look at Distmgr.log from secondary site which will send the packages to Windows 8‘

Here are the entries from IIS to check if the content is available or not.

Recap about Deployment Re-evaluation Though name indicates what it does but still clearing questions if any.

Re-evaluation on Software Updates Client Agent reevaluates(Recheck) software updates for installation status helps when Computers offline/Added newly to collection or removed patches from computers.

Scenario: You have created A deployment with List of patches (100) and deployed to collection consists of 100 machines with deadline date 1 week from current date.

computers which are online and performs below actions based on the settings you choose before they get the updates like how many are required(missing) ,not required ,installed etc.

Software Updates Scan Cycle: Scans for software updates compliance for updates that are new since the last scan. This action does not evaluate deployment policies as the Software Updates Deployment Evaluation Cycle does. This is a forced online scan and requires that the WSUS server is available for this action to succeed

Software Updates Deployment Evaluation Cycle: Evaluates the state of new and existing deployments and their associated software updates. This includes scanning for software updates compliance, but may not always catch scan results for the latest updates. This is a forced online scan and requires that the WSUS server is available for this action to succeed.

Once the scan is done  and policy arrives,Computer pass the time until the deadline(mandatory) is reached .When the deadline reaches,computers will start installing the required patches and reboot if necessary what you choose.

What happens to Computers which are offline/Newly Added/Updates Removed after the Deadline Expiration?

When computers (offline/New/Removed) come online and will perform Software Updates Deployment Evaluation Cycle,they see what is missed from the existing deployment and get them installed as mandatory.

For more information about Software Update Deployment Re-evaluation and how to Configure it,Read http://technet.microsoft.com/en-us/library/bb693813.aspx

Software Updates client agent settings http://technet.microsoft.com/en-us/library/bb632393.aspx

Today I was looking at Packages to see how many packages are created without any distribution Points added. This might help you in identifying whether these packages are really needed to be in SCCM or

archive them. This below report will help you in achieving this.

select PackageID,Name,Version,SourceDate from v_Package where PackageID not in (select packageid from v_PackageStatusDistPointsSumm)

Note : Some packages might use just command line (script based) and they do not have any distribution Points . Double Verify them before you take any action)