Connecting to Custom SQL Database

Comments Off on Connecting to Custom SQL Database

Custom SQL allows you to discover assets using your own SQL queries. In this guide, we’ll describe how it works.

Currently, vScope supports the following SQL databases:
– MS SQL
– MySQL

What you can do with Custom SQL?

  • Create new assets
  • Enrich information about existing assets
    • Tags
    • Merge instances
    • Mark assets as removed

Requirements

To ensure data quality and that data gets added to the correct asset(s), there are a couple of requirements to create a unique identifier for an asset, eg. Asset Serial Number.

What is an Asset Serial Number?

An Asset Serial Number is one or several serial numbers related to an asset from different data sources. This value is used for eg. stitching, searching, and matching assets from various sources. Asset Serial Numbers can apply to many different Asset types.

The Serial Number found in your SQL query is not displayed by default. This is because vScope doesn’t know what kind of Serial Number this value represents. You can either add this value to an existing tag or create a new one with the name that it actually represents eg. (Product Serial Number, Service Tag, BIOS Serial Number).

Computer Systems

A computer system can be a server or a client.

Asset identifiers

  • Name (Single value, UTF-8)

At least one of the following:

A) Asset Serial Number (Single value, UTF-8)
B) Domain (Single value, UTF-8)

Requirements for creating new Computer Systems

  • Length of Name + Asset Serial Number + Domain >= 5 characters
  • Name + Domain must be unique in the SQL data source
  • Asset Serial Number must be unique in the SQL data source

Requirements for matching with exiting Computer Systems

vScope will extend existing information of an Asset if there is a match. There are numerous rules for this matching:

  • Existing integrations, managed by vScope (eg. Active Directory, Jamf, Azure, Desktop Central, Nutanix, etc…) are always considered to have higher data quality than Custom SQL
  • Match assets if:
    • Name + Domain = Name + Domain (eg. Laptop01 + Infrasightlabs.com)
    • Name + Asset Serial = Name + Asset Serial Number (eg. Laptop01 + C4WX8849D)
  • Do not match assets if:
    • Asset Serial Number != Asset Serial Number (eg. C4WX8849D vs. D8HJ84KOF)

Requirements for mobile devices

Asset identifiers

Required:

  • Name
    • Single value, UTF-8

At least one of the following:

  • Asset Serial Number
    • Single value, UTF-8
  • IMEI
    • Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number + IMEI >= 5.
  • Asset Serial Number must be unique in the SQL data source.
  • IMEI must be unique in the SQL data source.

Rules for stitching with existing asset

  • Only allowed to match on Asset Serial Number if IMEI is empty for the asset in vScope.
  • Only allowed to match on Asset Serial Number if IMEI value is the same for asset in vScope and new asset.​

Requirements for Access Points

Asset identifiers

Required:

  • Name
    • Single value, UTF-8
  • Asset Serial Number
    • Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number >= 5.
  • Asset Serial Number must be unique in the SQL data source.

Requirements for Switches

Asset identifiers

Required:

  • Name
    • Single value, UTF-8
  • Asset Serial Number
    • Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number >= 5.
  • Asset Serial Number must be unique in the SQL data source.

Requirements for Routers

Asset identifiers

Required:

  • Name
    • Single value, UTF-8
  • Asset Serial Number
    • Single value, UTF-8

At least one of the following:

Additional rules

  • Length of Name + Asset Serial Number >= 5.
  • Asset Serial Number must be unique in the SQL data source.
  • If there are multiple matches on Asset Serial Number, vScope attempts to match on the name to see if it finds a match.

Requirements for Display Devices

Asset identifiers

Required:

  • Name
    • Single value, UTF-8

At least one of the following:

  • Asset Serial Number
    • Single value, UTF-8
  • Device ID
    • Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number + Device ID >= 5.
  • Asset Serial Number must be unique in the SQL data source.
  • The Device ID must be unique in the SQL data source.

Rules for stitching

  • Match on Asset Serial Number or Device ID.
  • If there are multiple matches on Asset Serial Number. Then we attempt to match on Device ID to see if we find one match.
  • If there are multiple matches on the Device ID, vScope attempts to match on Asset Serial Number to see if it finds a match.

Requirements for Firewalls

Asset identifiers

Required:

  • Name
    • Single value, UTF-8
  • Asset Serial Number
    • Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number >= 5.
  • Asset Serial Number must be unique in the SQL data source.
  • If there are multiple matches on Asset Serial Number, vScope attempts to match on the name to see if it finds a match.

Requirements for User Accounts

Asset identifiers

Required:

  • Name (Single value, UTF-8)

At least one of the following

  • Email (Single value, UTF-8)
  • Domain (Single value, UTF-8)

Additional rules

  • Length of Name + Email + Domain must be more than 5 characters.
  • Name + Domain must be unique in the SQL data source.
  • Email must be unique in the SQL data source.

Rules for stitching

  • Match on Name + Domain or Email.
  • If there are multiple matches, vScope attempts to match on Name + Domain + Email to see if we find one match.

Writing the query

As stated above the database requires certain values so your query needs to contain these as columns. The database columns can of course be rewritten by your query.

The query should also contain any values you wish to add to vScope as these can later be created as tags in Tag Manager.

Example of SQL query for vScope

SELECT Name, Domain, SerialNumber AS 'Asset Serial Number', cost, date FROM `test_table_cs`

In this example vScope uses the query to convert the column “SerialNumber” to the required “Asset Serial Number”. It also specifies that we want to be able to add the tags “Cost” and “Date” to the assets later on.

The query will result in seven objects being added/updated in vScope from the database in the image. The eight rows will be skipped as it’s a duplicate and has already been discovered.

Adding the Custom SQL credential

Now that you have your database and SQL query it’s time to add it as a credential.

Username

As vScope runs the SQL query directly against the database we strictly recommend a user account with a read-only role in the database. vScope has taken measures to make sure it can only read but you can never be secure enough.

SQL type

Set the SQL type of the database. vScope currently supports MS SQL and MySQL.

Database name

The name of the database you’re targeting with the query.

Computer query

Insert your SQL query here. vScope can currently only create Computer Systems using Custom SQL.

vScope uses SQL’s default port 1433. You can change the port it uses under advanced.

Lastly, you add the target which is the IP or hostname of where the database is located.

Freaquently Asked Questions