It's probably more to do with your SQL config than with CM. Nevertheless, I re-wrote it anyway.
This took 19 seconds to run for me, against a collection with 100k boxes in it. took 8 seconds against a collection with 100 boxes in it. If the below still just runs/never finishes, you're going to want to look at your sql config. TempDB maybe. Or if your disks have icky IOPS. DECLARE @CollID as nvarchar(8) = (select collectionid from v_collection c where c.name = 'CollectionNameHere') ;with GetOUorCN as (select ou.resourceid, Case when ( max(ou.System_OU_Name0) <> NULL or max(ou.System_ou_name0) <> '' ) then MAX(ou.system_ou_name0) else max(scn.System_Container_Name0) end as [ou] from v_RA_System_SystemOUName ou join v_FullCollectionMembership fcm on fcm.resourceid=ou.resourceid left join v_RA_System_SystemContainerName scn on scn.resourceid=ou. resourceid where fcm.collectionid=@CollID group by ou.resourceid) select s1.name0, GetOUorCN.ou, s1.Client_Version0, s1. Operating_System_Name_and0, s1.Creation_Date0 ,csys.model0, s1.AD_Site_Name0, s1.Distinguished_Name0 ,s1.user_name0, u.Full_User_Name0, u.mail0, u.employeeNumber0, u.department0 , u.title0, @CollID as 'CollectionID' ,s1.Resource_Domain_OR_Workgr0, s1.active0, s1.client0 from v_r_system s1 join v_FullCollectionMembership fcm on fcm.resourceid=s1.resourceid left join GetOUorCN on GetOUorCN.resourceid=s1.resourceid left join v_gs_computer_system csys on csys.resourceid=s1.resourceid left join v_r_user u on u.user_name0=s1.user_name0 where fcm.collectionid=@CollID order by ou On Thu, Aug 31, 2017 at 10:11 AM, Burke, John <john.bu...@bellaliant.ca> wrote: > HI, > > > > This report works on smaller collections, but when I bump it up to all > systems (with 50000) systems it just runs for hours and hours and never > ends. It runs on collections with 7000 or so in them. > > > > Is there something busted in it? I basically want a row for every pc in > the collection no matter what. > > > > SELECT DISTINCT > > SYS.Name0, SYS.Client_Version0, > SYS.Operating_System_Name_and0, SYS.Creation_Date0, > v_GS_COMPUTER_SYSTEM.Model0, SYS.AD_Site_Name0, > > SYS.Distinguished_Name0 AS Expr1, > MAX(v_RA_System_SystemOUName.System_OU_Name0) AS SystemOUName, > SYS.User_Name0, > > v_R_User.Full_User_Name0, v_R_User.Mail0, > v_R_User.employeeID0, v_R_User.department0, v_R_User.title0, @CollID AS > CollectionID, > > SYS.Resource_Domain_OR_Workgr0, SYS.Active0, > SYS.Client0 > > FROM v_R_System AS SYS FULL OUTER JOIN > > v_R_User ON v_R_User.User_Name0 = SYS.User_Name0 > FULL OUTER JOIN > > v_GS_COMPUTER_SYSTEM ON > v_GS_COMPUTER_SYSTEM.ResourceID > = SYS.ResourceID INNER JOIN > > v_FullCollectionMembership AS fcm ON > SYS.ResourceID = fcm.ResourceID INNER JOIN > > v_RA_System_SystemOUName ON SYS.ResourceID = > v_RA_System_SystemOUName.ResourceID > > WHERE (fcm.CollectionID = @CollID) > > GROUP BY SYS.Name0, SYS.Client_Version0, SYS.Operating_System_Name_and0, > SYS.Creation_Date0, v_GS_COMPUTER_SYSTEM.Model0, SYS.User_Name0, > > v_R_User.Full_User_Name0, v_R_User.Mail0, > v_R_User.employeeID0, v_R_User.department0, v_R_User.title0, > v_R_User.Distinguished_Name0, > > v_R_User.manager0, SYS.AD_Site_Name0, > SYS.Distinguished_Name0, SYS.Resource_Domain_OR_Workgr0, SYS.Active0, > SYS.Client0 > > ORDER BY SYS.Name0 > > -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blog: http://mnscug.org/blogs/sherry-kissinger