Home› General Discussion

Rest API and SQL

Raymond_DidonatoRaymond_Didonato Customer IT Monkey ✭
in General Discussion
This question does not involve any particular process of the Portal, but I have a customization that I'm working on in the Portal, and I could use some pointers if anyone out there has the experience.

Background: I used the API from Cireson to write a script that produces a link where Affected User is available throughout the Portal to see the most recent requests where the selected user is the Affected User.  This is giving the first call team and the IT Help Desk some insight over callers that call in multiple times for the same thing, and to make follow up easier.



I like it, but I want to do more.  I want to be able to query any type of CI and produce the same results.  Computers, servers, printers, etc.  Show me the most recent work items where this CI is related. 

I started to learn how to leverage Node JS to send a SQL query, and get some data returned.  If I run this query:

USE ServiceManagement
SELECT TOP 5 ci.DisplayName AS ConfigItem, wi.WorkItemId AS WorkItem
FROM dbo.ConfigurationItem ci
INNER JOIN dbo.Related_CI$WorkItem_ConfigurationItem r
ON ci.Id = r.ConfigurationItemId
INNER JOIN dbo.WorkItem wi
ON wi.Id = r.WorkItemId
WHERE ci.DisplayName = 'Raymond DiDonato' ORDER BY wi.Created

In SSMS and in my API call, it works just fine.  However, I need to make it dynamic so that I'm using a variable for the CI.  I've tried to follow an example similar to https://medium.com/voobans-tech-stories/how-to-quickly-create-a-simple-rest-api-for-sql-server-database-7ddb595f751a in order to get the syntax correct, but I'm always getting the 500 server error when I execute.  Does anyone have experience with doing something like this?  I can provide more detail if anyone needs it.

Thanks!

Best Answers

  • Raymond_DidonatoRaymond_Didonato Customer IT Monkey ✭
    Answer ✓
    Wow, yeah.. it was just me not writing it out properly.  So I have this:

    app.get('/ConfigItem/:Name', function(req, res) {
    new sql.ConnectionPool(dbConfig).connect().then(pool => {
    return pool.request().query("USE ServiceManagement SELECT TOP 5 ci.DisplayName AS ConfigItem,            wi.WorkItemId AS WorkItem FROM dbo.ConfigurationItem ci INNER JOIN                dbo.Related_CI$WorkItem_ConfigurationItem r ON ci.Id = r.ConfigurationItemId INNER JOIN dbo.WorkItem wi ON wi.Id = r.WorkItemId WHERE ci.DisplayName = '" + req.params.Name + "' ORDER BY wi.Created DESC");
    }).... and so on

    and if I type in <servername>:<port>/ConfigItem/<computername>, I'm getting the returned data.  Now the last piece of the puzzle is figuring out how to pass it as https

    Thanks Justin!

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    You might consider using the API call for GetDashboardDataById.  You can define a dashboard query with tokens in the format of {{token}}.  Then in your javascript feed the token values into the api url.  I hope that makes sense...
  • Raymond_DidonatoRaymond_Didonato Customer IT Monkey ✭
    I made the API call to list all of the available query IDs for the dashboards.  To define a new query, would I need the analytics package?  We don't have it unfortunately, just the Analyst Portal.
  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    You probably would need the Analytics license.  Sorry.
  • Raymond_DidonatoRaymond_Didonato Customer IT Monkey ✭
    Answer ✓
    Wow, yeah.. it was just me not writing it out properly.  So I have this:

    app.get('/ConfigItem/:Name', function(req, res) {
    new sql.ConnectionPool(dbConfig).connect().then(pool => {
    return pool.request().query("USE ServiceManagement SELECT TOP 5 ci.DisplayName AS ConfigItem,            wi.WorkItemId AS WorkItem FROM dbo.ConfigurationItem ci INNER JOIN                dbo.Related_CI$WorkItem_ConfigurationItem r ON ci.Id = r.ConfigurationItemId INNER JOIN dbo.WorkItem wi ON wi.Id = r.WorkItemId WHERE ci.DisplayName = '" + req.params.Name + "' ORDER BY wi.Created DESC");
    }).... and so on

    and if I type in <servername>:<port>/ConfigItem/<computername>, I'm getting the returned data.  Now the last piece of the puzzle is figuring out how to pass it as https

    Thanks Justin!
Sign In or Register to comment.