Client Management Suite

Hi All,

I'm new in using Altiris and creating Reports with the Altiris 7 report builder.

I'd like to create a report with the following data:

Computername, Primary User, User Full Name, Last Login Date/Time, Computer Model, Serial Number

I created a new computer report and added the field AeX AC Primary User to get the Primary User field in but I've no idea how to add the other coulms.

I was also not able to find a HowTo resp. a guid how to use :(

Thx & RG,

T

Hi Thomas78,

I think that the following general steps can help you:

1 - Go to Reports area, and right click to New -> Report -> Computer Report. This will open the Report Builder with the Computer as resource type.

2 - Now you need to select the dataclasses that are under Computer resource type definitions. Then, select "Use Fields & Data Class Attributes".

3 - Select Multuple Fields, because for this report you'll need to select more than one dataclass.

To know which dataclass select, a tip is analyze the Resource Manager (Inventory and Events area) of a computer that you have sure that was inventoried.

Regards,

You may also find Steven Riley's report videos helpful as well:

Creating a basic report:
https://www-secure.symantec.com/connect/videos/creating-basic-report

Advanced topics
Creating a drop-down menu filter:
https://www-secure.symantec.com/connect/videos/symantec-management-platform-reports-creating-dropdown-menu-filter

Creating a drilldown:
https://www-secure.symantec.com/connect/videos/symantec-management-platform-reports-creating-drill-down-report

Does this help?

Here's specifically how you would create this query.  I learned a bit doing this.

First, go to Reports > All Reports, and then right-click and choose New > Report > Computer Report

The first thing to do when creating a report is add all the fields/columns you want to appear in your report.  Under the Fields tab, click Add, and then check the box for 'Add multiple fields,' then in the search field, search for keywords that are part of the table name or column name.

A quick word about this: let's say you're in Resource Manager and you expand Inventory > Software > BIOS Element, and within this in the right pane you see the Manufacturer property has a value of 'Dell Inc.'.  In the Report Builder (which is what you use by default if you create a new report), whenever you search, you'll see the data class table in the first set of brackets, and the column in the second set.  For example, [BIOS Element].[Manufacturer].  In SQL, however, you'd see something a little different: [Inv_SW_BIOS_Element] is the table name and [BIOS Element] is the column.

Search for and add the following columns as fields in your report by searching to restrict results, then selecting from the list the correct entry, then clicking Add.  Remember, you've confirmed you have the right column by looking in Resource Manager:

• primary user to select [AeX AC Primary User].[User]
• logon user to select [AeX AC Identification].[Last Logon User]
• client logon to select [AeX Client Logon].[Login Time]
• client logon to select [AeX Client Logon].[User]
• model to select [Logical Device].[Model]
• serial to select [AeX AC Identification].[Hardware Serial Number]

Click OK to add all of these fields to your report.  If you run it now, you will get very many results.  You need to filter what's returned.  I recommend clicking Save Changes right now so you can see how this behaved, and how you'll need to work further to restrict what's placed into the report.

If you clicked Save changes, click Edit to begin working with the report again.  Click the Filter Expressions tab to restrict the data being returned.  Here, we'll add a filter so that only results where the Logical Device's description equals 'AT/AT Compatible' will be returned.  This is a trick I use to only return computer results.  Maybe others do this a different way, but it works for me and I haven't seen expected results missing or unexpected results included.

Click Add Condition and choose AND, then in the first field, type the column you want to evaluate and restrict.  Type 'logical device' and then select '[Logical Device].[Description]'; in the second field, choose EQUALS, and in the third field, type 'AT/AT Compatible' (all of these are without quotes).

Click Save Changes and you'll see that the results no longer include odd logical devices (such as peripherals, monitors, etc), but you are returning all login times, not just the most recent one.

It's possible that the report builder offers a way to show only the most recent login times, but I wasn't aware of one.  So what I did is converted it to a SQL query and added a statement to only return logon times if 1) the event type was 'Logon' (logoff events are boring and don't show the user, plus you asked for login date/time), and 2) only return the result of the value of ID is the maximum value for that computer.  I did this by looking at the _ResourceGuid and ID fields.  If you have a SQL person on staff, this is when you would ask them for help.  Or ask the forums.  Or try it.  I opted for the third choice and just tried to figure it out.  Here's what I came up with:

While editing the report, click 'Convert report to a SQL query' -- you may want to clone the report first if you don't want to hose things up while learning.  Now that it's been created as a SQL report, you can modify the SQL directly.  The report builder doesn't really let you do that.

Under the Parameterised Query tab, you'll see the query created by Altiris based on what you selected in the report builder.  You'll see a section of JOIN statements.  Add this beautiful text at the end of the JOIN statements, above the WHERE statement:

      INNER JOIN (SELECT max(_id) as MaxID, _ResourceGuid FROM [Evt_AeX_Client_LogOn] GROUP BY _ResourceGuid) AS grouped_cl ON [dca3_AeX Client LogOn]._ResourceGuid=grouped_cl._ResourceGuid AND [dca3_AeX Client LogOn]._id=grouped_cl.MaxID

[dca3_AeX Client Logon] should match however Altiris chose to define [Evt_AeX_Client_Logon] within the SQL statement already.  This INNER JOIN statement further restricts your query to only the rows where the Event type is Logon and the ID value is the maximum value for that computer _ResourceGuid.

I hope this helps.  Give it a try and let me know how it works in your environment.

Some raw SQL I threw together...:-)

SELECT vi.Name AS [Computer Name]

      ,u.Domain + '\' + u.[User] AS [Primary User]

      ,ISNULL(CONVERT(VARCHAR,logon.LastLogin), 'N/A') AS [Last Logon DateTime]

      ,ld.Model AS [Computer Model]

      ,ld.Description

      ,i.[Hardware Serial Number]

FROM vRM_Computer_Item vi

JOIN Inv_AeX_AC_Identification i

   ON i._ResourceGuid = vi.Guid

JOIN Inv_AeX_AC_Primary_User u

   ON u._ResourceGuid = i._ResourceGuid

JOIN Inv_HW_Logical_Device ld

   ON ld._ResourceGuid = i._ResourceGuid

   AND LOWER(ld.[Description]) = 'at/at compatible'

OUTER APPLY (SELECT _ResourceGuid, MAX(_eventTime) AS LastLogin

           FROM Evt_AeX_Client_LogOn

           WHERE _ResourceGuid = vi.Guid

           AND LOWER(Event) = 'logon'

           GROUP BY _ResourceGuid) logon

Thank you guys!!!!! It's working fine!!!!

If you're showing the Event type in your report, all of them should say Logon.  If some say Logoff, add a WHERE portion to the INNER JOIN I provided:

      INNER JOIN (SELECT max(_id) as MaxID, _ResourceGuid FROM [Evt_AeX_Client_LogOn] GROUP BY _ResourceGuid) AS grouped_cl ON [dca3_AeX Client LogOn]._ResourceGuid=grouped_cl._ResourceGuid AND [dca3_AeX Client LogOn]._id=grouped_cl.MaxID

Becomes:

      INNER JOIN (SELECT max(_id) as MaxID, _ResourceGuid FROM [Evt_AeX_Client_LogOn] WHERE [Evt_AeX_Client_LogOn].[Event]='Logon' GROUP BY _ResourceGuid) AS grouped_cl ON [dca3_AeX Client LogOn]._ResourceGuid=grouped_cl._ResourceGuid AND [dca3_AeX Client LogOn]._id=grouped_cl.MaxID

If it's working fine, perhaps this is already specified elsewhere..

Hi All,

I'm new in using Altiris and creating Reports with the Altiris 7 report builder.

I'd like to create a report with the following data:

1. I am unbale get the complete motherboard details
2. How to create the mail alert for hardware changes
3. Product key for Ms and non MS products, but do have license for AMS
4. Harddisk capacity and details

The Inner Join you posted works well but I have some PCs that have not sent inventory. They are in the stock room but has been imported via the connector off of a spreadsheet. They are in NS (ns 7) only as a serial number (and of course has a GUID).

Basically, I would see PCs (in this case serial numbers) not in the Evt_AeX_Client_LogOn table as well as PCs with all the event logon stuff that you already posted about.

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT

[vri2_Computer].[Guid] AS [_ItemGuid],

[vri2_Computer].[Name] AS 'PC Name',

[dca5_AeX AC Identification].[Hardware Serial Number] AS 'Serial Number',

[dca5_AeX AC Identification].[Client Date] as 'Last Contact',

va.[Status],

[dca6_AeX AC Primary User].[User] AS 'Primary User',

[dca3_AeX Client LogOn].[User] AS 'Last LogOn-Off User',

ugd.[Display Name] AS 'Display Name of Last User',

[dca3_AeX Client LogOn].[Event],

[dca3_AeX Client LogOn].[Time] AS 'Event Time',

aci.[Location],

[dca4_HW Logical Device].[Model],

FROM

[vRM_Computer_Item] AS [vri2_Computer]

LEFT OUTER JOIN [Evt_AeX_Client_LogOn] AS [dca3_AeX Client LogOn]

ON ([vri2_Computer].[Guid] = [dca3_AeX Client LogOn].[_ResourceGuid])

LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca4_HW Logical Device]

ON ([vri2_Computer].[Guid] = [dca4_HW Logical Device].[_ResourceGuid])

LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca5_AeX AC Identification]

ON ([vri2_Computer].[Guid] = [dca5_AeX AC Identification].[_ResourceGuid])

LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca6_AeX AC Primary User]

ON ([vri2_Computer].[Guid] = [dca6_AeX AC Primary User].[_ResourceGuid])

LEFT JOIN dbo.vAsset va

ON va.[_ResourceGuid] = [vri2_Computer].[Guid]

LEFT JOIN dbo.vAllComputerInfo aci

ON aci.Guid = va._ResourceGuid

LEFT JOIN dbo.Inv_Global_Windows_Users gwu

ON [dca3_AeX Client LogOn].[User] = gwu.UserId

LEFT JOIN dbo.Inv_Global_User_General_Details ugd

ON ugd._ResourceGuid = gwu._ResourceGuid

INNER JOIN (SELECT max(_id) as MaxID, _ResourceGuid FROM [Evt_AeX_Client_LogOn] GROUP BY _ResourceGuid) AS grouped_cl ON [dca3_AeX Client LogOn]._ResourceGuid=grouped_cl._ResourceGuid AND [dca3_AeX Client LogOn]._id=grouped_cl.MaxID

WHERE

(

(

([dca4_HW Logical Device].[Description] = N'AT/AT Compatible')

)

AND DATEDIFF(hh, [dca5_AeX AC Identification].[Client Date], getDate()) > '120'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'HP%'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'Latitude%'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'Inspiron%'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'Power%'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'Optiplex 170L'

AND [dca4_HW Logical Device].[Model] NOT LIKE 'VMWare%'

--AND

--([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))

)

ORDER BY

--aci.[Location],

--[dca5_AeX AC Identification].[Client Date] DESC

[Serial Number]