ITMS 7.x, 8.x

Following are the raw sql statements taken from a SQL Trace while populating the Resoruce Summary view for a linux computer.

Notification Server 7.x, 8.x

Example Guid of the linux client computer: F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F

IF EXISTS(SELECT TOP 1 Guid FROM vRM_User_Item WHERE Guid = @ResourceGuid) SELECT 1 ELSE SELECT 0 ',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select TOP 1 [Resource name] = isnull( os.[Name], item.[Name]), [Domain] = isnull( os.[Domain], N''''), [Site] = isnull( s.[Name], N''''), [Last Logged On User] = isnull( [Last Logon Domain] + N''\'', N'''' ) + isnull( [Last Logon User], N'''' ), CASE when ( [OS Revision] IS NOT NULL AND [OS Revision] <> N'''' ) THEN isnull( [OS Name], N'''' ) + N'' '' + isnull( [OS Version] , N'''' ) + N'' '' + isnull( [OS Type], N'''' ) + N'' ('' + [OS Revision] + N'')'' ELSE isnull( [OS Name], N'''' ) + N'' '' + isnull( [OS Version] , N'''' ) + N'' '' + isnull( [OS Type], N'''' ) END as [Operating System] from vRM_Network_Resource r JOIN [vRM_Network_Resource_Item] item on item.[Guid] = r.[Guid] LEFT OUTER JOIN Inv_AeX_AC_Identification os on os.[_ResourceGuid] = r.[Guid] LEFT OUTER JOIN [vSource] s on item.[OwnerNSGuid]=s.[Guid] where r.Guid = @ResourceGuid ',N'@ResourceGuid uniqueidentifier,@CultureCode nvarchar(16)',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F',@CultureCode=N'en-US'
select TOP 1 [Fully qualified name] = isnull( [Host Name] + N''.'' , N'''' ) + isnull( [Primary DNS Suffix] , N'''' ) from vRM_Network_Resource r LEFT OUTER JOIN Inv_AeX_AC_TCPIP a ON a._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on i._ResourceGuid = r.Guid where r.Guid = @ResourceGuid ',N'@ResourceGuid uniqueidentifier,@CultureCode nvarchar(16)',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F',@CultureCode=N'en-US'
select TOP 1 [OS Language] = c.LocalizedName, [Client Date] from vRM_Network_Resource r LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid left join vCultureLocalizedName c on c.PrimaryLanguageId=i.[Install Primary Language] and c.SubLanguageId=i.[Install Sub Language] and c.LocalizingCulture = @CultureCode where r.Guid = @ResourceGuid ',N'@ResourceGuid uniqueidentifier,@CultureCode nvarchar(16)',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F',@CultureCode=N'en-US'
select TOP 1 [Primary User] = isnull( [a].[Domain] + N''\'' , N'''' ) + isnull( [User] , N'''' ) from vRM_Network_Resource r LEFT OUTER JOIN Inv_AeX_AC_Primary_User a ON a._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid where r.Guid = @ResourceGuid and a.Month = @Month ',N'@ResourceGuid uniqueidentifier,@CultureCode nvarchar(16),@Month varchar(8)',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F',@CultureCode=N'en-US',@Month='February'
select TOP 1 isnull( [Standard Name] , N'''' ) from vRM_Network_Resource r LEFT OUTER JOIN Inv_OS_Timezone a ON a._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid where r.Guid = @ResourceGuid ',N'@ResourceGuid uniqueidentifier,@CultureCode nvarchar(16),@Month varchar(8)',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F',@CultureCode=N'en-US',@Month='February'
IF EXISTS(SELECT TOP 1 Guid FROM vRM_User_Item WHERE Guid = @ResourceGuid) SELECT 1 ELSE SELECT 0 ',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select isnull( a.[Identifying Number], N'''') as [Identifying Number], isnull( a.[Device ID], N'''' ) as [Device ID] from vHWComputerSystem a where a._ResourceGuid = @ResourceGuid',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT TOP 1 vi.CreatedDate FROM vRM_Network_Resource vr INNER JOIN vRM_Network_Resource_Item vi ON vi.Guid = vr.Guid WHERE vr.Guid=@Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT MAX(StartTime) as MaxRequestTime FROM Evt_NS_Client_Config_Request WHERE ResourceGuid = @Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT MAX(w.ModifiedDate) as MaxModifiedDate FROM ResourceUpdateSummary w INNER JOIN vRM_Network_Resource r ON w.ResourceGuid = r.Guid WHERE r.Guid=@Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT MAX(StartTime) as MaxStartTime FROM Evt_NS_Event_History WHERE ResourceGuid=@Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT TOP 1 * FROM Inv_AeX_AC_Client_Connectivity WHERE _ResourceGuid=@Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT ca.[Agent Name], ca.[Product Version] FROM Inv_AeX_AC_Client_Agent ca JOIN vRM_Network_Resource r ON ca._ResourceGuid = r.Guid WHERE r.Guid = @Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
SELECT ca.[Agent Name], ca.[Product Version] FROM Inv_AeX_AC_Client_Agent ca JOIN vRM_Network_Resource r ON ca._ResourceGuid = r.Guid WHERE r.Guid = @Guid ',N'@Guid uniqueidentifier',@Guid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select isnull(p1.[Model], '''') as [Type], p1.[Max Clock Speed (Mega-hertz)] as [Speed], cs.[Number Of Processors] as [Number], cs.[Manufacturer], cs.[Model], cs.[Device ID], cs.[Number Of Processors] [Processor count], cs.[Total Physical Memory (Bytes)] as [RAM] from vHWProcessor p1 inner join vHWComputerSystem cs on p1._ResourceGuid = cs._ResourceGuid and cs._ResourceGuid = @ResourceGuid',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select [Device], [MAC Address], [IP Address], [DHCPEnabled], [Subnet Mask], [Subnet], [Default Gateway], [DNS Server 1], [DNS Server 2], [DNS Server 3], [Primary WINS Server], [Secondary WINS Server] from vRM_Network_Resource r LEFT OUTER JOIN Inv_AeX_AC_TCPIP a ON a._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid where r.Guid = @ResourceGuid union select [Device], [MAC Address], [IP Address], [DHCPEnabled] = 0, [Subnet Mask] = '''', [Subnet] = '''', [Default Gateway] = '''', [DNS Server 1], [DNS Server 2], [DNS Server 3], [Primary WINS Server] = '''', [Secondary WINS Server] = '''' from vRM_Network_Resource r RIGHT OUTER JOIN Inv_AeX_AC_TCPIPv6 ipv6 ON ipv6._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid where r.Guid = @ResourceGuid',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select [Name], [Description], [Size (Bytes)], [Free Space (Bytes)], [File System] from vHWLogicalDisk where _ResourceGuid=@ResourceGuid',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select isnull( a.[Description], N'''') from vResource r LEFT OUTER JOIN Inv_SW_Patch_Windows a ON a._ResourceGuid = r.Guid LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid where r.Guid = @ResourceGuid and a.[Description] <> '' ''',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'
select isnull(a.[DisplayName], '''') as [Display Name], (''v'' + isnull(b.[Version], '''')) as [Version] from Inv_AddRemoveProgram a inner join vInstalledItem i on a._ResourceGuid = i.Guid and a._ResourceGuid = @ResourceGuid and a.Hidden = 0 left join Inv_Software_Component b on a._SoftwareComponentGuid = b._ResourceGuid order by [Display Name]',N'@ResourceGuid uniqueidentifier',@ResourceGuid='F9CB265D-C026-4BD3-BAA9-50D8BF5D3E4F'