Migrate reports from SCCM 2007 to SCCM 2012

sync SSRS report between two report servers:

The built-in migration wizard in ConfigMgr 2012 can migrate most objects but not reports. In ConfigMgr 2012 classical ASP reports (as we know them from earlier versions) are no longer supported.
Migrate the classical reports to SQL Reporting Services in SCCM 2007
Below is a walk thru of how you migrate your existing classical reports to SQL reporting Services in SCCM 2007. Notice that you will still be able to run all classical reports after the migration.

1. On you SCCM 2007 site server install either SCCM 2007 R2 or SCCM 2007 R3, both will add SQL Reporting Services support for SCCM 2007.
2. In the ConfigMgr. console, navigate to Site Database, Site Management, A01 (where A01 is the sitecode), Site Systems. Right click the Site system server that will become a Reporting Services Point and click New Roles. Select Reporting Services Point and finish the wizard using the default values. 
3. In the ConfigMgr. console, navigate to Site Database, Computer Management, Reporting, Reporting Services, and expand the node.
4. Right-click SCCM1 (where SCCM1 is the name of your server), and then click Properties.
5. On the Data Source Settings tab, specify SCCM1 as the database server and SMS_A01as the database name (where SMS_A01 is the name of your SCCM 2007 database. ClickTest.
6. On the Data Source Authentication tab, select Windows integrated security.
7. Click OK.

Copy reports to SQL Reporting Services

1. Right-click SCCM1, and select Copy Reports to Reporting Services.
2. On the Data Source Settings page, accept the default settings, and click Next.
3. On the Data Source Authentication page, accept the default settings, and click Next.
4. On the Select Reports page, accept the default settings, and click Next.
5. On the Security page, accept the default settings, and click Next.
6. On the Summary page, click Next.
7. On the Progress page - once all of the reports have copied - click Next and then clickClose.

Migrate the SCCM 2007 reports to SCCM 2012
You can’t use the built-in migration tools to migrate reports from SCCM 2007 to SCCM 2012. This leaves you with two choices; 1) Download each RDL file one by one from SCCM 2007 and upload them to SCCM 2012 or 2) Use one of the free community tools that can download multiple folders at the time. In my last project I used this tool – ReportSync - to assist me.

1. Launch ReportSync
2. In Source server type in the name of the SCCM 2007 Report server in form of http://CM01/ReportServer. Provide credentials and click Load
3. In Destination server type in the name of the SCCM 2012 Report server in form of http://CM02/ReportServer. Provide credentials and click Load
   
4. Select the folders or individual reports you want to migrate and click Sync
   
5. Once the process is finished click OK and your reports are now ready in SCCM 2012.
6. This tool keeps the old Data source which will generate an error the first time you run the report.
   
7. Open the report properties (by clicking on the report link)
8. Select Data Sources, click Browse and select the shared SCCM 2012 data source. The data source is found in Home\ConfigMgr_Sitecode. The data source is the object with long GUID. Select the data source click OK and Apply.
    
9. Run the report and verify that it works. There is no guarantee the all SCCM 2007 reports work out of the box in SCCM 2012.
   

Download link : http://code.google.com/p/reportsync/downloads/list

All credits goes to Kent.

A consolidated Report for Patch Deployments

to get the consolidated Report for all your deployments / instead of depending on dashboard you can view this PVT Report

 

Source:- http://blog.coretech.dk/kea/status-report-for-software-update-deployments/

Select Deploymentname, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '%Compliant',

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

SCCM / SMS Backup Report

To know what sites haven't backed up within a given date range (a day and a half since last backup), and those that have never backed up (never reported a backup).

Groovy stuff, not so easy to determine though, or so it would seem, unless you fall back entirely onto the Status Message sub-system.

When a SMS2003 or ConfigMgr backup completes, a Status Message is produced by the SMS_SITE_BACKUP component and processed by the Site server. As you probably know, Status Messages replicate up the hierarchy, so the Central Site server will know all Status Messages being "sent up".

I've had this code running for a few days, it seems to work a charm.

There is one thing that can get in the way though, customised Status Filter Rules. If you have them, you understand them (unless you've inherited in which case GIYF), and know that they can block Status Messages from being processed or delivered to the Parent Site server (drop all informational for example, as the last rule in the rule set, yes some folks do this!).

The T-SQL to bring back the Status Messages, and to render a list of machines that do not fit the logic (no backup reported, ever) is here:

SELECT vsite.ServerName AS Servername, vsite.SiteCode, MAX(stat.Time) AS LastTime

FROM vStatusMessages AS stat LEFT OUTER JOIN

StatusMessageInsStrs AS ins ON ins.RecordID = stat.RecordID LEFT OUTER JOIN

StatusMessageAttributes AS att1 ON att1.RecordID = stat.RecordID LEFT OUTER JOIN

v_Site AS vsite ON vsite.ServerName = stat.MachineName

WHERE (vsite.Type = 2) AND (stat.Component = 'SMS_SITE_BACKUP') AND (stat.MessageID = 5035)

GROUP BY vsite.ServerName, vsite.SiteCode

HAVING (MAX(stat.Time) < DATEADD(day, - 1.5 , GETDATE()))

ORDER BY LastTime

select 'Sites that have never reported a backup (Investigate any server that is listed)'

select servername from v_site where type = 2 and servername not in

(SELECT vsite.ServerName AS Servername

FROM vStatusMessages AS stat LEFT OUTER JOIN

StatusMessageInsStrs AS ins ON ins.RecordID = stat.RecordID LEFT OUTER JOIN

StatusMessageAttributes AS att1 ON att1.RecordID = stat.RecordID LEFT OUTER JOIN

v_Site AS vsite ON vsite.ServerName = stat.MachineName

WHERE (vsite.Type = 2) AND (stat.Component = 'SMS_SITE_BACKUP') AND (stat.MessageID = 5035)

GROUP BY vsite.ServerName, vsite.SiteCode

HAVING (MAX(stat.Time) >= DATEADD(day, - 1.5 , GETDATE())))

 

 

The Query itself is entirely harmless, in that it doesn't UPDATE or DELETE, but you should be aware it can put a load on your DB instance while running. Nothing to big to worry about, unless you are seriously underspec'd hardware-wise.

And finally, be aware that as long as Status Filter rules are not inhibiting the delivery of the Status Message from down-level sites up the hierarchy, and that Sites in your hierarchy are communicating with their Parent's correctly, then you should be OK to rely on this report. I would suggest having some more reports configured to check for any sites that haven't reported in within the last 24hrs (indicative of some kind of failure taking place) and also to look at using dedicated Monitoring tools such as OpMgr to monitor Site servers and Site systems health using the Management Pack.

Checking Patch Statuses through WMI

Normally the optimal and quickest way to determine if a patch has definitely been installed on a system is to use WMI. That's where the SCCM client primarily gets its information from to report back to its parent. There are several ways you can go about but the ways i've found to be ideal for me are described below:

Through the command prompt:

1. On the system which you wish to query, open up command prompt

2. Run "Wmic /namespace:\\root\ccm\softwareupdates\updatesstore path CCM_UpdateStatus get status, Article, Bulletin, UniqueId"

3. This will show you ALL updates on the particular system. I've added additional information for reference but you can always play around with what you wish to see returned.

4. This is best for an overview and you can quickly do a find or filter to determine a patch status if you pipe it to a txt file.

Through the Wbemtest User Interface:

1. Start "WBEMTEST" from a run prompt

2. Connect to the namespace "\\<computername>\root\ccm\softwareupdates\updatesstore

3. Select the Query button and using WQL, create a statement that suits your needs. Some examples are below:

   1. • Searching for a Missing Patch that is for Web Components would be:

        • select * from ccm_updatestatus where status = "missing" and title like "%web%"

      • Searching for all installed patches would be:

        • select * from ccm_updatestatus where status = 'installed'

Depending on your requirements / needs, have fun! Wbemtest is very useful for quick references to WMI to determine patch statuses whenever you are suspecting the integrity of data received from SCCM due to possible sync issues.

SQL Report with the systems Names and Architecture for specific collection

finding the systems Names and Architecture for specific collection

 

SELECT     dbo.v_Collection.CollectionID, dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_GS_COMPUTER_SYSTEM.SystemType0,
                      dbo.v_GS_COMPUTER_SYSTEM.UserName0, dbo.v_R_System.Operating_System_Name_and0
FROM         dbo.v_Collection INNER JOIN
                      dbo.v_FullCollectionMembership ON dbo.v_Collection.CollectionID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE     (dbo.v_Collection.CollectionID = 'CollectionID')

Required Patches based on collection ID

SELECT DISTINCT
                      TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code],
                      CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status', UI.BulletinID, UI.ArticleID, UI.Title,
                      dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionID
FROM         dbo.v_FullCollectionMembership INNER JOIN
                      dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
                      dbo.v_R_System AS SYS LEFT OUTER JOIN
                      dbo.v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN
                      dbo.v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID ON dbo.v_FullCollectionMembership.ResourceID = UCS.ResourceID
WHERE     (UCS.Status IN ('3', '2')) AND (dbo.v_Collection.CollectionID = 'CollectionID')
ORDER BY UI.ArticleID

sccm For Desktops only report

For Desktops only report

 

SELECT     TOP (100) PERCENT dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name],
                      dbo.v_R_System.User_Domain0 AS [Domain Name], dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer,
                      dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number],
                      dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type], dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM         dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '3') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '4') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '5') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '6') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '7') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '15') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '16')
ORDER BY [Computer Name]

SQL Report for only Laptop Computers

 

 

If your boss ask to get list of laptops which are managed by SMS or SCCM.what do you do and how do you get that. Right click on computer and go to resource explorer to identify the computer is Laptop or Desktop ?

You can identify if the computer is Laptop or Desktop based on its chassis Types.

Below are listed the Chassis types available to create SCCM collection or reports.

For Laptops Chassis Types : 8 , 9, 10, 11, 12, 14, 18, 21

For Desktop Chassis Type : 3, 4, 5, 6, 7, 15, 16

For server Chassis  Type: 23

 

 

 

 

 

 

 

 

SELECT     dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
                      dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                      dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
                      dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM         dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '8') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '9') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '10') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '11') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '12') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '14') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '18') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '21')

SCCM report for hardware specs of all desktops and laptops on the domain

 

 

 

---select * from v_GS_SYSTEM_ENCLOSURE

SELECT  distinct
CS.name0 as 'Computer Name',
CS.domain0 as 'Domain',
CS.UserName0 as 'User',
BIOS.SerialNumber0 as 'Bios serial',
SE.SerialNumber0 as 'System Enclosure serial',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'model',
OS.Caption0 as 'OS',
RAA.SMS_Assigned_Sites0 as 'Site',
RAM.TotalPhysicalMemory0 as 'Total Memory',
sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
CPU.MaxClockSpeed0 as 'Max CPU Speed',
CPU.Name0 as 'CPU Model',
CPU.Is64Bit0 as '64 Bit Compatible'
from 
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID 
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
LDisk.DriveType0 =3
group by
CS.Name0,
CS.domain0,
CS.Username0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.MaxClockSpeed0,
CPU.Name0,
CPU.Is64Bit0
ORDER BY CS.name0