Yes and no. Yes because reports in SSRS can certainly timeout. No to that article and its details as it was specific to the reporting in ConfigMgr 2007 pre-SSRS.
J From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Burke, John Sent: Friday, September 1, 2017 10:07 AM To: mssms@lists.myitforum.com Subject: [mssms] RE: Report clocking. Used to work in 2007 but not in CB Besides the joins possibly - could this sort of thing apply to CB sQL reporting ? https://nikifoster.wordpress.com/2011/01/31/sccm-reporting-the-10000-limit-and-report-timeouts/#comment-1045 From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com] On Behalf Of Burke, John Sent: Friday, September 01, 2017 11:34 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] RE: Report clocking. Used to work in 2007 but not in CB I did add the User attributes to discovery for "manager, employeeID, department and title". I noticed you commented them out. Safe to enable them again? Getting employee info is super important. When using Inner Joins - I don't always get a ROW entry for every pc. I need to make sure I always get a row entry, even if that "extra" info is empty. If I change it to inner joins will it still give me a row entry for every item in the collection? Thoughts on why it works for smaller number collections? Didn't upgrade SQL recently. From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com] On Behalf Of Garth Jones Sent: Thursday, August 31, 2017 7:17 PM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] RE: Report clocking. Used to work in 2007 but not in CB This query will work in CMCB but only if you added the user discovery items to it, PLUS you really need to change the Full outer joins to inner joins. Did you upgrade SQL Server version recently? I used one of the Query formatter https://www.enhansoft.com/blog/query-formatting-tools to make it more readable. I quickly fixed up a number of things. On my slow VM with 20,000 computers, this took just over 4 minutes to run. SELECT DISTINCT R.Name0 , R.Client_Version0 , R.Operating_System_Name_and0 , R.Creation_Date0 , CS.Model0 , R.AD_Site_Name0 , R.Distinguished_Name0 AS Expr1 , MAX(OU.System_OU_Name0) AS SystemOUName , R.User_Name0 , U.Full_User_Name0 , U.Mail0 , -- U.employeeID0 , -- U.department0 , -- U.title0 , @CollID AS CollectionID , R.Resource_Domain_OR_Workgr0 , R.Active0, R.Client0 FROM dbo.v_R_System AS R INNER JOIN dbo.v_R_User as U ON U.User_Name0 = R.User_Name0 INNER JOIN dbo.v_GS_COMPUTER_SYSTEM as CS ON R.ResourceID = CS.ResourceID INNER JOIN dbo.v_FullCollectionMembership AS fcm ON R.ResourceID = fcm.ResourceID and fcm.CollectionID = @CollID INNER JOIN dbo.v_RA_System_SystemOUName as OU ON R.ResourceID = OU.ResourceID GROUP BY R.Name0 , R.Client_Version0 , R.Operating_System_Name_and0 , R.Creation_Date0 , CS.Model0 , R.User_Name0 , U.Full_User_Name0 , U.Mail0 , -- U.employeeID0 , -- U.department0 , -- U.title0 , U.Distinguished_Name0 , R.AD_Site_Name0 , R.Distinguished_Name0 , R.Resource_Domain_OR_Workgr0 , R.Active0 , R.Client0 ORDER BY R.Name0 Garth Jones Chief Architect See us at Microsoft Ignite in booth #1246!<https://www.enhansoft.com/conferences/microsoft-ignite-2017> From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com] On Behalf Of Burke, John Sent: Thursday, August 31, 2017 11:11 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] Report clocking. Used to work in 2007 but not in CB 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
