Welcome
Software / Technology

Using Device42 Devices in Kelverion Automation Portal Queries

- October 7, 2019 | October 7, 2019 - Bryan McClellan

Summary

I love automating repeated admin tasks. Especially ones that cross technology boundaries where the steps may have to go through multiple teams or silos. I build a lot of forms-based apps and I see the need for a form to front all kinds of infrastructure self-service solutions on a weekly basis. I like to mainly focus on the admin tasks that are repeated. This is mainly to collect the right amount of data and in the correct format. This also removes the error prone chair to key process.

Due to the nature of my job, I need a couple standalone products that can cross boundaries and be used by multiple teams. I need a robust CMDB for storing data about my Cis, devices, objects, assets or whatever you would like to call them. I need a portal so I can use this data to automate tasks throughout the enterprise.

Which brings me to this write up. I have been playing two products. Kelverion Automation Portal, shortened to Kelverion throughout, to front end the request and Device42 to hold machine and user data for automating.

TL/DR

I want to make the Kelverion Automation Portal and Device42 things work together.

Challenge

Kelverion Automation Portal is SQL based. It reads from SQL. It writes SQL. It will get most of its power from “Queries” you use to build dynamic fields. These fields then are used by the offering user to request the offering.

Device42 is JSON based and uses a RestAPI to expose its data. I need to get the data from Device42 into a SQL table to consume using the Queries in Kelverion Automation Portal.

Solution

Configure a persistent data source (Device42 SQL Cache) to hold a subset of the machine data that Kelverion Automation Portal can use.

Create the Device42 SQL Cache

This assumes you have the Kelverion Automation Portal installed and that you have Device42 with some devices in it.

  • Get a SQL server you can use
  • You will need a new database called Device42
    • Small, this is just going to hold some tables we will use for Device42 SQL Cache
  • You should create a group in AD and add the Kelverion Automation Portal Server to the group
  • Add that group to the security of the Device42 database
    • dbreader is enough here but you get a SHOWPLAN error on the SQL query tester later without dbowner
  • Get the Device42 ODBC Driver from here
  • Install It on the SQL server
  • Configure System DSN on the SQL Server to connect to your Device42 instance
    • Device42 user who has at least read access
  • Create a SQL Import Job to a table in the new database
  • Use device42 (name of the System DSN) as the connection name
  • Select the SQL Server Native Client 11.0
  • Use the Device42 database as the destination
  • Leave the default here
  • Find the “view_device_v1” table, select it and click edit mappings
  • Set drop and recreate table
  • Turn off all mappings except the following.
          • name
          • type
          • in_service
          • serial_no
          • uuid
          • customer_fk
          • notes
          • first_added
          • last_edited
          • os_name
          • last_changed
          • datacenter
  • Be sure to set Drop and re-create destination table
  • Run and save
  • Set the encryption and file system
  • Give it a good name and save location so it can be ran by the SQL Server Agent account
  • Review and finish
  • You should now have a database with a table in it
  • Selecting the tops 1000 row query from context should produce rows.
    • Your row count will vary
  • Create your job and schedule your job
  • Give it a name
  • Go to steps
  • Click on New..
  • For the new step Select the SQL Server Integration Services Package Type
  • Select File System for the package source
  • Browse to your package file you saved from earlier
  • Click OK
  • Create a schedule
    • My environment is dev so I like to see changes quickly, daily or hourly maybe good or once a week. Whatever.

Now you should have a connection to Device42 which is refreshing a subset of Device42 devices into the Device42 SQL Cache.

Next, we hook it into Kelverion.

Create the Kelverion Query

  • In the Kelverion Automation Portal Select Queries and New Query
  • Give it a good name
  • Enter in your SQL server name
  • Enter your database name
  • Select Windows Authentication
  • Enter your query
    • “select name from view_device_v1 where in_service = 1” will return only in service device names
  • In services Create a Service if you do not already have one
  • Create an offering in that service
  • Add a new Offering field
  • Use the Type of Table and use your Query you just created
  • Use this field selection in a data set for your form as a selection with a search field to narrow the query results.

More to come!

Have a good day!