Cireson Portal Request - SQL Widgets - Allow DataSource to be set

Conner_Wood

I am requesting the feature that SQL Widgets in the Portal can be configured to query directly against the SCSM ServiceManager Database instead of the Cireson ServiceManagement Database.

Currently SQL Widgets are only for querying the Cireson Database, which has some slight downsides because not all info is present in Cireson DB compared to SCSM Live DB.  One limitation is you can't search custom relationships or custom properties because it's not in the Cireson DB.

The Widgets in particular I'd want to have this feature in order of priority:

1. SQL Chart Widget
2. SQL Table Widget
3. SQL Count Widget

Remember if you want it to vote on it!

Jerry_Veldhuis

Great idea.

I will mention that if the database exists on the same server as your ServiceManagement db and you have access, you can use it in a query. As an example, I'm able to query my ReportServer db by specifying this in a SQL Table Widget.

select  [RoleFlags] FROM [ReportServer].[dbo].[Roles]

Not as idea as being able to select the db, but it is possible.

james_kleinschnitz

This one was already in our backlog and is being worked on as part of the Cireson Data Analytics feature coming soon.

Jerry_Veldhuis

james_kleinschnitz said:

This one was already in our backlog and is being worked on as part of the Cireson Data Analytics feature coming soon.

I hope the feature includes arbitrary databases on a separate server and not locked down to scsm dbs. Can you confirm what the design plans are ?

carrie_medine

Jerry_Veldhuis said:

james_kleinschnitz said:

This one was already in our backlog and is being worked on as part of the Cireson Data Analytics feature coming soon.

I hope the feature includes arbitrary databases on a separate server and not locked down to scsm dbs. Can you confirm what the design plans are ?

Hello Jerry,

We are currently working through the implementation details.  We plan to allow administrators to enter in custom datasources by providing:
Provider (i.e. System.Data.SqlClient)
ConnectionString (i.e. Server=sql;Database=ServiceManager;Trusted_Connection=True;)

Essentially allowing the ability to query any database that you have appropriate credentials to access.   The ConnectionString will of course be encrypted for the cases where a username and password is within the connectionstring.

We would love to hear your feedback and welcome any suggestions.
Thanks,
Carrie

Jerry_Veldhuis

another similar discussion - https://community.cireson.com/discussion/1501/allow-dashboard-sql-widgets-to-query-a-database-other-than-servicemanagement

Conner_Wood

Geoff_Ross said:

Hi Jamie, I think putting the alias in [] will work.

Ah, that helps me tremendously, I couldn't figure out why the "Group By" dropdown kept going read-only!

Edit:  It appears I cannot do "WITH" to predefine so I must adapt my query to maybe use subqueries instead.

WITH [AssigneeTickets] AS 
(
  SELECT TOP 1000 [Id]
  FROM [ServiceManagement].[dbo].[WorkItem]
)
SELECT [AssigneeTickets].[Id] FROM [AssigneeTickets]

Edit #2: Blah, also make sure for IN Operator that it returns a value, otherwise it may appear it doesn't work.