Issue with canned reports not reporting correctly-issue and fix

shawn.m · 05-23-2005, 07:42 PM

Long and for some experts you already know this, but if you are a medium user this will help you understand some of the functioning of the Altiris tables.

Had a issue with canned reports reporting lower numbers than when i created the report myself. Also had a group of computers that would not forward to my asset management server. I know that the computers are reporting to my notification server within 60 days.

Example would be app metering version report.

SELECT a.[Agent Name], a.[Product Version], COUNT(DISTINCT CAST(a.[_ResourceGuid] AS NVARCHAR(128))) AS '# Computers' FROM dbo.Inv_AeX_AC_Client_Agent a WHERE a.[Agent Name] = 'Application Metering' GROUP BY a.[Agent Name], a.[Product Version]

This reports look correct in the numbers in the field. However when you click the numbers to the drill down report you get a total of 10077 ( 10009 unique) machines. no date qualifiers

SELECT c.[Name], c.[Domain], a.[Agent Name], a.[Product Version], c.[Guid] S '_ResourceGuid' FROM dbo.Inv_AeX_AC_Client_Agent a JOIN dbo.vComputer c ON a.[_ResourceGuid] = c.[Guid] WHERE a.[Agent Name] = 'Application metering' AND a.[Product Version] = '%Product Version%'

The count of the computers does not match the previous numbers.

I wrote my own report to show user info along with version information, which shows 10418 (10360 unique) within 60 days reporting, or 11723 (11617 unique) all time. so the stock canned report is stripping out more machines than i believe it should when it is matched to vComputers.

SELECT DISTINCT T1.[Name] AS 'Name', T1.[Last Logon User] AS 'Last Logon User', T3.[Default User Name] AS 'Default User Name', T1.[Last Logon Domain] AS 'Last Logon Domain', T0.[Product Version] AS 'Product Version', T0.[Agent Name] AS 'Agent Name', T0.[Product Version] AS 'Product Version', T2.[Collection Time] AS 'Collection Time'

FROM [Inv_AeX_AC_Identification] T1 LEFT OUTER JOIN [Inv_AeX_AC_Inventory_Results] T2 ON T1.[_ResourceGuid] = T2.[_ResourceGuid] LEFT OUTER JOIN [Inv_AeX_AC_Client_Agent] T0 ON T1.[_ResourceGuid] = T0.[_ResourceGuid] LEFT OUTER JOIN [Inv_AeX_OS_Windows_NT] T3 ON T1.[_ResourceGuid] = T3.[_ResourceGuid] WHERE T0.[Agent Name] = 'Application Metering' AND T2.[Collection Time] >= (getdate()-60) ORDER BY T1.[Name] ASC

After troubleshooting it myself. I found out

if i take the canned report

SELECT c.[Name], c.[Domain], a.[Agent Name], a.[Product Version], c.[Guid] AS '_ResourceGuid' FROM dbo.Inv_AeX_AC_Client_Agent a JOIN dbo.vComputer c

ON a.[_ResourceGuid] = c.[Guid] WHERE a.[Agent Name] = 'Application Metering'

AND a.[Product Version] = '%Product Version%'

and substitute dbo.Inv_AeX_AC_Identification for V.Computer i get my 11945 rows again. hmm time to look at V.computer

when i match dbo.Inv_AeX_AC_Identification to V.Computer i get 23218 when i do a inner join, when i do a left outer join with AC identification as the master i get 25665 records. so i have 2447 records in AC Identification that are not in Vcomputer. When I do this by a filter of "Client Date" less than 60 days i get 13218 in ACIdentification reversed only 11000 records. so 2218 records in Ac Identify not in vComputer with a client date of 3/15/05 or greater.

however I do not have a date filter on my prior query so the 987 plus 230 should be valid machines in the query

OK VComputer has 23220 records in it, with a filter from VComputerResources

FROM dbo.[vComputerResource] r join [vItem] item on r.[Guid]=item.[Guid]

VComputerResource has 23229 records in it, with a filter from VActiveAsset, when i match the resource GUID's in AC Identification to VcomptuerResource i am still missing 2218 computers

VcomptuersResources has a filter on it that I do not understand however the numbers are still down. so that means the records being feed into this table from vActiveAsset are off.

ALTER VIEW dbo.vComputerResource AS select * from [vActiveAsset] r where r.[ResourceTypeGuid] in (select [ResourceTypeGuid] from [ResourceTypeHierarchy] where [BaseResourceTypeGuid]='493435F7-3B17-4C4C-B07F-C23E7AB7781F')

VActiveAsset has 23280 records in it. so we are still down from my 25665 number, with a filter from VResourceHierarchy. This is where my problem looks to reside.

VActiveAsset does have another filter that looks to be stripping out computers.

SELECT DISTINCT rth.[ResourceId], rth.[Guid], rth.[ResourceTypeGuid], rth.[IsManaged] FROM [vResourceHierarchy] rth LEFT OUTER JOIN ResourceAssociation resAssoc ON rth.Guid = resAssoc.ParentResourceGuid

AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL)

AND BaseResourceTypeGuid = 'B9EE0AB2-AE0E-4867-BF4C-41D4A382163B'

dbo.vResourceHierarachy has 106090 records in it. feed by dbo.ItemResource and dbo.vResourceTypeHierarchy

I can match all 13218 records from AC Identificaiton with no nulls in the dbo.vResourceHierarachy view so i am OK here. This means whatever the prior conditions on VActiveAsset are reduces my computer count.

Also I match dbo.Inv_AeX_AC_Identification to dbo.ItemResource and get 25665 records so my issue is not their

Resolution

I dug deeper and found the child resource table

dbo.vFixedAssetStatus

Which showed the resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' is "active"

if it is marked retired 492C463B-FA2-4DD6-AE73-6FD2C7B0E489

it will not post to the VComputer table.

Wrote a quick report to find all my retired machines, selected them, right click and change to "active" status. VOLIA! my reports are correct, plus inventory forwarding for this machines to my asset management server works, plus they can now be found in the computer collection once more.

select distinct
dbo.Inv_AeX_AC_Identification.[_ResourceGUID]AS 'GUID', dbo.ResourceAssociation.ParentResourceGuid, dbo.ResourceAssociation.ChildResourceGuid,
dbo.ResourceAssociation.[CreatedDate],
dbo.Inv_AeX_AC_Identification.[Name],
dbo.Inv_AeX_AC_Identification.[client date]

From dbo.ResourceAssociation
LEFT OUTER join dbo.Inv_AeX_AC_Identification
ON dbo.ResourceAssociation.[ParentResourceGuid]=dbo.Inv_AeX_AC_Identification.[_ResourceGUID]

WHERE dbo.ResourceAssociation.ChildResourceGuid = '492c463b-afa2-4dd6-ae73-6fd2c7b0e489' AND dbo.Inv_AeX_AC_Identification.[_ResourceGUID] IS NOT NULL

JeremyDallas · 05-23-2005, 09:51 PM

Ok, holy hell! That's a lot of hard work Shawn! Thanks for this OUTSTANDING information.

JAustgen · 05-23-2005, 10:03 PM

So is there a way we're processing tables? Or something wrong in the way we're processing reports? I'm a little hazy on the details.

shawn.m · 05-23-2005, 10:08 PM

the worst part of it to me was that Altiris Support should have been able to spot that the GUID matched retired machines after i sent them the first email

. It was at the developers when i finally figured it out after spending 20 hours staring at tables structure and dependences

on the processing tables part. First a comment on customer service and product support. the lack of public knowledge on the table structure for out of the box Altiris tables, queries and reports is horrendous. Especially when the customer service has no knowledge of back end structure for advanced users. I was told several times by customer support that they were no SQL experts or experts on reports. Someone darn well should be. Plus having a whole lot more white papers written for advanced user.

As to how Altiris is processing the tables to create reports, I think it is horrifically complex when it could be so much simpler. The core issue was that computers were marked in error retired by a incorrect rule on the console. Computers still reported to the notification server but no data was forwarded to the Asset management server. Also the computers dropped out of current collections as they were retired. Their should have been a data rule that if the computer reports to the server that it is moved out of retired and back to active. Plus this should have a automatic report to report this to the Admin that computers have reappeared in the field after being retired.

Out of the box reports have no error checking on the reports when they are run. Also the attributes and constraints on reports should be in easier to read hidden headers for developers to read. Instead of me having to guess on table structures and how they are being populated.

I have created my own troubleshooting folder where I can have 3 version of the same report to try to spot errors and “Nulls”. Many of the out of the box reports assume no Nulls on key values such as Serial number. We can be assured that their will be nulls in serial number. Anybody who has Compaq EN series computers knows this, older IBM desktops same plus if you have any system board replacements.

Nick · 05-26-2005, 11:54 PM

Outstanding information Shawn! I'm going to sticky this since I'm sure many will find this extremely useful.

JeremyDallas · 05-27-2005, 09:51 AM

Quote:

I have created my own troubleshooting folder where I can have 3 version of the same report to try to spot errors and “Nulls”. Many of the out of the box reports assume no Nulls on key values such as Serial number. We can be assured that their will be nulls in serial number. Anybody who has Compaq EN series computers knows this, older IBM desktops same plus if you have any system board replacements.

I couldn't agree more. I've had to completely change the way I write reports because you can't depend on inner joins. If you're not doing left and right outers on a bunch of the tables, you will definitely come up short on reports.

Ray · 05-29-2005, 04:48 PM

UG my head hurts now....