Posted by Eswar Koneti on 6th April 2013
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
Tags: CM12, configuration Manager, Create custom reports using SCCM 2012 SQL views, Information_Schema.columns, SCCM 2012, SCCM 2012 SQL Views Download, SQL, SQL views, Views
Posted in CM2012, ConfigMgr (SCCM), Configmgr2012, SCCM 2012, SCCM Reports, SQL Quiries, System Center 2012 Configuration Manager | 2 Comments »
Posted by Eswar Koneti on 8th January 2013
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
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)
Tags: All drivers not in Any Package report, configmgr report, Driver packages report, report, Report for Drivers Migration, sccm report, SQL
Posted in CM2012, ConfigMgr (SCCM), Configmgr2012, Migration, SCCM 2007, SCCM 2012, SCCM Reports, SQL Quiries, System Center 2012 Configuration Manager | No Comments »
Posted by Eswar Koneti on 7th January 2013
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
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’,
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.
Tags: advertisement status, cm, configmgr, configmgr report, errocode, LastAcceptanceStatusTime, LastStatusTime, sccm report, SQL
Posted in CM2012, ConfigMgr (SCCM), SCCM 2007, SCCM Reports, SQL Quiries | No Comments »
Posted by Eswar Koneti on 20th November 2012
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.
US.SiteCode, S.ServerName, S.SiteName, US.ContentVersion, US.SyncTime
update_syncstatus US, v_Site S
where US.SiteCode=S.SiteCode and US.contentversion <@Version
Prompt for @Version :
SELECT distinct ContentVersion from
Tags: configmgr report, sccm report, Sites wit WSUS Sync status, sites with their last wsus sync report, SQL, System center configuration Manager 2007 report, WSUS Sync report for all sites in hirarchy, WSUS syncronisation, wsyncmgr.log
Posted in Patch Reports, SCCM 2007, SCCM Reports, Software Updates, SQL Quiries | No Comments »
Posted by Eswar Koneti on 15th October 2012
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.
When 0 Then ‘OK’
When 1 Then ‘Warning’
When 2 Then ‘Critical’
Else ‘ ‘
End AS ‘Site 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!
Tags: sccm all nodes in report, SCCM hirarchy report, sccm installation directory report, sccm report, SCCM report with reporting sites and its child site, sccm site hirarchy report, SQL, SQL code, SQL Quiries
Posted in SCCM 2007, SCCM Reports, SQL Quiries | No Comments »
Posted by Eswar Koneti on 22nd September 2012
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
Tags: Distribution Point, sccm report, SCCM report compare packages, SCCM Report Compare Packages on 2 DP's, SQL, SQL code
Posted in SCCM 2007, SCCM 2012, SCCM Reports, SQL Quiries | No Comments »
Posted by Eswar Koneti on 18th September 2012
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.
join dbo.v_FullCollectionMembership FCM on R.ResourceID = a.ResourceID
join dbo.v_Collection COLL on COLL.CollectionID = FCM.CollectionID
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/
Tags: Applications on users collections, List all all collection user member of, Member collections, sccm report, SQL, SQL code, SQL report, User member of What collections, Users
Posted in SCCM 2007, SCCM Videos, SQL Quiries | No Comments »
Posted by Eswar Koneti on 30th August 2012
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.
JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of
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.
Tags: Create SCCM reports using SQL Joins, Full Join, Inner join, Joins in SCCM reports, Left join, Reports using SQL Joins, SCCM Reports, SQL, SQL reports
Posted in SCCM 2007, SCCM 2012, SCCM Reports, SQL Quiries | 2 Comments »
Posted by Eswar Koneti on 23rd August 2012
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 =
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’
COUNT(PackageType) as Total
packageID not in (select PackageID from dbo.v_Advertisement)and
PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)
group by PackageType
Tags: Package Types in SCCM, sccm 2007 report, sccm package Types, sccm report packages not used, sccm report packages with no advertisements, SQL, SQL code, SQL report
Posted in SCCM 2007, SCCM Reports, SQL Quiries | 2 Comments »
Posted by Eswar Koneti on 1st August 2012
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]
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.
Tags: Packages count on Distribution Point, sccm report, SCCM report count of packages avilable on DP, SQL, SQL report
Posted in SCCM Reports, SQL Quiries | No Comments »