8 Replies Latest reply on Apr 6, 2017 6:59 AM by Jamie Cannon

    Query External Datasource with current user

    Rookie

      We are running version 7.5 and are looking to create a new dashboard of telephony data.

       

      Records within the database are stored with agents full names, and there is no way to change this.

       

      I have create a DB link from our telephony database back to LANDesk, allowing me to create a view which also shows the ITBM username and GUID.

       

      I have created the data connection and a few queries, but now need to create a query based on the current logged in user.

       

      The only option appears to me the criteria "Is Me", however this throws and error "The condition type 'IsMe' is not supported for the 'String' data type."

       

      Does any one have any experience of filtering external data based on the current logged in user?

       

      Any advise or tips on how I can achieve this?

        • 1. Re: Query External Datasource with current user
          Senta SupportEmployee

          Hello Terry,

           

          besides, that 7.5 is outdated and not supported, I suggest that you import the data from the external system during a normal data import run.

          This allows you to use a reference to user in the new Object and which you then can filter with "is me"

          • 2. Re: Query External Datasource with current user
            Rookie

            Hi Senta,

             

            Unfortunately our hospital has no funding to maintain our maintenance and so we are not able to upgrade to the latest versions.

             

            I had considered the data import, but doing this would leave the data at best 1 hour out of date and would no longer be "Live" data.

             

            Can we still create a reference on this object to user and carry out the filter without requiring a data import?

            • 3. Re: Query External Datasource with current user
              Senta SupportEmployee

              Hello Terry,

               

              this will not be possible as "is me" is looking for a Landesk service desk user reference.

              Which you do not have when using an external data connection.

               

              Regards

              Landesk Support

              • 4. Re: Query External Datasource with current user
                Jamie Cannon ITSMMVPGroup

                So, just so I understand.

                 

                You have a 2nd database that has telephony data in it along with a user name and GUID.

                 

                Inside Service Desk, you are trying to create a query for a dashboard that says "Show me all calls where User is CurrentUser" (essentially).  But that query is actually pulling against that data connection instead of data inside of Service Desk.

                 

                Am I following this correctly?

                 

                Do you have a screenshot of your query criteria?

                 

                Thanks,

                • 5. Re: Query External Datasource with current user
                  Rookie

                  Hi Jamie,

                   

                  We have a view within our telephony database (MS SQL) as follows:

                   

                  SELECT
                    dbo.TCallLog_Extension.Date_Time AS [Start Time],
                    DATEPART(
                    HOUR,
                    dbo.TCallLog_Extension.Date_Time
                    ) AS HOUR,
                    DATEPART(
                    DAY,
                    dbo.TCallLog_Extension.Date_Time
                    ) AS DAY,
                    DATENAME(
                    dw,
                    dbo.TCallLog_Extension.Date_Time
                    ) AS Weekday,
                    DATENAME(
                    m,
                    dbo.TCallLog_Extension.Date_Time
                    ) AS MONTH,
                    dbo.TCallLog_Extension.Completion AS [End Time],
                    dbo.TCallLog_Extension.Agent_Name AS Agent,
                    dbo.TCallLog_Extension.Extension,
                    dbo.TCallLog_Extension.Group_Name AS [Group],
                    dbo.TCallStatus.Status_Name AS Type,
                    dbo.TCallLog_CO.Caller_Id AS [Caller ID],
                    dbo.TCallLog_CO.Caller_Name AS [Caller Name],
                    dbo.TCallLog_CO.[DDI/DID/MSN] AS DID,
                    dbo.TCallLog_CO.Dialed_Number AS [Dial Number],
                    dbo.TCallLog_CO.Customer_Id2 AS Customer,
                    CASE TCallLog_Extension.No_Answer
                  WHEN 0 THEN
                    'Yes'
                  ELSE
                    'No'
                  END AS Answered,
                  dbo.TCallLog_Extension.Ext_Number_Transferred_To AS Transferred,
                  dbo.TCallLog_Extension.TalkTime / 1000 AS [Talk Time],
                  dbo.TCallLog_Extension.RingTime / 1000 AS [Ring Time],
                  dbo.TCallLog_Extension.HoldTime / 1000 AS [Hold Time],
                  dbo.TCallLog_Extension.QueueTime / 1000 AS [Queue Time],
                  dbo.TCallLog_Extension.Department,
                  dbo.TCallLog_Extension.Group_Overflowed_From AS [Group Overflowed From],
                  dbo.TCallLog_Extension.Agent_NoAnswer_From AS [Agent Overflowed From],
                  dbo.TCallLog_Extension.Notes_1,
                  dbo.TCallLog_Extension.Notes_2,
                  dbo.TCallLog_Extension.Notes_3,
                  dbo.TCallLog_Extension.CustomerRequest,
                  dbo.TCallLog_Extension.AgentNotes,
                  dbo.TCallResult.ResultCode,
                  dbo.TCallLog_CO.CallLog_Id,
                  dbo.TCallLog_Extension.AgentRate,
                  dbo.TCallLog_Extension.FollowUp,
                  dbo.TCallLog_Extension.Forwarded,
                  dbo.AreaCodes.[Area Name] AS Area,
                  dbo.WebAccCodeName.[AccName] AS [Account Name],
                  dbo.WebDIDCodeName.[DIDName] AS [DID Name],
                  u.tps_guid AS [ITBMUserGUID],
                  u.tps_name AS [ITBMUserName]
                  FROM
                    dbo.TCallLog_Extension
                  INNER JOIN dbo.TCallLog_CO ON dbo.TCallLog_CO.CallLog_Id = dbo.TCallLog_Extension.CallLog_Id
                  INNER JOIN dbo.TCallStatus ON dbo.TCallLog_CO.Status_Id = dbo.TCallStatus.Status_Id
                  LEFT OUTER JOIN dbo.TCallResult ON dbo.TCallLog_Extension.CallResult_Id = dbo.TCallResult.CallResult_Id
                  LEFT OUTER JOIN dbo.AreaCodes ON CHARINDEX(
                    dbo.AreaCodes.[Area Prefix],
                    dbo.TCallLog_CO.Dialed_Number
                  ) = 1
                  OR CHARINDEX(
                    dbo.AreaCodes.[Area Prefix],
                    dbo.TCallLog_CO.Caller_Id
                  ) = 1
                  LEFT OUTER JOIN dbo.WebAccCodeName ON dbo.WebAccCodeName.[AccCode] = dbo.TCallLog_CO.Account_Code
                  LEFT OUTER JOIN dbo.WebDIDCodeName ON dbo.WebDIDCodeName.[DIDCode] = dbo.TCallLog_CO.[DDI/DID/MSN]
                  LEFT JOIN [QAHSQLNODE01\ITBM].ITBM.dbo.tps_user u ON dbo.TCallLog_Extension.Agent_Name = (
                    u.usr_firstname + ' ' + u.usr_lastname
                  )

                   

                  We have created a server link from our telephony DB back to LANDesks DB, in order for us to matchup the user accounts against the telephony rows, this can be seen in the join-

                   

                  LEFT JOIN [QAHSQLNODE01\ITBM].ITBM.dbo.tps_user u ON dbo.TCallLog_Extension.Agent_Name = (
                    u.usr_firstname + ' ' + u.usr_lastname
                  )

                   

                  This then allows us to display the ITBMUserGUID and the ITBMUserName in the View's output:-

                   

                  u.tps_guid AS [ITBMUserGUID],
                  u.tps_name AS [ITBMUserName]

                   

                  After creating this we then created a Generic Data Connection called RRCCS01, followed by a new connection type, select the view as the Table Name.

                   

                  Our overall aim is to display data for the current logged in user matching against the full name within the telephony records.

                   

                  This far i'm told this isn't possible without importing the data, and then linking the user object to the import object.

                   

                  Hope that makes sense?

                  • 6. Re: Query External Datasource with current user
                    Jamie Cannon ITSMMVPGroup

                    I'm trying some testing from my side to see what I can come up with.  I thought it would work against a data connection in the past, just have to test.

                    • 7. Re: Query External Datasource with current user
                      Rookie

                      Hi Jamie,

                       

                      Been a little while and I wondered if you had managed to test this at all?

                       

                      Regards

                      • 8. Re: Query External Datasource with current user
                        Jamie Cannon ITSMMVPGroup

                        I did and it does not have the option of "Is Me" or "Is Current User" on a data connection.  Must just be a native criteria within the tool only.