2 Replies Latest reply on Apr 17, 2014 5:56 AM by tpratt

    Running SQL script to get query data

    Jamie Cannon ITSMMVPGroup

      Is it possible to run a query within Service Desk using a SQL script instead of the built in query engine?

        • 1. Re: Running SQL script to get query data
          Mariusz.Maniak Expert

          Edit:

          I misunderstood Your question, however maybe it will be a bit helpfull

           

          ------

           

          Yes, it is, in calculation, but You have to use some .net :

           

          import System

          import System.Data.SqlClient

          static def GetAttributeValue(Incident):

           

              try:

                  Value=DateTime.UtcNow.ToString("yyyyMMdd")

           

                  dbcon as SqlConnection = SqlConnection()

                  command as SqlCommand = dbcon.CreateCommand()

           

                  connectionString as string = 'Server=db;Database=LDSD;User Id=LD;Password=****'

                  commandstring as string ="SELECT count(*)FROM [LDSD].[dbo].[pm_process]where pm_lifecycle_guid ='2E560C8C-71A2-4762-94E4-0AFB19F7C1E3' and CONVERT (date, pm_raise_date) = CONVERT (date, SYSDATETIME())"

           

                  dbcon.ConnectionString = connectionString

                  command.CommandText= commandstring

           

                  dbcon.Open()   

           

                  Value2=command.ExecuteScalar()+1

           

                  dbcon.Close()

           

           

           

                  if Value2<10:Value3=Value + String.Format('00{0}', Value2)

                  elif Value2<100:Value3=Value + String.Format('0{0}', Value2)

           

                  return Value3

           

              except e:

                  raise String.Format('Error on {0}: {1}', e.StackTrace.Substring(e.StackTrace.LastIndexOf(':') + 1), e.Message)

           

          In example above, I'm counting incidents logged today, to prepare my custom IPC Id.

          • 2. Re: Running SQL script to get query data
            tpratt Apprentice

            I have used a less than elegant solution that may or may not fit your requirements. I simply created a View in the database for what I needed (i.e. - hopefully the view can apply all the constructs you use in the script). I then added the view as a "Data Connection" - under "Generic Data Sources" - at which point I have a custom object that I can query against. Since the view does all the heavy lifting I use the Query tool in LDSD just to select to columns I want tand to apply any filters.

             

            This works very well for example when I need data from t business objects - where neither have all the data I want (an example was for PC's - I have 1 Object that had the CI's that had a User Assigned - and 1 that had All CI's - but I wanted to show ALL CI's and an assigned user IF one existed). So - pretty much anything you can do in SQL can get done in a view - unless you need a runtime value to construct the view (easy to use a runtime value to limit the results - just not to create the view).

             

            The limitation is that the query result is related to my custom Object (the view) - I understand why this is, just wish somehow the result set could be linked to another object. To make sure this is clear - if I click any of the items in the returned query I will see all of the columns for my custom view for the particular row selected. It would be great for example if I could link my object to another - for example so my custome view result could link to the Configuration Item object - if this is possible I have not done so.

             

            Perhaps this helps - perhaps not,

             

            Terry