Reports

[Kristopher Rowe]

Hello,  

I am new to the community, and appreciate any help you can give me. I have been tasked with creating a custom report, and I am not verst at all in SQL. Below is the base of report and what needs to be added to it.  I have a base report that can filter computers and details by location and I need to add a secondary filter for computers with the cloud connector disabled. Any help in creating this report would be very helpful.

Base Report

DECLARE @v1_Loc nvarchar(max)
SET @v1_Loc = N'%Loc%'


SELECT

a.[Name],

MAX(h.[_eventTime]) AS 'Last Agent Config Request',

i.[department],
ol.Location,
ol.[LOCATION HIERARCHY],
ol.OU,

c.[User] as 'Primary User', a.[OS Name], a.[Hardware Serial Number] as 'AC Serial Number',

b.[Model], b.[Manufacturer],

c.[IP Address], c.[MAC Address]

FROM Inv_AeX_AC_Identification a

LEFT OUTER JOIN vAsset b ON a.[_ResourceGuid] = b.[_ResourceGuid]

LEFT OUTER JOIN vComputer c ON a.[_ResourceGuid] = c.[Guid]

LEFT OUTER JOIN Inv_HW_Computer_System d ON a.[_ResourceGuid] = d.[_ResourceGuid]

LEFT OUTER JOIN Inv_HW_Processor e ON a.[_ResourceGuid] = e.[_ResourceGuid]

LEFT OUTER JOIN vResourceEx f ON a.[_ResourceGuid] = f.[GUID]

LEFT OUTER JOIN Evt_NS_Client_Config_Request h ON a.[_ResourceGuid] = h.[ResourceGuid]

LEFT OUTER JOIN vAssetDepartmentOwner i ON a.[_ResourceGuid] = i.[_AssetGuid]

LEFT OUTER JOIN vHWComputerSystem s ON s.[_ResourceGuid] = c.[Guid]




Left join Oak_Location ol on ol.Guid = c.Guid

WHERE ol.[LOCATION HIERARCHY] like '%Loc%'
GROUP BY a.[Name], c.[User], a.[OS Name], a.[Hardware Serial Number], b.[Serial Number],

b.[Model], b.[Manufacturer],

s.[Model], s.[Manufacturer],

c.[IP Address], c.[MAC Address],

d.[Total Physical Memory (Bytes)], e.[Max Clock Speed (Mega-hertz)], i.[department],ol.Location,
ol.[LOCATION HIERARCHY],
ol.OU

ORDER BY Name




Cloud Connector
info
SELECT DISTINCT vc.Guid FROM vComputerResource vc
INNER JOIN Inv_AeX_AC_Network_Zone nz
on vc.Guid = nz._ResourceGuid
where nz.InternetModeSupported =0
AND nz.AppliedPolicyCount >= 0

Thank you 

Kris 

[Igor Perevozchikov]

Hi Kristopher Rowe!

Try this query

DECLARE @v1_Loc nvarchar(max)
SET @v1_Loc = N'%Loc%'
DECLARE @CollectionGuid UNIQUEIDENTIFIER
SET @CollectionGuid = '%Collection%'

SELECT
a.[Name],
MAX(h.[_eventTime]) AS 'Last Agent Config Request',
i.[department],
ol.Location,
ol.[LOCATION HIERARCHY],
ol.OU,

c.[User] as 'Primary User', a.[OS Name], a.[Hardware Serial Number] as 'AC Serial Number',
b.[Model], b.[Manufacturer],
c.[IP Address], c.[MAC Address]

FROM Inv_AeX_AC_Identification a

LEFT OUTER JOIN vAsset b ON a.[_ResourceGuid] = b.[_ResourceGuid]
LEFT OUTER JOIN vComputer c ON a.[_ResourceGuid] = c.[Guid]
LEFT OUTER JOIN Inv_HW_Computer_System d ON a.[_ResourceGuid] = d.[_ResourceGuid]
LEFT OUTER JOIN Inv_HW_Processor e ON a.[_ResourceGuid] = e.[_ResourceGuid]
LEFT OUTER JOIN vResourceEx f ON a.[_ResourceGuid] = f.[GUID]
LEFT OUTER JOIN Evt_NS_Client_Config_Request h ON a.[_ResourceGuid] = h.[ResourceGuid]
LEFT OUTER JOIN vAssetDepartmentOwner i ON a.[_ResourceGuid] = i.[_AssetGuid]
LEFT OUTER JOIN vHWComputerSystem s ON s.[_ResourceGuid] = c.[Guid]
LEFT JOIN Oak_Location ol on ol.Guid = c.Guid

WHERE ol.[LOCATION HIERARCHY] like '%Loc%'
AND 
c.[Guid] in (select distinct cm.[ResourceGuid] from dbo.CollectionMembership cm where cm.[CollectionGuid]=@CollectionGuid)

GROUP BY a.[Name], c.[User], a.[OS Name], a.[Hardware Serial Number], b.[Serial Number],

b.[Model], b.[Manufacturer],
s.[Model], s.[Manufacturer],
c.[IP Address], c.[MAC Address],
d.[Total Physical Memory (Bytes)], e.[Max Clock Speed (Mega-hertz)], i.[department],ol.Location,
ol.[LOCATION HIERARCHY],
ol.OU

ORDER BY Name

Before your report saving, you need to add new Query & Report parameters like on pic below


Then after saving this report, you will have option to choose any required filter for this report

Best regards,
IP.