Client Management Suite

There are often cases where you might have some unique reporting requirements, to have data associated in a specific way, and need to be able to run the reports based on that data very quickly. Often, there are environmental concerns that put constraints on how this data can be accessed.

Environment

This was performed in an Altiris 6.x environment. While the concepts can be used in a Symantec Management Platform 7.x environment, all screenshots, console references, and configuration descriptions provided reference a 6.x environment.

Our hypothetical situation, including reporting requirements, operating parameters, and environmental or other constraints are listed below:

1. We needed to be able to report on applications that had not been used in the last 30 days.
2. We also needed to know, by machine, which machines, from the group above, had actually been managed by the NS for longer than 90 days, and, in fact, when they first connected to the Notification Server.
3. We wanted this report, and other similar reports to run on the reporting server, as inventory forwarding was active.
4. We did not want the SQL queries to adversely affect the SQL server during production hours, when the reports would need to be run.

Some Further Specifics

A know restriction of Inventory Forwarding in the 6.x environment is, that the CREATEDDATE value that the ITEM table contains will be different on the source or forwarding Notification Server (NS), than it will on the destination or reporting NS, for the same object. This is a known restriction, that the CREATEDDATE value is the actual date when the object was created, on the respective NS's, and that Inventory Forwarding (and its associated item replication functions ) does not change the CREATEDDATE values to match each other. This presents a problem, if you need to reference and know the exact date a resource became managed in the environment, because the true CREATEDDATE is the date contained on the source NS. But to help keep production databases functioning efficiently, processing all of the NSE files and other web requests and data processing, it is not best practice in large environments to also burden the source or managing server and its respective database server with report processing load. Hence the need to have accurate data on the reporting server.

The Options and Solution Chosen

There were a few ideas on how this could best be handled. Since acquiring the data required SQL reports and queries to be processed, one idea was to create a SQL-based linked server configuration, similar to how Recovery Solution interacts with a separate Notification Server. This option, while providing the required data, after consideration, was discarded as having too much potential for additional load placed on the managing NS. Another option was to accept the CREATEDDATE value from the reporting NS. This was also rejected as inaccurate.

The final solution chosen was to create a new custom data class on the reporting server, which would contain a resourceguid, and a CREATEDDATE value. The CREATEDDATE value would be populated via a Connector Solution Rule, which would, on a recurring (daily) schedule (scheduled to run at night to avoid conflict), query the managing NS's Item table, capture the correct CREATEDDATE, and populate the custom dataclass accordingly. Then the report could be modified to join tables to include the new dataclasses information, and provide accurate reporting. The remainder of this article describes how this was done. While this implementation may seem reasonably simple, it is hoped that the ideas formulated here will be useful to show a different method to use Connector solution to achieve a specific goal.

Implementing the Chosen Solution

In addition to having a custom report already generated for the Software Inventory and Usage, the following steps were required to build this solution. Please note that all steps outlined below should be performed on the reporting server. You will need SQL access credentials to create a connection to the source server.

1. Create a Custom DataClass
2. Associate that DataClass to the Computer Resource Type
3. Create and Configure a Connector Solution Data Source
4. Create and Configure a Connector Solution Import\Export Rule
5. Run the Import\Export Rule created in Step 4
6. Modify the custom report and replace CREATEDDATE from the source NS's ITEM table with the CREATEDDATE from the new dataclass on the reporting server.

A. Creating the Custom DataClass

You first should create new custom dataclass. To do so, open the Configuration Tab, and then browse to the following location in the console - Resource Settings>Data Classes>User Defined. Right-Click on User Defined, and Choose New>Editable Data Class, as shown in Figure 1 below:

Figure 1

You will then be presented with the Resource Data Class Configuration page. Enter a name and useful description for the data class. Leave the "Multiple Rows" box, and the "Data Entry Tab Name:" options unchanged. Next, under "Data Class Definition", click on the "Add New Attribute" button to open the Data Class Attribute Configuration Screen. Since creating a custom dataclass also creates a custom database table in the Altiris database, this is essentially allowing you to configure the various column or columns of data that will appear in that table. Since we are only interested in the resource guid and the CREATEDDATE, we are going to create a new attribute for CREATEDDATE. We will obtain, by resource association, during the import, the associated resource guids. Follow the items below and refer to Figure 2:

Name: Createddate
Description: (Enter data or leave blank as required)
Type: Date
Key: (Leave as Default)
Required: (Leave as Default)
Hidden: (Leave as Default)
Display Order: 1

And click OK

Figure 2

Once done, click Apply to build the DataClass

B. Associate that DataClass to the Computer Resource Type

Next, we need to associate the newly created data class to the Computer resource type. In the console, assure that you have opened the Configuration Tab, and then browse to Resource Settings>Resource Types>Asset Types>IT>. Click on the Computer resource type to see the screen shown in Figure 3 below:

Figure 3

Click on the "Add Data Classes" button, and browse to the "User Defined" dataclass folder location, shown in Figure 4 below. Choose the custom data class that you created in Step A, and click Apply, then Apply again to complete the association.

Figure 4

C. Create and Configure a Connector Solution Data Source

Next, we need to define a data source rule. This will require access to the source Notification Server's SQL database. Please ensure that you have correct SQL access credentials to connect to the source database server.

Again, from the Configuration tab, browse to Solutions Settings>Connectors>Data Sources. Right-Click on the Data Sources folder, and select New>OleDB Data Source, as shown in Figure 5 below. First, provide a name and description for this new data source. Next, provide the "Data Source Type:" by using the dropdown menu and selecting "MS SQL Server Database". Provide the "DB Server Name:" next and enter the name of the source management database server. The next step is to assure you have connectivity. If the account that you are logged into the server with has access to the source server, you can click the "Load List" button, next to the "DB Name:", and then you should be able to dropdown a list of all of the available databases on the selected server. If not, you will need to provide the correct "User Id:" (in the format of DOMAIN\Username) and "Password:", to access the SQL server, after which you will then be able to drop down and choose the correct "DB Name:", and "Table Name:". The example shown in Figure 5 shows connectivity WITHOUT needing credentials.

Figure 5

Make sure you check the "Allow Imports" box. You can leave the remaining choices at default settings, and click Apply. It is also a good option the first time, to click the "Test Data Source" button, to make sure that the data you want to include is correct. Next, we will create the Import\Export Rule.

D. Create and Configure a Connector Solution Import\Export Rule

Once the data source is configured, the next step is to configure the Import \Export rule to that the custom dataclass created in Step A can be populated with the Resource guid and CREATEDDATE information that we need.

Again, from the Configuration tab, browse to Solutions Settings>Import\Export Rules. Right-Click on the Import\Export Rules folder, and select New>Resources Import Export Rule, as shown in Figure 6 below.

Figure 6

You will now see the new Resources Import Export Rule page, as shown in Figure 7.

Figure 7

First, provide a name and description for this new import\export rule. Next, provide the "Data Source:" by using the dropdown menu and selecting the name of the data source you created previously in Step C. Using the dropdown menus provided, provide the "Replication direction:" of "Import", and the "Resource Type" of "Computer". Leave "Multiple resource type imports" and "Allow resource creation:" set to defaults. Check the box next to "Allow resource update:". The "Removed assets should be:" option should be selected to "Left unchanged in the NS". Leave the rest of the options set to defaults, as shown in Figure 7. Next, expand the "Column Mappings" section as shown below in Figure 8.

Figure 8

We must provide a Resource Lookup Key. Select Resource Guid and correlate (connect) that to Guid, from the source table. This makes the connection between resource guids of the computers.

Next, we need to map our new data class, and its attributes, to the source table. Click on the "Select data classes...", and choose the correct data class name and attribute of Createddate to map to the Createddate of the Source server. There is no need for any Association or Reverse Associations Mappings that need to be configured.

We also need to configure a recurring schedule for this to happen - so that as new resources are created and forwarded from the source, the CREATEDDATE is also populated. This import should not need to occur more than daily. Configure the schedule in the UI as shown in Figure 9. Lastly, click Apply to save all the settings. You also have two additional option buttons on this page, "Run Rule" and "Test Rule", both of which appear to function the same - but the "Run Rule" option actually populates the data class, whereas the "Test Rule" does exactly that, running the rule without actually making any database changes.

Figure 9

E. Run the Import\Export Rule

Once you have tested the rule, click "Run Now", and let the data populate. You may want to open a SQL query analyzer window to verify that there is data that is the new data class. You can find the correct table by searching for Inv_[Data Class Name]. You should be able to see what data is populated by listing the contents of this table.

With all of the options created and functioning, now you can modify your custom report and replace CREATEDDATE from the source NS's ITEM table with the CREATEDDATE from the new dataclass on the reporting server. Sample SQL (delineated in the SQL below) is included below. Replace the table name references as necessary. This SQL can then be included in a report definition. The same contains some parameters, such as App_Days and Computer_Days, delineating the number of days the Application has not been used, and the number of days the resource has actually been managed by the source NS.

select distinct v0.[Name],v0.[User],t0.[Application Name],v0.[IsManaged],v0.[GUID] from vComputer v0

 LEFT OUTER JOIN [Enter Custom Software App Inventory Table] t0 on v0.[GUID] = t0.[_ResourceGuid]
LEFT OUTER JOIN [Inv_ENTER NEW CUSTOM DATA CLASS NAME] t2 on v0.[GUID] = t2.[_ResourceGuid]
 where v0.[IsManaged] ='1' AND t0.[Application Name] like '[Enter Inventoried App Name here]%' AND t0.[FileName] != 'Not found'
AND guid NOT in (select _ResourceGuid from Inv_AeX_AM_Monthly_Summary
 where [product name] = [Enter Inventoried App Name here]' AND [last Start] >= getdate () -%App_Days%)
AND guid NOT in ( select _ResourceGuid from Inv_AeX_AM_Monthly_Summary_Archive
   			where [product name] = [Enter Inventoried App Name here]' AND [last Start] >= getdate () -%App_Days%)
AND guid in ( SELECT _ResourceGuid from dbo.Inv_AeX_AC_Client_Agent
 			 where [Agent Name] = 'Application Metering')
AND guid NOT in (SELECT _resourceguid from [Inv_ENTER NEW CUSTOM DATA CLASS NAME] where createddate >= getdate () -%Computer_Days%)

Conclusion

While this is a reasonably simple application, it does outline the flexibility that Connector solution has to provide data between Notification Servers, when Inventory Forwarding, and other replication methods don't offer the desired results. It is hoped that this might be an example for other various applications.