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


Reply via email to