May 25, 2017 at 5:38 am
Hey,
I've created a report to list the machines that are not encrypted. Every time I try to run it, it just times out. I don't get any errors in Report Builder. Can anyone spot what I must be missing?
select distinct v_R_System.Netbios_Name0 as 'Computer Name',
v_R_System.User_Name0 as 'User Name',
MAX(v_RA_System_SystemOUName.System_OU_Name0) as 'OU Name',
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
v_GS_OPERATING_SYSTEM.CSDVersion0 as 'Revision',
v_GS_COMPUTER_SYSTEM.Model0 as 'Model',
v_GS_WORKSTATION_STATUS.LastHWScan as 'Last Hardware Scan'
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
inner join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
inner join v_RA_System_SystemOUName on v_RA_System_SystemOUName.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
left join v_FullCollectionMembership_Valid on v_FullCollectionMembership_Valid.resourceid = v_R_System.ResourceId
where v_R_System.ResourceId in (select ResourceID from v_FullCollectionMembership_Valid where CollectionID = @CollectionID) and v_R_System.Netbios_Name0 not in (select distinct v_R_System.Netbios_Name0 from v_R_System
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%Pointsec PC%' or v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%Check Point Endpoint Security - Full Disk Encryption%' or v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%Check Point Endpoint Security%' or v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%McAfee Drive%')
group by v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0,
v_GS_OPERATING_SYSTEM.CSDVersion0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_WORKSTATION_STATUS.LastHWScan
order by
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_GS_OPERATING_SYSTEM.Caption0,
v_GS_OPERATING_SYSTEM.CSDVersion0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_WORKSTATION_STATUS.LastHWScan
May 25, 2017 at 6:08 am
Just providing your query isn't going to tell us much. To list a "few" reasons it could be:
How long does the query take to run in SSMS; if a long time can you post a query plan? Is the server under a lot of load? Are you running the report locally or on the SSRS web portal?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 30, 2017 at 11:24 am
Thom A - Thursday, May 25, 2017 6:08 AMJust providing your query isn't going to tell us much. To list a "few" reasons it could be:
- Your query is slow
- The server is "busy" when you're trying to run the report
- The report contains a high amount of expressions and formatting, which slows down the rendering process
- You are running the report locally and your PC doesn't have a lot of resources available
How long does the query take to run in SSMS; if a long time can you post a query plan? Is the server under a lot of load? Are you running the report locally or on the SSRS web portal?
To add to the list...
Your report server is functioning as a "catch all server" with other programs besides SQL Server (a print server, a file server, etc.) that are grabbing bandwidth from SQL.
The server NIC card could be experiencing issues (or maybe the network cord isn't plugged all the way in, which actually happened to me).
Have you run both PerfMon traces and server-side Profiler traces (not Profiler itself) to identify points of contention yet?
May 30, 2017 at 12:10 pm
Just guessing here, but it would seem that all of your data is coming from views. Without any knowledge of the underlying sources for each view, I would take a guess that none of the views are indexed.
Your where clauses prevent the use of indexes.
Your 2nd where clauses below is fully covered by the 3rd one
WHERE v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Pointsec PC%'
OR v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Check Point Endpoint Security - Full Disk Encryption%' -- This where is covered by the next one
OR v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Check Point Endpoint Security%'
OR v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%McAfee Drive%'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply