Symantec Management Platform (Notification Server)

We are in the process of upgrading from NS6 to NS7. One of the main reports that we use is ARP by Owner's Department. I was wondering if anyone might be able to assist me with converting the report?

The SQL for the report is the following:

SELECT DISTINCT T1.[Name] AS 'Name', T0.[Name] AS 'Product Name', T0.[Version] AS 'Version', gd.[Department], gd.[Display Name] AS 'Owner', T1.[_ResourceGuid]
 

WHERE T0.[Name] LIKE '%_name%'
AND gd.[Department] LIKE '%_department%'
Order by gd.[Department]

The Global Parameters that we use are:



Thanks in advance!

-Chris

Thanks, seems something is off in my environment. I am receiving this message at the top of the report after importing it:

When editing the report, under the Data Snapshot Query tab it states:

Data Snapshot Query cannot be Resolved:

'The RawSqlDataSource ran but one or more Database objects were not present'

This is on the Resolved Query tab:

Altiris.DataSource.Exceptions.DataSourceMissingObjectException: The RawSqlDataSource ran but one or more Database objects were not present. ---> System.Data.SqlClient.SqlException: Invalid object name 'Inv_AeX_OS_Add_Remove_Programs'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Altiris.Reporting.DataSource.RawSqlDataSource.GetSchemaFromDataReader()
   --- End of inner exception stack trace ---
   at Altiris.Reporting.DataSource.RawSqlDataSource.GetSchemaFromDataReader()
   at Altiris.Reporting.DataSource.RawSqlDataSource.RunSchema()
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.UpdateTarget()
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.DataBindResolvedQuery(Boolean bCallback)

Thanks again for the assistance,

-Chris

Well, the first step would be to run the raw query against your database from sql server management studio to see if actually works.

Let me know.

SELECT DISTINCT T1.[Name] AS 'Name', T0.[Name] AS 'Product Name', T0.[Version] AS 'Version', gd.[Department], gd.[Display Name] AS 'Owner', T1.[_ResourceGuid]

FROM [Inv_AeX_OS_Add_Remove_Programs] T0

INNER JOIN [Inv_AeX_AC_Identification] T1 ON T0.[_ResourceGuid] = T1.[_ResourceGuid]

INNER JOIN [ResourceAssociation] ra ON T0.[_ResourceGuid] = ra.[ParentResourceGuid]

INNER JOIN [Inv_Global_User_General_Details] gd ON ra.[ChildResourceGuid] = gd.[_ResourceGuid]

WHERE T0.[Name] LIKE '%'

AND gd.[Department] LIKE '%'

Order by gd.[Department]

Well I did have to change a line after going through the raw query.

I changed the [Inv_AeX_OS_Add_Remove_Programs] T0 to

[INV_AddRemoveProgram] T0

I am not getting any errors now, but when I test it in the studio, it is not returning any results. Someone else here at work said that it should be showing alot of information since I did not put in any parameters.

Hard to say. It seems like you do not have the Inv_AeX_OS_Add_Remove_Programs view. Although from what you've said you had it in version 6. Without seeing your environment it's hard to troubleshoot something like this. Sorry.

Dan

With the change from v6 to v7 Symantec introduced the Softwre Management Solution and this has some differences in database tables to v6.

From my experience the best table to use for Installed software is Inv_InstalledSoftware and this needs to be linked to a couple of other tables for the details of the software. The Software Inventory in 7 gathers a whole bunch of info from more than just the AddRemove registry location and its difficult to separate it out. The Inv_AddRemoveProgram should have data in it too so perhaps check if there are any rows in there.

Below is a modified version of your SQL that uses the v7 Tables/Views that Symantec use in their reports. This query works for me without the department information/joins - as I am not sure what info you have in the db for the joins.

SELECT DISTINCT T1.[Name] AS 'Name', sc.[Name] AS 'Product Name', isc.[Version] AS 'Version'
, gd.[Department], gd.[Display Name] AS 'Owner'
,T1.[_ResourceGuid]

--FROM [Inv_AeX_OS_Add_Remove_Programs] T0
FROM Inv_InstalledSoftware iis
JOIN Inv_Software_Component isc
    ON iis._SoftwareComponentGuid = isc._ResourceGuid
JOIN vSoftwareComponent sc
    ON isc._ResourceGuid=sc.Guid

INNER JOIN [Inv_AeX_AC_Identification] T1 ON iis.[_ResourceGuid] = T1.[_ResourceGuid]
INNER JOIN [ResourceAssociation] ra ON iis.[_ResourceGuid] = ra.[ParentResourceGuid]
INNER JOIN [Inv_Global_User_General_Details] gd ON ra.[ChildResourceGuid] = gd.[_ResourceGuid]

WHERE sc.[Name] LIKE '%_name%'
AND gd.[Department] LIKE '%_department%'
Order by gd.[Department]

Thanks for the response, I apologize for not getting back to this post earlier.

The SQL you gave me worked great! I made a couple additionals for our needed parameters and its running smoothly.