How to use Custom SQL in vScope

vScope 3.17.0 introduced the new probe Custom SQL where you can discover assets or enrich asset data using your own SQL querys. In this guide we’ll describe how you can use Custom SQL.

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

Custom SQL is only available for vScope installations running the latest database Kevas3. Contact support if you are unable to use Custom SQL or wonder what vScope database you are running.

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 assets, there are a couple of requirements to create a unique identifier for an asset. You will also need an Asset Serial Number.

What is an Asset Serial Number?

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

The Serial Number found in your SQL query is not displayed by default. This is because vScope don’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).

Requirements for Computer Systems

A computer system can be a server or a client.

Asset identifiers

  1. Name
    a) Single value, UTF-8
  1. At least one of the following:
    1. Asset Serial Number
    a) Single value, UTF-8

    2. Domain
    a) Single value, UTF-8

Additional rules

  • Length of Name + Asset Serial Number + Domain >= 5.
  • Name + Domain must be unique in the SQL data source.
  • Asset Serial Number must be unique in the SQL data source.
  • Only allowed to match on Asset Serial Number if there is ALSO a match on Name + Domain.

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.
  • 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.

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 'BIOS Serial Number', cost, date FROM `test_table_cs`

In this example vScope uses the query to convert the column “SerialNumber” to the required “BIOS 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 row will be skipped as it’s a duplicate and have 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 to 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 targetting 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.