create a custom SCSM connector for SQL Server without Orchestrator

Question

Hello,

I've been asked to look into the possibility of creating a connector for a custom SQL Server database for our SCSM instance. We do not have Orchestrator. My understanding is that it's possible. As an organisation we have lots of different programming skills between us. Could somebody please point us in the direction of where to start / documentation / examples.

Thank you in advance.

Answer 1

Now i get an idea what you are planning to do ;-)

First of all you need to create the new classes for your assets.
More details for this you will find here: https://learn.microsoft.com/en-us/system-center/scsm/auth-classes?view=sc-sm-2019

To get the data in SCSM from SQL you have 2 options in my opinion:

1. Exporting the data from SQL in CSV and import the CSV in SCSM (import can be done with PowerShell) - https://learn.microsoft.com/en-us/system-center/scsm/import-data-csv?view=sc-sm-2019
2. Using a PowerShell script to query the data from SQL DB and import in SCSM (Create/update SCSM objects)

The first option is easier.
The second option offers the opportunity to use some more complex "import logic".

Both options you could schedule with:

1. The Task Scheduler of the OS using PowerShell scripts.
2. Use a SCSM internal workflow with a schedule

How to create a scheduled workflow in SCSM is described here: http://www.scsm.se/?p=239
Instead of the PowerShell script to create a CR you could use any PowerShell script for "CSV import" or "query SQL and import in SCSM"

To keep it simple I would prefer the Task Scheduler for the import job via PowerShell.

If I am using PowerShell to get things done in SCSM I prefer the SMlets (SCSM PowerShell Cmdlets)
https://github.com/SMLets/SMLets

Hope this helps.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 2

Could you please provide some more details what you like to do with the "SCSM-SQL Connector"?

• Importing Data from SQL?
• Do something in SCSM if in a SQL DB data is inserted, updated or deleted?
• Connector should start if an event happens?
• Connector should start based on a schedule?

Basically everything you can do in Orchestrator is possible with another tool as well. For instance with Task Scheduler and PowerShell.
Orchestrator just offers "ready-to-go" connections and activities to automate.

---

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

Hi Andreas,

Thank you so much for replying.

So, we're creating a custom asset database on SQL Server and will ideally want to import some of that hardware data into SCSM (to store in assets). We take some of it already from AD but need certain classes of assets (prints, switches etc). from this custom SQL database.

A daily upload would work fine. Of new, or changed assets. Perhaps with some level of <if it exists in SQL database & not SCSM database, update - else create>. haven't thought about deletes yet.

I'm a SQL woman & know powershell enough to get cracking. Is there an existing module to call upon or a script I could plagiarise? Or is it a case of copying from the custom SQL database into the SCSM database via a script on a schedule.

Appreciate your advice.

Jess.

Answer 4

That's amazing. I wasn't sure where to start but have enough now to really crack on with this.

Thank you for taking the time to lay it all out Andreas!

Jess.

:)

Answer 5

Jessica, did you succeed?

I would like to use an SQL database to use in request offerings for dynamic content to select from in list boxes. Not sure how to proceed and wondering what your needs were?

I was thinking that it would be possible to create an additional data source in Data Warehouse or a connector in the Administration part but that is limited to Active Directory and some MS products but not SQL Server. Should I go with diving deeper into this classes solution?