1 Reply Latest reply on Feb 14, 2019 6:19 AM by AlasdairRobertson

    Database view Query

    Dan_Mills Specialist

      Hi, I've the below request from one of our Developers, I'm not sure it's something that I'm able to do.

       

      If so, anyone know the script??

       

      Thanks

      Dan

       

       

       

      Can a database view be created in the Ivanti database with the following information? The database view should list only active user information. Same user should have multiple records if the user has multiple levels of access. The key data that would be required is:

       

      • Details that identify the user (First Name, Last Name, Full Name, User ID, E-mail Address)
      • Details that identify the access level i.e. what the user can do within the application

      o Access level names, e.g. Administrator, Self Service, Analyst, Change Manager, Configuration Manager, Problem Manager, License Manager etc.

        • 1. Re: Database view Query
          AlasdairRobertson ITSMMVPGroup

          HI Dan

           

          Scripting your own views in to the database is probably not supported.  You could probably add a Business object with some business rules which would create a similar object which is updated when you add/remove a role form an employee.

           

          If you want the SQL here it is:

          select
          e.FirstName,
          e.LastName,
          e.DisplayName,
          e.PrimaryEmail,
          e.LoginId,
          dr.DesktopName,
          dr.DisplayName
          from Frs_def_role_assignment ra
          Inner Join Employee e on ra.SourceID =  e.RecId
          Inner join Frs_def_role dr on ra.TargetID = dr.RecId
          Order by LoginID