locked
Does anyone have a report showing which SCCM clients have migrated to a new 2012 infrastructure, and which ones remain on 2007 in an environment? RRS feed

  • General discussion

  • I was going to build one if I'm not able to adapt a previously built one. I haven't seen any on the Internet yet but perhaps I missed it. Thanks!
    Friday, July 26, 2013 9:01 PM

All replies

  • This cannot be done using CM07 because it has no knowledge of clients that were migrated. There's no difference between "client is offline" and "client was migrated to CM12".
    CM12 does not know that a client was reporting to a CM07 site before it was assigned to CM12.
    You could set up a linked server and query both databases though.

    Torsten Meringer | http://www.mssccmfaq.de

    Friday, July 26, 2013 9:14 PM
  • Querying both databases was along the lines of what I was thinking. In simple terms, it would query CM07 and CM12, and if found in CM12, would indicate 'Yes' in a column called "Migrated." So, just curious if anyone had created something like that before.
    Monday, July 29, 2013 6:57 PM
  • Sure. You just have to create a linked server in SQL, then you can run distributed queries (i.e. remote-sql-server.database.dbo.v_XYZ)

    Torsten Meringer | http://www.mssccmfaq.de

    Monday, July 29, 2013 8:50 PM
  • Here's the query that works:

    Select Distinct SYS12.Netbios_Name0 as 'ComputerName', ISNULL(SYS12.User_Name0, '') as 'Username', ISNULL(SYS12.AD_Site_Name0, '') as 'AD_Site', ISNULL(OS12.Caption0,'') as 'OperatingSystem', ISNULL(OS12.CSDVersion0,'') as 'ServicePack', ISNULL(COM12.Manufacturer0, '') as 'Manufacturer', ISNULL(COM12.Model0, '') as 'Model', ISNULL(ENC12.SerialNumber0, '') as 'SerialNumber'
    FROM CAS.dbo.v_R_System_Valid SYS12
    inner join CAS.dbo.v_GS_OPERATING_SYSTEM OS12 on SYS12.ResourceID = OS12.ResourceID 
    left outer Join CAS.dbo.v_GS_COMPUTER_SYSTEM COM12 on SYS12.ResourceID = COM12.ResourceID
    left outer Join CAS.dbo.v_GS_SYSTEM_ENCLOSURE ENC12 on SYS12.ResourceID = ENC12.ResourceID
    WHERE EXISTS
    (
    Select Distinct SYS07.Netbios_Name0, ISNULL(SYS07.User_Name0, ''), ISNULL(SYS07.AD_Site_Name0, ''), ISNULL(OS07.Caption0, ''), ISNULL(COM07.Manufacturer0, ''), ISNULL(COM07.Model0, ''), ISNULL(ENC07.SerialNumber0, '')
    FROM SERVERNAME.SMS.dbo.v_R_System_Valid SYS07
    inner join SERVERNAME.SMS.dbo.v_GS_OPERATING_SYSTEM OS07 on SYS07.ResourceID = OS07.ResourceID
    left outer Join SERVERNAME.SMS.dbo.v_GS_COMPUTER_SYSTEM COM07 on SYS07.ResourceID = COM07.ResourceID
    left outer Join SERVERNAME.SMS.dbo.v_GS_SYSTEM_ENCLOSURE ENC07 on SYS07.ResourceID = ENC07.ResourceID
    where SYS12.Netbios_Name0 = SYS07.Netbios_Name0
    )

    Change the SERVERNAME and database names to your environment names. The first part of the query is the CM12 database, and the second part is the CM07 database. Then created a linked server (if necessary) in your CM12 database server to your CM07 database server.


    • Edited by skissel Thursday, October 10, 2013 4:48 PM
    Thursday, October 10, 2013 4:47 PM