Create a Custom Inventory for DSN's (ODBC)

I just recently came up with a need to find all systems with a specific DSN name so I can target those systems with a change to the DSN configuration. By default Altiris Inventory gets the installed ODBC drivers, but not the configured DSN's.

I have written a script using Wise Scripting. I have attached a ZIP file containing the WSE file and the compiled EXE file.

The script queries the registry key HKLM\Software\ODBC\ODBC.INI and its sub keys, then dumps it to a temporary txt file. It then does some parsing of that file to create another txt file with just the DSNs. Then it takes that file to more specifically query the registry for each DSN and their configurations. It then uses the information it has found to create an NSI file for Altiris Inventory Solution. After Inventory Solution collects this info there is now a table in the Altiris DB named Inv__DSN_Info_Inv.

To get this into Inventory copy the GetDSNInfo.exe file into the \\server\NSCap\Bin\Win32\X86\Inventory Solution folder on your NS server. Then create an INI file named AexIncSolnDSN1.ini with the following:

GetDSNInfo.exe
aexnsinvcollector.exe /hidden /nsctransport /v default /useguid

Open up Inventory Solution in NS. Click View > Solutions > Inventory Solution

Expand 'Tasks > Windows > Inventory Tasks' then select one of your current tasks. e.g. Software Inventory.

Click 'Go To Program'. You will get the page to modify the package.

Click the new button and enter the following then click Apply and close the window:

Name: DSN Info definition file
Command line: aexinvsoln.exe /hidden /s AeXInvSolnDSN1.ini
Estimated disk space: 124
Estimated run time: 1
Terminate after: 10
After running: No action required
Start window: Hidden
Run with rights: System Account
Program can run: Whether or not a user is logged on
Minimum connection speed: No network connection required

Right click on 'Software Inventory' and choose Clone. Give the new task a name like 'DSN Info Inventory'

Select Enable for the new task. Choose program name 'DSN Info definition file'. Select the collection(s) you want this to run against and when you want it to run.

Here is an example of my task.

When the systems start to report in they will run this custom inventory and the table will be created in the Altiris DB.

Here is an example of a report I created to gather this info. In 'Reports > All Reports' right click on User Defined and choose 'New > Report'

In the report wizard enter a name for your report and choose Enter SQL Directly. Insert the follow SQL statement and click Finish:

SELECT VC.[Name] as 'Computer Name', 
	VC.[User] as 'User most logged in for the month', 
	DI.[DSN_Name] as 'DSN Name', 
	DI.[DSN_Config_Details] as 'DSN Config Details', 
	VC.GUID as '_ResourceGuid'
FROM     
	dbo.vComputer VC 
	Join dbo.Inv__DSN_Info_Inv DI on DI._ResourceGuid = vc.Guid    
WHERE     
	DI.[DSN_Name] LIKE '%DSNName%' 
ORDER BY VC.[Name], DI.[DSN_Name], DI.[DSN_Config_Details]

Click on your report in the NS console and select 'Edit this report'. Click the 'New Parameter' button. Enter the following and click OK then click Apply on the report editor.

Name: DSNName
Parameter type: Basic
Select 'Prompt user for...'
User prompt: Enter DSN Name To Search For (Use % as Wildcard)
Value type: String
Value: %
 

You can now run your report to get your systems that have DSNs configured and the details of their configuration.