Question
Where can I find the queries that populate the Resource Summary page?

Answer

[Taken from the Resource Summary Page SQL Queries document created by the Altiries EMEA Support team.]

Overview:

               

The Resource manager offers some interesting summaries on its first tab, Collection Summary, Policies Summary, Resource Summary etc.

 

This document intends to provide the reader with ready to run SQL statement similar to the ones used in the Resource Explorer Resource Summary page by when the NS server prepares the process the page request. The resource summary page is located on “C:\Program Files\Altiris\Resource\Explorer” for the default install path. It is named BasicSummary.aspx and can be accessed from a web browser via the Resource Explorer or directly using the following syntax:

 http://<server_name>/Altiris/Resource/Explorer/BasicSummary.aspx?ItemGuid=<resource_guid>

 

This page in itself as a short html body, and most of the work is done in the background by the Altiris.Resource.ResourceManagerSummary class located in the Altiris.Resource assembly.

 

 

 

Note that a few SQL variables are used as listed below:

 

@Guid / @ResourceGuid: These two variables are used in the SQL to retrieve information related to the specified Guid (and thus machine).

 

@Month: This variable is used in the system to select the current month and display the Machine usage accordingly.

 

@CultureCode: This variable represent the Culture and is based on information retrieved by the CultureInfo.CurrentUICulture.Name method (Cultures are listed in the Altiris DB on the Culture table. ‘en-GB’, ‘en-US’ or ‘fr-FR’ are examples of valid Culture strings).


Code:

 

Method name: GetBasicPageGeneralSection

 

select [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 vResource r

JOIN [vItem] 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

 

 

select [Fully qualified name] = isnull( [Host Name] + N'.' , N'' ) + isnull( [Primary DNS Suffix] , N'' )

from vResource 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

 

select [OS Language] = c.LocalizedName, [Client Date]

from vResource 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

 

select [Primary User] = isnull( [a].[Domain] + N'\\' , N'' ) + isnull( [User] , N'' )

from vResource 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

 

select isnull( [StandardName] , N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_OS_Time_Zone a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

select isnull( N'.' + [Build] , N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_OS_Operating_System a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

Method: GetBasicPageIdentificationSection

 

select isnull( [Serial Number], N''), isnull( [Asset Tag], N'' ), [Guid] = @ResourceGuid

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_Serial_Number a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

SELECT Guid FROM vResource WHERE Guid=@ResourceGuid

 

 

Method: GetAltirisAgentDetailsSection

 

SELECT vi.CreatedDate

FROM vResource vr

INNER JOIN vItem vi ON vi.Guid = vr.Guid

WHERE vr.Guid=@Guid

 

SELECT MAX(StartTime) as MaxRequestTime

FROM Evt_NS_Client_Config_Request

WHERE ResourceGuid = @Guid"

 

SELECT MAX(w.ModifiedDate) as MaxModifiedDate

FROM ResourceUpdateSummary w

INNER JOIN vResource r ON w.ResourceGuid = r.Guid

WHERE r.Guid=@Guid

 

SELECT MAX(StartTime) as MaxStartTime

FROM Evt_NS_Event_History

WHERE ResourceGuid=@Guid

 

SELECT *

FROM Inv_AeX_AC_Client_Connectivity

WHERE _ResourceGuid=@Guid

 

SELECT ca.[Agent Name], ca.[Product Version]

FROM Inv_AeX_AC_Client_Agent ca

JOIN vResource r ON ca._ResourceGuid = r.Guid

WHERE r.Guid = @Guid

 

 

Method: GetBasicPageHardwareSection

 

select isnull( [System Manufacturer], N'' ) , isnull( [Computer Model], N'' ) , isnull( [System Model Number], N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_Serial_Number a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

select isnull( [Type], N''), isnull( [Speed], N'0'), isnull( [Number], N'0')

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_CPU a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

select [Total Physical Memory (MB)]

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_Memory a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

 

Method: GetBasicPageNetworkSection

 

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 vResource 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

 

select isnull( a.[Device Description], N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_PCI_Bus a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid\r\nwhere r.Guid = @ResourceGuid

and upper(a.[Device Description]) like upper('%Controller%')

 

Method name: GetBasicPageDrivesSection

 

select isnull( a.[Name], N''), isnull( [Description], N''), isnull( [Size in MBytes], N''), isnull( [Free Space in MBytes], N''), isnull( [File System], N''), isnull( [Volume Name], N'')

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_Logical_Drive a ON a._ResourceGuid = r.Guid

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid”

Note: Inventory for Windows 6.1.1075 uses the following query to populate hard drive information according to SQL trace logs. Note the difference in the table names.

select isnull( a.[Name], N''''), isnull( [Description], N''''), isnull( [Size in MBytes], N''''), isnull( [Free Space in MBytes], N''''),
isnull( [File System], N''''), isnull( [Volume Name], N'''')
        from vResource r
        LEFT OUTER JOIN Inv_AeX_HW_Logical_Disk 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

 

 

Method: GetFullPageMemoryModulesSection

 

select  isnull( a.[Total Physical Memory (MB)], N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_Memory a ON a._ResourceGuid = r.Guid  

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

select  isnull( a.[Socket], N'' ), isnull( a.[Size], N'' ) + N' ' + isnull( a.[Type], N'' )

from vResource r

LEFT OUTER JOIN  Inv_AeX_HW_Memory_Modules a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

and upper(a.Socket) not like upper('%Flash%')

 

 

Method: GetFullPagePCIDevicesSection

 

select isnull( a.[Device Description], N'')

from vResource r

LEFT OUTER JOIN Inv_AeX_HW_PCI_Bus a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

   and upper(a.[Device Description]) not like upper('%Controller%')

   and upper(a.[Device Description]) not like upper('%Bridge%')

 

 

Method: GetFullPageMicrosoftHotfixesSection

 

select isnull( a.[Description], N'')

        from vResource r

        LEFT OUTER JOIN Inv_AeX_OS+Quick_Fix_Engineering 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] <> ' '

 

 

Method: GetFullPageLicencedProgramsSection

 

select isnull( a.[Name], N''), isnull( a.[Product ID], N'')

from vResource r

LEFT OUTER JOIN Inv_AeX_OS_Add_Remove_Programs 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.[Product ID] <> ' '

and upper(a.[Product ID]) not like upper('%none%')

 

 

 

 

 

 

 

 

 

Extra SQL Statements from the ResourceManagerSummary:

 

Method: GetFullPageInstalledSoftwareSection

 

select isnull( a.[ProductName], N'') + N' ' + isnull( a.[ProductVersion], N'')

from vResource r

LEFT OUTER JOIN Inv_AeX_SW_Audit_Software a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

and upper(a.[ProductName]) not like upper('%null%')

order by a.[ProductName]

 

 

 

 

Method: GetFullPageEUSection

 

select  isnull( a.[Given Name], N'' ) + N' ' + isnull( a.[Surname], N'' ), isnull( a.[Title], N'' ), isnull( a.[Department] , N'' )

from vResource r

LEFT OUTER JOIN Inv_AeX_EU_Contact_Detail a ON a._ResourceGuid = r.Guid 

LEFT OUTER JOIN Inv_AeX_AC_Identification i on r.Guid = i._ResourceGuid

where r.Guid = @ResourceGuid

 

 

 

SELECT dbo.fncIsnullOrEmpty( i.[Name], vri.[Name] ) AS [DisplayName]

                    FROM [vResourceItem] vri

                    LEFT OUTER JOIN Inv_AeX_AC_Identification i

ON i.[_ResourceGuid] = vri.[Guid]

                    WHERE vri.[Guid] = @Guid

Imported Document ID: HOWTO1150
Legacy ID: 1618

Thanks for your feedback. Let us know if you have additional comments below. (requires login)

    © 1995-2020 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.