SQL Queries from the Resource Summary page
HOWTO1150
Last Updated November 22, 2006
readonly
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.
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
Subscribing will provide email updates when this Article is updated. Login is required.
Subscribed to the Article.
Unable to subscribe
Thanks for your additional feedback !!!
Enterprise Support Virtual Agent
Rate Me :
Tell us more:
Welcome! My name is Sami, the Enterprise Support Virtual Agent answering technical support questions.
Start Over
This will clear the history and restart the chat.
Thanks for your feedback. Let us know if you have additional comments below. (requires login)