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

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.

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

Sometimes I find Difficult to locate specific site from the Hierarchy(mostly if you are in large environment with many nodes) until you know where they attached to.

I use the report data most of the times as reference to locate where the site resides based in its reporting site (primary) and primary site reporting to which.

Select
C.servername,b.SiteCode,C.SiteName,C.ReportingSiteCode, C.InstallDir,
Case b.Status
When 0 Then ‘OK’
When 1 Then ‘Warning’
When 2 Then ‘Critical’
Else ‘ ‘
End AS ‘Site Status’,
Case C.Status
When 1 Then ‘Active’
When 2 Then ‘Pending’
When 3 Then ‘Failed’
When 4 Then ‘Deleted’
When 5 Then ‘Upgrade’
Else ‘ ‘
END AS ‘Site State’
From V_SummarizerSiteStatus B
Join v_Site C on B.SiteCode = C.SiteCode
join v_GS_LOGICAL_DISK LD on c.Servername=LD.SystemName0
group by C.servername,b.SiteCode,C.SiteName,C.ReportingSiteCode,C.InstallDir,b.Status,C.Status
Order By B.SiteCode

Hope It helps!

Until Next

Ok You are done with installation of Primary / Secondary site and you needed to distributed the packages to site but you want to check if there are any packages missing from its Parent or other site.

Here is simple report list all the packages in comparison with other DP.

select s.SiteCode,s.PackageID,p.Name,(p.sourcesize)/1024 as "size(MB)",s.sourceversion as "Source Version",p.storedpkgversion as PkgVersion,s.Installstatus as ‘Package Status’
from v_PackageStatusDistPointsSumm s
inner join smspackages p on s.packageid = p.pkgid
where s.PackageID not in (select PackageID from v_DistributionPoint where SiteCode=’P01′) and s.SiteCode=’S01′

order by p.SourceSize

If you in environment where in the deployment of Application is User Based and you are sometimes getting issues if User doesn’t get apps so you will have to check if User member of AD Security groups and then check if user added to right collection with right advertisement.

Here is Simple report that give you User member of What collections .Based on this you can get to know if user has right apps.

select
COLL.CollectionId,
FCM.Name
from
dbo.v_R_User U
join dbo.v_FullCollectionMembership FCM on R.ResourceID = a.ResourceID
join dbo.v_Collection COLL on COLL.CollectionID = FCM.CollectionID
Where
U.User_Name0 =@UserName

Prompt for UserName : select User_Name0 from v_R_User

This UserName(login Account or alias Account) you can get GAL.

For Sub collections and Path to identify where the collection Resides,check this out http://blogs.catapultsystems.com/mdowst/archive/2012/02/23/finding-sccm-sub-collections.aspx and http://eskonr.com/2009/07/sccm-report-computers-member-of-which-collection/

Joins ? In Simple terms they are used to Join more than 2 tables and give you required information.

while working with SCCM reports,we will use SQL Joins in most of the reports to get customized information (not part of default reports).

These joins  are used to query data from two or more tables, based on a relationship between certain columns in these tables. It creates a set that can be saved as a table or used as is.

A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOIN: INNER, OUTER, LEFT, and RIGHT.

Here is nice Pictorial representation of using SQL Joins.You can keep it as reference when creating reports/collections on the need basis.

INNER JOIN : This Join used to get result when there is at least one match in both tables.

LEFT JOIN :This Join used to get all rows from the left table even if there are no matches in the right table.

RIGHT JOIN : This Join used to get all the rows from the right table, even if there are no matches in the left table

Full Join : This Join used to get all rows when there is a match in one of the tables.

This Report talks about all package types like software distribution package,drive package,TS package etc for which there is no advertisement created and not used in any of the Task sequences .Take action what you would

which you can take it further to action on them what to do .

select     PackageType =
Case Packagetype
When 0 Then ‘Software Distribution Package’
When 3 Then ‘Driver Package’
When 4 Then ‘Task Sequence Package’
When 5 Then ‘software Update Package’
When 6 Then ‘Device Settings Package’
When 7 Then ‘Virtual Package’
When 257 Then ‘Image Package’
When 258 Then ‘Boot Image Package’
When 259 Then ‘OS Install Package’
End ,
COUNT(PackageType) as Total
from
dbo.v_package

Where
packageID not in (select PackageID from dbo.v_Advertisement)and
PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)

group by PackageType

If you are performing SCCM cleanup activity on packages,this might help you to identify if there are any packages still available on DPs rather looking at each package manually also to check how many DP’s each package available so you can distribute the package to missing DP’s(if you have standards to ensure all packages should be on X many DP’s)

SELECT PackageID, Name, Version, Manufacturer, Language, Description,PkgSourcePath , LastRefreshTime,
(SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = v_Package.PackageID) AS [Total Number of DP’s]
FROM v_Package
order by [Total Number of DP’s]  desc

P.S :Quotes in this post are fancy quotes,please replace them in your query while pasting.