Symantec Management Platform (Notification Server)

Hey All,

I am having an issue adding an appropriate drilldown to a report I have managed to create.


Report:
SELECT T0.[Name] AS 'Product Name', T0.[Version] AS 'Version', COUNT(*) AS 'Total Installed'

FROM [Inv_AeX_OS_Add_Remove_Programs] T0
JOIN Inv_AeX_AC_Inventory_Results acir
ON T0._ResourceGuid = acir._ResourceGuid

WHERE DATEDIFF(DAY,acir.[Collection Time], GETDATE()) < 90
AND (T0.[Name] LIKE '%myEXTRA! Enterprise%'
OR T0.[Name] LIKE '%EXTRA! Personal Client%'
OR T0.[Name] LIKE '%EXTRA! for windows%'
OR T0.[Name] LIKE '%EXTRA! X-treme 8%' )
GROUP BY T0.[Name], T0.[Version]




I basically want to add a drilldown to the app versions it does return to tell me the computername and its IP address.

I found this and know it is wrongly formated for my report but not sure how to modify it to work with the above query
SELECT vc.Name, vc.[Ip Address] from
vComputer vc
join Inv_AeX_OS_Add_Remove_Programs arp on arp._ResourceGuid = vc.Guid
order by vc.Name


Any help would greatly be appreciated


Clay

Someone on another board gave me this as a quick response just off the top of their head.

select ag.[_ResourceGuid], i.[Name]
from Inv_AeX_AC_Client_Agent ag
join vComputer i on ag.[_ResourceGuid] = i.Guid
join Inv_AeX_AC_TCPIP tcp on ag._ResourceGuid = tcp._ResourceGuid
where [Product Name] = %Product Name% and [Product Version] = %Product Version%
order by i.[Name] asc


I am getting an error when ran as the drill down
" SQl error in query. Error: System.Data.SQLClient.SQLError: Line 9: Incorrect syntax near 'Product'.Sql CommandText: -- MAX ROWCOUNT etc..."


Any advice on formatting it to work for me.


Thanks in advance,

Clay

You need to add the table where product name is coming from, I believe.

Do i add it with a regular JOIN statement?

I thought it would know from where the drilldown cam from.



BTW - Does the advanced reporting class that Altiris has help people with stuff like this?


Clay

Not a super genius myself. However, if you're slow like me the class would help. Since you have [Product Name], that doesn't tell the drilldown where to look. At least that's my assumption.

Unless I'm crazy (which could very well be), that query you posted you got from the other forum isn't going to get you much because it doesn't even have a join to the Add\Remove Programs table. So, you've pretty much got the SQL to do your drilldown in your original post, just drop the Count and add a where clause for the product name and version and add in the variables, and a join into item or vcomputer to get active computers.

So try this for the sql:
SELECT i.Name as [Computername], T0.[Name] AS 'Product Name', T0.[Version] AS 'Version'
FROM [Inv_AeX_OS_Add_Remove_Programs] T0
JOIN Inv_AeX_AC_Inventory_Results acir
ON T0._ResourceGuid = acir._ResourceGuid
Join item i on i.guid = T0._resourceguid
Join itemresource ir on i.guid = ir.guid and ir.ismanaged = 1
WHERE DATEDIFF(DAY,acir.[Collection Time], GETDATE()) < 90
AND T0.[Name] = %ProductName%
AND T0.Version = %Version%

In these steps:
1) Change your original query at level 0 to the alias for product name to 'ProductName', so it starts like the following:
SELECT T0.[Name] AS 'ProductName', T0.[Version] AS 'Version', COUNT(*) AS 'Total Installed'
2) Add that SQL as a new query at level 1
2) On your level 0 query, Click the add drill downs button
3) In the configure drilldowns, pick Different Qeury Level from the Drill down to:, give it a name to display, choose query level 1
4) For Drilldown paramters, add the following:
ProductName|Version
5) I usually like to change the output to new window

Save it all and try it out. You should be able to click any line item in the report and see the breakout of the computers that make up the count.
HTH
Pat

Pat,
Appreciate the info on pointing me to the right direction.

I can now drilldown from the inital query to give me the computername.

Where in the second query could I add in a command to also pull me the computers IP address that resides in the Inv_AEX_AC_TCPIP or Computers table or some other table it can easly be extracted from.

I need to tell the higher ups where all the licenses are residing for my Attachmate products

Thanks Again,
Clay

Add a join to the TCPIP table and return [IP Address], like this:

SELECT i.Name as [Computername], t.[IP Address], T0.[Name] AS 'Product Name', T0.[Version] AS 'Version'
FROM [Inv_AeX_OS_Add_Remove_Programs] T0
JOIN Inv_AeX_AC_Inventory_Results acir
ON T0._ResourceGuid = acir._ResourceGuid
Join item i on i.guid = T0._resourceguid
Join itemresource ir on i.guid = ir.guid and ir.ismanaged = 1
Join Inv_AeX_AC_TCPIP t on i.guid = t._resourceguid
WHERE DATEDIFF(DAY,acir.[Collection Time], GETDATE()) < 90
AND T0.[Name] = %ProductName%
AND T0.Version = %Version%

Note though that some of your computers will double up in the output if they have multiple IP's, like for wireless, etc.

Pat,

Thanks a million for the help.

I owe you a beer.


Clay