Custom SQL
This guide will walk you through integrating vScope with an SQL database to inventory assets, using a sample case to display asset costs. This method is adaptable to various asset types and multiple tags.
Video: Getting Started with Custom SQL
Steps to Set Up Custom SQL Integration
- Ensure vScope has access to the SQL database and required credentials.
- Check the SQL table: Identify necessary columns and expected values.
- Write and preview the SQL query before pasting it into vScope.
- Add the query to vScope and initiate a discovery.
- Review results in vScope.
- Configure tags in Tags.
1. Access & Credentials
First, confirm that the vScope server can access the SQL database. Different SQL databases use various default ports:
- MySQL: 3306
- MSSQL: 1433
Configure the connection under Advanced Settings when adding credentials for Custom SQL. Ensure the account has read permissions.
To add Custom SQL credentials, go to Discovery Manager > Credentials > + Credential > Custom SQL and input the following:
- Username
- Password
- SQL Type
- Database Name
- Query (specific to asset type)
- Note (optional)
- Custom Port (optional)
2. Check the SQL Table in Data Source
In this example, we’re adding assets from a MySQL database. Reviewing the table:
Notice that the Serial Number column must be renamed to Asset Serial Number to allow vScope to match assets. Additionally, filter out non-computer assets like iPhone.
3. Writing the SQL Query
Starting with a basic query, adjust it to select relevant columns and match criteria:
SELECT * FROM `asset_table`Refine to include only necessary columns:
SELECT `Name`, `Domain`, `Serial Number` AS `Asset Serial Number`, `Total Cost` FROM `asset_table`Preview the output to ensure it matches the expected format.
4. Inventory the SQL Database
In vScope, paste the SQL query into the Computer Query field. To verify the setup, click the lightning icon next to the field. This will test the connection and ensure that the query format is correct. If the test is successful, initiate a discovery by clicking Rediscover for the Custom SQL credential.
If everything looks correct, you can check the Log History tab in Discovery Manager to review the details of each step, including any success or error messages from the discovery process.
5. Viewing the Results
Since we’re adding Computer assets, create a table in Tables to list All Machines. Apply a filter to show only assets Found By: Custom SQL to isolate the newly added data.
You may notice that some assets appear as new, while others merge with existing records. For instance, assets with matching Asset Serial Numbers and Domain will be merged with pre-existing records, while unmatched records will be added as new assets.
If an asset is missing a Domain or Asset Serial Number, it won’t be stitched with existing records, as these fields are required to confirm a match.
6. Fetching Values to a Tag in vScope
With the SQL connection established, you can now configure vScope to continuously fetch specific values from the SQL database and add them as tags.
- Navigate to Tags.
- Go to Created from Discovery and click + Create Tag.
- Select Custom SQL as the source and choose All Machines as the asset type.
Give the new tag a meaningful name, such as Total Cost (SQL Database), to clarify that the values are fetched from an external SQL database.
- From the Value from dropdown, select the SQL column that corresponds to the data you want to retrieve, in this case, Total Cost.
- Set the Format to Number and define the Unit as SEK.
Click Create Tag to finalize, and then rediscover the Custom SQL credential to populate the tag. Now, in Tables, add the Total Cost (SQL Database) column to your table to view the values.
