Reports are not opening in Asset management tool 8.5 version.

[Shiv Choudhary]

Reports are not opening in Asset management tool 8.5 version.

We are facing issue while running the below attached report query & getting error The report execution time exceeded server timeout. Please refresh this page to retry. If the problem continues please contact your administrator. 

We are running custom inventory report (It is same like computer summary report that comes Out of Box, only location is added in it which changes according to roles) which gives us result based on location.

We've created a location hierarchy and granted access to reports and other menu items in the tool based on those roles.

Earlier this report was working fine but don't know why suddenly stopped working when we didn't make any changes.

When we ran this query in SQL Management studio getting below attached error .

 Plz help us as we need to resolve this at earliest, Report of Inventory Data with Speed attached with this thread.

 

------------------------------
Shiv Choudhary
India
------------------------------

Attachment(s)

Report of Inventory Data with Speed.txt   2 KB 1 version

[Igor Perevozchikov]

Hi Shiv Choudhary!

Query failed in SQL management studio,  because there was no GUID specified for "SET @v1_ScopeCollectionGuid" and also for v2_TrusteeScope.

Better way is to "Edit" existing report in SMP Console > go to "Resolved Query" tab and copy Query, then you can use it in SQL management studio.

DECLARE @v1_ScopeCollectionGuid uniqueidentifier
SET @v1_ScopeCollectionGuid = '46d7be18-e223-8432-85f0-8e5f17e1d8f7'
DECLARE @v2_TrusteeScope nvarchar(max)
SET @v2_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{344CB9EA-BFA6-46A7-8ABF-8F521079FF74},{53D1AA80-2869-4DC4-B7DC-775F7332100E},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{6BA1530B-61A5-4A2C-A089-F8A67F431F3B},{7B7FA5A9-1E62-46CC-8BB6-A09693AF7C9D},{9CC6F8AF-AE76-4B56-9E38-E5068962305A},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{E6CDDA84-9F0A-41F5-B6B0-FADFE06E8CDE}'
DECLARE @g6_Location uniqueidentifier
SET @g6_Location = '05de450f-39ea-4aae-8c5f-77817889c27c'
SELECT DISTINCT
i.Guid,
i.Name,
i.Domain,
i.[IP Address],
i.[MAC Address],
i.[System Type] [OS BIT],
sn1.Manufacturer [Manufacturer],
sn1.[Model] [Computer Model],
cpu.[Max Clock Speed (Mega-hertz)] [CPU Frequency],
cpu.[Family] [CPU Type],
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],
m1.[Speed (Mega-hertz)],
i.[OS Name],
d.[OS Version] [OS Version],
CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)],
ld.[Free Space (Bytes)] [Free Space(MB)],
sn1.[Identifying Number] [Serial Number],
[vri8_Location].[Name] AS [Location],
[vri8_Location].[State]

FROM dbo.vComputer i
JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
LEFT JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
LEFT JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
LEFT JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.Inv_HW_Processor cpu
ON cpu.[_ResourceGuid] = i.[Guid]
LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
ON i.Guid = ld._ResourceGuid
LEFT JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.Guid
LEFT JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)],
_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid)CST
ON CST._ResourceGuid = i.Guid
LEFT JOIN Inv_AeX_AC_TCPIP t on t._ResourceGuid = i.Guid

LEFT OUTER JOIN ([ResourceAssociation] AS [ra7_Location]
INNER JOIN [vLocation] AS [vri8_Location]
ON ([ra7_Location].[ChildResourceGuid] = [vri8_Location]._ResourceGuid)
AND
(
(
([ra7_Location].[ResourceAssociationTypeGuid] = @g6_Location)
)
))
ON (i.[Guid] = [ra7_Location].[ParentResourceGuid])
WHERE
(
([i].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollectionsByScope(@v2_TrusteeScope, @v1_ScopeCollectionGuid, 1))))
)

Attached "Report of Inventory Data with Speed (v2).xml"

Best regards,
IP.

------------------------------
[JobTitle]
[CompanyName]
[State]
------------------------------

Attachment(s)

Report of Inventory Data with Speed (v2).xml   12 KB 1 version

[Shiv Choudhary]

Hi Igor , 

Thanks for reply please see below screen shot. We have used the query suggested by you still getting the same error which we were getting earlier. When I clicked on validate, it got validated successfully but when we run the report we are getting error.


We also ran the query in SQL but it took around 2 hours to run.

------------------------------
Shiv Choudhary
India
------------------------------

[Igor Perevozchikov]

Hi Shiv Choudhary!

"Report of Inventory Data with Speed (v2)" report this is same report as you have original "Report of Inventory Data with Speed" report and this is custom created report (not by me) and is not a default report provided from installed ITMS solutions, etc.

Use original "Report of Inventory Data with Speed" report and better to ask author of original "Report of Inventory Data with Speed" report to optimize it to make it run faster.

FYI

To avoid report auto-run once it is opened, you can change existing core setting, so anyone who will open any report in SMP Console, report will not be auto executed, only manual "Run Now" click will start report execution.

Need to change "SuppressReportAutorun" from 0 to 1

Best regards,
IP.

------------------------------
[JobTitle]
[CompanyName]
[State]
------------------------------

[Shiv Choudhary]

Hi Igor , 

We ran the SQL query again and it completed in about 2 hours. Why did it take so long for it to refresh?

Plz let us know if you have any idea so that we could work on it and resolve it.
As I have highlighted the Location and State only these two fields mainly we want with OOB Computer Summary Report. Actually our requirement is to have an Inventory report wrt. to location  .Thanks!!

------------------------------
Shiv Choudhary
India
------------------------------

[Roy B]

Hello Shiv,

If the database seems to be getting slower (seen in the Console and by running large reports like this one), it's always a good idea to check the Index fragmentation and statistics have been updated recently as part of a daily Maintenance Plan.  

My suggestion: Execute the query (.sql file) attached to KB 158633 and after it completes then run the report again and see if it ran any faster.  If it is faster, I would set up that query as specified in the KB and run it nightly before 2am or after 4am (a lot of SMP processes run between 2am and 4am local time).  This would replace any existing Maintenance Plan setup in SQL.

Best Regards,
Roy

[Jesse Gonzales]

Hi;

One of the first things to check is to see if your licenses are up to date if nothing has changed from the day it worked to the day it didn't. 

This is just a simple sanity check.  This can be checked in the SIM on your Notification Server.