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

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

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

Configuration Manager 2007 gives you an option to create New folder (kind of Custom reports folder ) with New Category to create your Own reports what you required But in Configuration Manager 2012,i don’t see way to create New folder to create all the custom reports for your reference.

Creation of Custom folder is not available in configuration Manager 2012 ,the only option left out is to go with Category .

How do you create New Citatory ? If you right click on reports node from Configuration Console,you don’t get such option to create one.

You will have to go with web reports with enough rights to create it.

Open IE with SSRS report manager website : http://SCCMServername/reports

Click on the Report Folder (Configmgr_Sitecode)

Click on New Folder which is actually a Category to create reports underneath.

give A Name what you would like with some Description and click ok

the custom category what we created You see it below :

Now open the Console and go to monitoring node—>reporting–>reports

Now we can create our Custom reports in Custom Folder But how ? you don’t get an option to create new report under Custom Category.

Right click on reports node and select Create Report

Enter the right name for the report and then select the path where you want to store the report under ?

Once you are finished with the report,you will be prompted with Report Builder to enter the SQL query and more customizations.

Hope it Helps!

The way SCCM Configmgr 2012 object information stored is changed from CM07 with respect to tables/views.

In SCCM Configmgr 2012,Status of content stored in dbo.vSMS_DistributionDPStatus view.This view basically contains information about packageID,content status,objectTypeID like what type of package is it(boot image or package/Application etc) and status message ID.

These status Message IDs and State Message IDs are translated here in more description way which will help you to take necessary action to fix them.

What is status messages and State Messages : status messages provide information about component behavior and data flow, whereas state messages provide a snapshot of the state of a process at a specific time. To know more about state messages,read here

Based on these status Message IDs,you can write customized SSRS report to know the status of package. (note: package can be treated as application,Boot images,SU packages etc).

You can use Case Statement while using these Status Messages Ids to put the description filed in reports.

Message State IDs from dbo.vSMS_DistributionDPStatus  :

 here is the case statement for the status Message ID’s:

select PackageID, Name, StatusMessage =
CASE MessageID
WHEN ’2384′ THEN ‘Content hash has been successfully verified’
WHEN ’2330′ THEN ‘Content was distributed to distribution point’
WHEN ’2303′ THEN ‘Content was successfully refreshed’
WHEN ’2323′ THEN ‘Failed to initialize NAL’
WHEN ’2324′ THEN ‘Failed to access or create the content share’
WHEN ’2354′ THEN ‘Failed to validate content status file’
WHEN ’2357′ THEN ‘Content transfer manager was instructed to send content to Distribution Point’
WHEN ’2360′ THEN ‘Status message 2360 unknown’
WHEN ’2370′ THEN ‘Failed to install distribution point’
WHEN ’2371′ THEN ‘Waiting for prestaged content’
WHEN ’2372′ THEN ‘Waiting for content’
WHEN ’2380′ THEN ‘Content evaluation has started’
WHEN ’2381′ THEN ‘An evaluation task is running. Content was added to Queue’
WHEN ’2382′ THEN ‘Content hash is invalid’
WHEN ’2383′ THEN ‘Failed to validate content hash’
WHEN ’2391′ THEN ‘Failed to connect to remote distribution point’
WHEN ’2398′ THEN ‘Content Status not found’
WHEN ’8203′ THEN ‘Failed to update package’
WHEN ’8204′ THEN ‘Content is being distributed to the distribution Point’
WHEN ’8211′ THEN ‘Failed to update package’
ELSE ‘I dont know this MessageID’
END, LastUpdateDate, Status_at_LastUpdateDate =
CASE MessageState
WHEN ’1′ THEN ‘Success’
WHEN ’2′ THEN ‘In Progress’
WHEN ’4′ THEN ‘Failed’
ELSE ‘I dont know this MessageState’
END
from dbo.vSMS_DistributionDPStatus
ORDER BY PackageID;

—-Reference Via danrichings Blog

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.

When i try to run SCCM Reporting Services Report,I get error always saying ‘Cannot create a connection to data source ‘AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_’.

I verified if the user has  sufficient permissions to access the database (datasource) or not,User has enough permissions.

Error Message:

If you look at the above Screen shot,it says “You have specified integrated security or credentials in the connection string for the data source, but the data source is configured to use a different credential type. To use the values in the connection string, you must configure the unattended report processing account for the report server”

I check the properties of Reporting server from SCCM Console under Data Source authentication Tab. I see below Settings.

I changed the settings from Credentials stored securely by the report server to Windows Integrated Security.

With this change,I ran the report again.It worked.

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.

In my previous blogs,I posted couple of reports for Patch compliance status based on the KB/MS ID numbers .

SCCM Patch Compliance Progress report:  http://eskonr.com/2009/10/patch-status-compliancesoftware-updates-report-in-sms-sccm/

Software Update Compliance Status on Specific Collection : http://eskonr.com/2009/09/report-for-software-update-report-for-software-update-compliance/

SCCM monthly Patch statistics report http://eskonr.com/2011/10/sccm-monthly-patch-statistics-reports-to-the-management-in-a-simplified-manner/

This report is going to be interesting .It has 2 SQL queries in one Report .One with count of Active patches for past 1 month and percentage successful

and Other is what are the patches Active on specific collection of machines with Installed,Missing ,required and Percentage of successful.

Report looks like this :

SCCM Report :

Select ‘Total number of active patches within 30days:’, COUNT(distinct Title) AS ‘Count’
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30
UNION
select ‘Percent sucessfully installed’, round(100.0*COUNT( case when LastState=107 or LastState=105 then ResourceID else NULL end)/COUNT(ResourceID),1) as ‘Percent successful’
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30

select ps.ID, ps.QNumbers, ps.Title,
round(100.0*COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end)/COUNT(distinct ps.ResourceID),1) as ‘Percent successful’ ,
COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as ‘Distribution Successful’,
COUNT(distinct case when ps.LastState=101 then ps.ResourceID else NULL end) as ‘Distribution Failed’,
COUNT(distinct case when ps.LastState not in (107,105,101) then ps.ResourceID else NULL end) as ‘Distribution Incomplete’,
COUNT(distinct ps.ResourceID) as ‘In Distribution Scope’,
‘SMS00001′ as ‘CollectionID’,
‘Microsoft Update’ as ‘Type’,
inf.InfoPath
from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx inf on
ps.UpdateID=inf.UpdateID
where fcm.CollectionID= @COLLID and
inf.Type = ‘Microsoft Update’
AND (DATEDIFF(Day, ps.LastStatusTime, GETDATE())) <=30
group by ps.ID, ps.QNumbers, ps.Title, inf.InfoPath

Prompt for COLLID :

select distinct CollectionID,Name from v_FullCollectionMembership

If you need to drill down what are the computers missing specific Path,Create report for Computers missing Particular Patch and link it here.

Do more customizations how you want.

I have exported the report into MOF file ,easy to import without any syntax errors while creating report. Here you go with MOF file 

Until Then!