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

If you are planning to migrate your SCCM 2007 environment to SCCM 2012,you may have to think about your packages,collections,OSD and other stuff.

you cannot migrate all collections from SCCM 2007 to SCCM 2012.Couple of things to note before you migrate them.

Points to note  on collection migration:

1. Collection can contain only either devices or users but not both.

2.  Every collection must have Limitation.

3.   Sub collections are no longer exists instead you have folders.

blogged couple of posts on archiving/removing packages not been used for X days that will help you to not migrate them to CM12.

In this blog,will show you how to identify the collections that has both users and computers member of it  to look at the collections if you want to take of users/Computers query before you go on migration.

Configmgr Report:

select
fcm.CollectionID ‘Collection ID’,COLL.Name
from
v_FullCollectionMembership fcm,v_Collection COLL
where fcm.CollectionID=coll.CollectionID
group by
fcm.CollectionID,COLL.Name
having
count(distinct fcm.ResourceType) > 1

When you are doing Migration from SCCM 2007 to SCCM 2012,you may want to remove some of the drivers which are no longer needed .

This report list all the drivers which are part of any driver package so you can take action on them before you migrate to SCCM Configmgr 2012.

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c82cc16a-06b7-49ae-ac11-2f6399047b6a

select
locCI.DisplayName as DriverName,locci.CI_ID,
drivers.DriverClass as DriverClass,
drivers.DriverProvider as ProviderName,
drivers.DriverVersion as DriverVersion,
CIs.IsEnabled as DriverEnabled0
from v_CI_DriversCIs drivers
inner join v_ConfigurationItems as CIs on CIs.CI_ID=drivers.CI_ID
inner join v_LocalizedCIProperties_SiteLoc as locCI on drivers.CI_ID=locCI.CI_ID
where locCI.CI_ID not in (select ci_id from v_DriverContentToPackage)

Status of Specific Advertisement can get from Default available report but what if you want to find the status of specific advertisement with its LastAcceptanceStatusTime greater or less than X days.

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/30e4dbda-982b-43dc-8665-8dd142925d4f

DECLARE  @olddate datetime
        ,@NullVal datetime
SET @olddate = DATEADD(day,-5, GETUTCDATE())

select adv.AdvertisementName ‘Application’, sys.Name0 ‘System’, LastAcceptanceStateName ‘Acceptance’,
sts.LastAcceptanceStatusTime ‘AcceptTime_GMT’, sts.LastStatusMessageIDName ‘ExecutionStatus’,
sts.LastStateName ‘ExecutionState’,sts.LastStatusTime ‘ExecutionTime_GMT’,
adt.MessageName,sts.LastExecutionResult
from v_ClientAdvertisementStatus sts
join v_AdvertisementStatusInformation adt on adt.MessageID=sts.LastStatusMessageID
join v_Advertisement adv on adv.AdvertisementID=sts.AdvertisementID
join v_R_System sys on sys.ResourceID=sts.ResourceID
where ((sts.LastAcceptanceStatusTime  < @olddate) or (sts.LastStatusTime  < @olddate))
and  adv.AdvertisementName like ‘%Adobe_reader_Eng%’ and sts.LastExecutionResult like ‘1603‘
order by sys.Name0

Change the number of days and Advertisement Name.

Note: Please change the Quotes ,blog converts them as fancy here.

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 report for Domain Controllers with RAM,Operating System,IP Address etc.

Full Details : http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/0dedf567-7bbd-4c36-bc7a-12c409a79618

SELECT

      sys.Name0 AS ‘System Name’,

      os.Caption0 AS ‘Operating System’,

      os.CSDVersion0 AS ‘Service Pack’,

      PM.TotalPhysicalMemory0 /1024 as ‘RAM in MB’,

      OS.InstallDate0 AS ‘Original OS Install Date’,

      os.LastBootUpTime0 AS ‘Last Boot Time’,

      NAC.IPAddress0 AS ‘IP Address’,

      NAC.IPSubnet0 AS ‘Subnet Mask’

FROM

      dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC

INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON NAC.ResourceID = OS.ResourceID

INNER JOIN dbo.v_GS_PC_BIOS BIOS ON NAC.ResourceID = BIOS.ResourceID

INNER JOIN dbo.v_GS_SYSTEM sys ON NAC.ResourceID = sys.ResourceID

INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS on NAC.ResourceID = CS.ResourceID

INNER JOIN dbo.v_GS_X86_PC_MEMORY PM on NAC.ResourceID = PM.ResourceID

Where

      NAC.IPaddress0 is Not Null

and CS.Roles0 like ‘%Domain_Controller%’

 

Note : Blog converts the quotes to fancy, Please replace them.

SCCM Package Clean Up activity? When you Build SCCM in your Environment, you create lot of packages to deploy onto clients But later sometime, you get updated versions for it   or no more in use.

Packages which are not in use resides in Database and occupies disk space on the source server as well on the Distribution Points.

As SCCM admin guy, you should look at package clean up activity once in year or so depends on the requirement.

For Packages which has no advertisements, look here http://eskonr.com/2012/10/sccm-report-software-distribution-packages-with-no-advertisements/

Use this report to list packages which are not used recently for 1 year based and it is filtered with its advertisement status. This will not get the packages which are used in Task Sequences.

If you have any packages which are not used for 1 year but still if they are part of Task Sequence, you won’t see the results.

SELECT   
      A.AdvertisementName AS [Advertisement Name],
      A.PresentTime AS Created,
      MAX(CAS.LastStatusTime) AS [Last Time Used],
      p.packageID,
      A.ProgramName,
      A.CollectionID,
      P.PkgSourcePath,
            (SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = p.PackageID) AS [No of DP]
FROM
      v_Package P
      INNER JOIN v_Advertisement A ON P.PackageID = A.PackageID
      INNER JOIN v_ClientAdvertisementStatus CAS ON A.AdvertisementID = CAS.AdvertisementID
WHERE
      (CAS.LastStateName != ‘Accepted – No Further Status’)AND
      p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and p.Packagetype=0

GROUP BY
      A.AdvertisementName,
      A.PresentTime,
      A.CollectionID,
      P.PkgSourcePath,
      P.Name,
      P.PackageID,
      A.ProgramName,
    P.Description
HAVING
      (MAX(CAS.LastStatusTime) IS NOT NULL)
      AND datediff(mm,MAX(CAS.LastStatusTime),getdate()-365) > 0

ORDER BY
      A.AdvertisementName,
      A.PresentTime

Good Luck!

Report provide count of computers from the collections.

Full Details: http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/f0cccbd7-7616-4d06-a3a6-31d4c583e2f0

SELECT v_Collection.Name ,v_Collection.Comment,v_Collection.CollectionID,COUNT(*) as [Members] FROM v_Collection,v_FullCollectionMembership
where v_Collection.CollectionID=v_FullCollectionMembership.CollectionID
group by v_Collection.Name ,v_Collection.Comment,v_Collection.CollectionID
order by v_Collection.Name

Having multiple sites and checking the status of WSUS synchronization on all servers tedious job and child sites must sync with its parent sites to ensure right compliance levels from clients.

This report will give you the status of WSUS server with Right content version.

When SCCM sync with Microsoft Updates,Content version will get increment to +1 and it passes this information to its child sites to sync with right version.

SELECT 
    US.SiteCode, S.ServerName, S.SiteName, US.ContentVersion, US.SyncTime
FROM
    update_syncstatus US, v_Site S 
    where US.SiteCode=S.SiteCode and US.contentversion <@Version
ORDER BY
    SyncTime

Prompt for @Version :

SELECT      distinct ContentVersion from
    update_syncstatus

In previous post,we saw how to get list of referenced task sequence packages used in Task Sequence which are incomplete.

In this report,we see how to get list of Distribution Points who do not have these referenced packages used in Task Sequence so you know all these sites which do not have package may fail during the OSD Policy check.

Count  : Distribution Points who do not have these packages used in Task Sequence

select sitecode,COUNT(distinct packageID) as [Not Distributed] from v_PackageStatusDetailSumm PSD
where PSD.Targeted=’0′ and PSd.PackageID in (select ReferencePackageID from v_TaskSequenceReferencesInfo
where PackageID =@TS1)
group by psd.SiteCode
order by psd.sitecode

what Task Sequence Reference packages missing from Which DP :

select PSD.PackageID,PSD.Name,psd.SiteCode,PSD.SiteName,PSD.SummaryDate  from v_PackageStatusDetailSumm PSD
where PSD.Targeted=’0′ and PSd.PackageID in (select ReferencePackageID from v_TaskSequenceReferencesInfo
where PackageID =@TS1)
Group by PSD.PackageID,PSD.Name,psd.SiteCode,PSD.SiteName,PSD.SummaryDate
order by SiteCode

Prompt for TS1 :

select PackageID,Name from v_TaskSequencePackage

Fix it before you advertise any Task Sequence to Computers .