Eswar Koneti's Blog

SQL Code used in SCCM Reporting to get list of Advertisements that are Mandatory!

select adv.AdvertisementName as [Adv Name],
adv.PresentTime as DistributionDate,
pkg.Name as PackageName, pgm.ProgramName, coll.Name as CollectioName,
adv.AdvertisementID
from v_Advertisement adv
join v_Package pkg on adv.PackageID=pkg.PackageID
join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName
join v_Collection coll on adv.CollectionID=coll.CollectionID
join v_ClientAdvertisementStatus stat on adv.AdvertisementID=stat.AdvertisementID
where (adv.AssignedScheduleEnabled != 0 or adv.AdvertFlags & 0X720 != 0)
group by adv.AdvertisementID, adv.AdvertisementName, adv.PresentTime,
pkg.Name, pgm.ProgramName, adv.SourceSite, coll.Name
order by adv.AdvertisementName

SQL Code used in SCCM reporting to get list of advertisements that are Optional.

select adv.AdvertisementName as [Adv Name],
adv.PresentTime as DistributionDate,
pkg.Name as PackageName, pgm.ProgramName, coll.Name as CollectioName,
adv.AdvertisementID
from v_Advertisement adv
join v_Package pkg on adv.PackageID=pkg.PackageID
join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName
join v_Collection coll on adv.CollectionID=coll.CollectionID
join v_ClientAdvertisementStatus stat on adv.AdvertisementID=stat.AdvertisementID
where (adv.AssignedScheduleEnabled = 0 or adv.AdvertFlags & 0X720 = 0)
group by adv.AdvertisementID, adv.AdvertisementName, adv.PresentTime,
pkg.Name, pgm.ProgramName, adv.SourceSite, coll.Name
order by adv.AdvertisementName

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!

Long ago ,written SCCM report to list packages not used for 6 months as part of Package Archival process http://eskonr.com/2012/11/sccm-configmgr-package-archival-process-cleanup-activity/ .This report tells you to take further action to clean or move to Archive folder to wait for some more months before it goes for Deletion.

What next ? Delete these packages from Assigned DPs to get some disk space ?

You may find several scripts to do this task on the internet but, i  find this is easy for me to get this activity done.

Identify list of package which you want to remove ,pipe them to notepad .You can do more customizations if needed.

‘Script to delete packages from its assigned Distribution Points.

strComputer =inputBox("Please Enter the SMS provider OR Site where the packages are created" , "SCCM Server name")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set packages=fso.OpenTextFile("C:\PACKAGESTODELETE.txt",1,true)
Set objoutputfile=fso.OpenTextFile("C:\DP_results.txt",2,true)

Do While packages.AtEndOfLine <> True
    ‘read the next line
    package = packages.Readline

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\SMS\site_CEN")
if err.number<>0 then
    msgbox "server connection failed"
    wscript.quit
end if
Set colItems = objWMIService.ExecQuery("SELECT * FROM SMS_DistributionPoint where packageid=’" & Package & "’")
For Each objItem in colItems
        ‘Wscript.Echo "ServerNALPath: " & objItem.ServerNALPath
objoutputfile.WriteLine ( package & vbTab & " will be deleteting from" & VBTAB & objItem.ServerNALPath)

objitem.Delete_

If Err.number <> 0 Then

objoutputfile.WriteLine ( "Failed to delete" & vbTab & package & "from" &  vbTab & objItem.ServerNALPath)
      End If
    Next

loop

msgbox "Done"

Note: Please Replace the quotes.

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

With the Release of SCCM Configmgr 2012 SP1 RTM,When you deploy Secondary Site from its Parent site ,it will install SQL Server Express 2008 R2 Service Pack 1 (SP1) but as you can see from below snap (SQL server Requirements),the minimum required cumulative update is CU6 which you will have to install it manually.

Read more on http://support.microsoft.com/kb/2688247/en-us?sd=rss&spid=1060:SQL

Getting AD information into SCCM Database can be done using AD discovery Methods From SCCM Configmgr but there are cases where in some of the computers may not be discovered or Computers do not exist in AD but do available in SCCM Database.

I used MaikKoster Old blog post as reference and made some customizations/Updates in the query to retrieve information about only Active Computers (Exclude disabled Machines).

Comparing AD Computer information with SCCM Database sometimes interesting to know about client health and are there any missing out clients did not discover into SCCM Database.

This also helps you to focus more on clients inventory issues .There are cases where in User will be logged into domain(last logon timestamp) but client will not report to site .With this you can create hardware inventory report for computers not reported with in X number of Days with AD last logon Timestamp.(if your discovery is not customized and run frequently).

You can use this procedure in SMS 2003, SCCM 2007 and SCCM 2012 environments.

Updated : Please be clear what information do you want to retrieve from AD into SCCM Database in the initial phase else you may lead to an issue to update the existing table with custom values.

I tried adding more attributes from AD to the existing SQL Job what is already been created but i failed . I have to drop the table ,Change the query and run the job to make it Work.

This procedure requires you to download Log Parser (http://www.microsoft.com/en-us/download/details.aspx?id=24659) and install it on CM server.If your SQL hosting on different Box,Install it on SQL Box with default settings (next,next,next,close )

Start SQL server Management Studio

Move onto SQL server Agent—>Jobs—>Create New Job

On the General Tab,Enter the required Fields as per appropriate.

Move to Next tab Steps .This is the step where you Create Job to execute and get AD computer Information into SCCM Database.

Click on New

You are prompted to select Different fields like Step Name (AD info), Type(ActiveX Script) and Run as(SQL server Agent Service Account).

Next is to import the the script to execute. Download the Script from Maikkoster website here and Import it using Open.

Once you are imported , you are required to supply values for the below Objects :

strLDAP = “LDAP://eskonr.com”
strSQLServer = “sccmSQL”
strDatabase = “SMS_CEN”
strTable = “ADComputersInfo“

Note: Strtable is Table not view

Replace the below customized query with existing One after ‘ Create query text

strQuery = “SELECT cn, objectpath, operatingSystem, operatingSystemServicePack”
strQuery = strQuery & “, CASE LastLogonTimestamp WHEN 0 THEN NULL ELSE ”
strQuery = strQuery & ” TO_TIMESTAMP(ADD(DIV(TO_REAL(LastLogon), 10000000.0), TO_REAL(TIMESTAMP(’1601′,’yyyy’)))) END AS [LastLogon]”
strQuery = strQuery & “, CASE pwdLastSet WHEN 0 THEN NULL ELSE ”
strQuery = strQuery & ” TO_TIMESTAMP(ADD(DIV(TO_REAL(pwdLastSet), 10000000.0), TO_REAL(TIMESTAMP(’1601′,’yyyy’)))) END AS [PwdLastSet]”
strQuery = strQuery & ” INTO ” & strTable & ” FROM”
strQuery = strQuery & “‘” & strLDAP & “‘” & ” where ”
StrQuery = strQuery & “userAccountControl” & “=” & “4096″

Note : You can do customizations to the above query depends on your requirement.

“Please replace the Quotes as the blog converts them to fancy ”

Click Ok.

Go to Schedule Tab,Click New

Schedule it as per the requirement.

Click on Ok, OK

Start the job which we created now.

You see from below,Job is executed successfully.

Now its time to create Various reports based on your needs.

Simple report to list Computers which are not available in SCCM but do Exist in AD as Active.

SELECT [cn] AS ‘Computer Name’
,[ObjectPath] AS ‘Path’
,[operatingSystem] AS ‘OS’
,[operatingSystemServicePack] AS ‘SP’
,[LastLogon]
,DATEDIFF(dd, [LastLogon], getdate()) AS ‘days LastLogon’
,[PwdLastSet]
,DATEDIFF(dd, [PwdLastSet], getdate()) AS ‘days PwdLastSet’
FROM [DatabaseName].[dbo].[tablename]
WHERE cn NOT IN (SELECT name0 FROM v_GS_Computer_System)
ORDER BY LastLogon

Replace the Database name and table what you given in VB Script earlier.

Until Next !

Recently I had major issue while replicating the patch packages to sites and they started experiencing the problem to process the patch information (CID and SDMpackages).

I am really not sure if this is because of recent MS patch released in October 2012,More info can be found here  ,due to this,all the revised updates (which you can see from WSUS console )went back downloaded=No from Downloaded=Yes .

Later month Ago,MS released fix for this to get the issue sorted out also some other functions more here http://support.microsoft.com/kb/2783466

Below is the Error message which I get on problem site (distmgr.log) and unable to insert the Patch info into Database.The reason I tell you below why it is not.

“Failed to Insert SMS package CEN00XXX because SDM type Content xxxxxxxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxx is not prensent in CI-Contents Table,will try later”

 

from objreplmgr.log :

Processing replication file F:\SMS\inboxes\objmgr.box\INCOMING\Retry\Sitecode_66082.CID in retry.

SDM Package Name Site_B97EB0D1-435F-4CEC-88B7-5A1E7645B585/SUM_07f87a3d-23b3-4ec1-b35c-6461a82ded35 Version 2 has not arrived yet. Will retry

Failed to insert Object 07f87a3d-23b3-4ec1-b35c-6461a82ded35 from replication file F:\SMS\inboxes\objmgr.box\INCOMING\Retry\Sitecode_66082.CID.

Problem site is looking for object 07f87a3d-23b3-4ec1-b35c-6461a82ded35 with version 2 which is not arrived.

Before you follow this guide,I would suggest you to read steve Rachui Blog explaining about Software updates and process Part1 and Part 2.Thanks to Steve for nice explanation on software updates and CI.

*************This post is more of Database Editing to fix the issue and no warranties provided. If you don’t know what you are doing /without enough information,you are at Risk**************

To check how many CIDs left over to process, check it from inbox folders . Path \Inboxes\objmgr.box\incoming\retry

If the CID files are processed max number of times,they are moved to retry\bad folder.

To get all SDMpackage names,try to filter the log with word “SDM Package Name” ,you may get bulk number of IDs out of which some of them are Repeated.

Copy the SDM package names to Excel and remove duplicates.

Copy the results to excel and remove the duplicates. You now have all SDM packagenames.

I have something below from the log/Excel after removing duplicates.

Site_B97EB0D1-435F-4CEC-88B7-5A1E7645B585/SUM_0d9f9145-9e83-4c44-a8d5-9decbe25a586

Site_B97EB0D1-435F-4CEC-88B7-5A1E7645B585/SUM_cc5d5d63-32bb-4593-bd6f-3b61ba97707f

Site_B97EB0D1-435F-4CEC-88B7-5A1E7645B585/SUM_f4ba81de-c60b-4e2f-9589-9cc888bc09da

If you want to know more about what it says,Please Refer Steve blog.

Let’s have a look at Database on problem site and its parent site to see what information they contain and are they really out of sync ?

I take an example with central,Parent and child

Take the first ID :0d9f9145-9e83-4c44-a8d5-9decbe25a586

If I run the below query on central DB ,I get below results :

select * from CI_SDMPackages WHERE (SDMPackageName like ‘%0d9f9145-9e83-4c44-a8d5-9decbe25a586%’)

Problem Parent :

Problem site :

Look at problem site, it still has version 1 and IsDeleted value is not set to 1 and it does not have latest Version =2.

How do we fix it ?

We will change the LastModifiedData and set Isdeleted=1 for these error Objects on central and let the sync happens with child sites.

Set the DateLastmodified on Central DB:

UPDATE CI_ConfigurationItems SET DateLastModified = GETDATE() WHERE CI_UniqueID in (

‘0d9f9145-9e83-4c44-a8d5-9decbe25a586’,

‘cc5d5d63-32bb-4593-bd6f-3b61ba97707f’,

‘f4ba81de-c60b-4e2f-9589-9cc888bc09da’)

UPDATE CI_SDMPackages SET DateLastModified = GETDATE() WHERE (

SDMPackageName like ‘%0d9f9145-9e83-4c44-a8d5-9decbe25a586%’ OR

SDMPackageName like ‘%cc5d5d63-32bb-4593-bd6f-3b61ba97707f%’ OR

SDMPackageName like ‘%f4ba81de-c60b-4e2f-9589-9cc888bc09da%’ )

Run the below query on problem site to Fix isdeleted=1 for the older version and let the current version sync.

update ci_sdmpackages

set IsDeleted = 1

WHERE (

SDMPackageName like ‘%0d9f9145-9e83-4c44-a8d5-9decbe25a586%’ OR

SDMPackageName like ‘%cc5d5d63-32bb-4593-bd6f-3b61ba97707f%’ OR

SDMPackageName like ‘%f4ba81de-c60b-4e2f-9589-9cc888bc09da%’ )

and SDMPackageVersion = ’1′

Let’s wait for couple of hours to sync the modified data with problem site.

So here is the outcome on problem Site after couple of hours

Now move the .CID file back to objmgr.box\incoming folder from retry folder to process it and monitor objreplmgr.log if that succeeds or not.

Note :This procedure is recommended only if you have less objects failed rather going with synchild/.sha that generates huge traffic .If more objects failed to process, go with synchild/.sha non-office hours.

Detailed information available here on http://blogs.technet.com/b/mwiles/archive/2011/06/24/troubleshooting-failed-to-insert-object-error-message.aspx

This post is in Continuation to previous post on Wally Mead sessions on SCCM 2012. http://eskonr.com/2012/11/sccm-2012-videos-wally-mead-sessions/

System Center Configuration Manager 2012, Managing non-Windows systems

httpv://www.youtube.com/watch?v=Rsl7skEII_Q

SCCM GURU Webcast Series – Episode 6: Wally Mead Returns

httpv://www.youtube.com/watch?v=ZdKNeNIagn0

Wally Mead, System Center Configuration Manager 2012, QA

httpv://www.youtube.com/watch?v=ZdKNeNIagn0

System Center Configuration Manager 2012 Overview, Wally Mead

httpv://www.youtube.com/watch?v=jTqWBFrxLXs

How to make a successful ConfigMgr implementation, Wally Mead

httpv://www.youtube.com/watch?v=ya5kF6NIuPs

Rest walkthrough on http://eskonr.com/2012/11/sccm-2012-videos-wally-mead-finlan/