4 Replies Latest reply: Dec 21, 2011 3:17 PM by Hadyn Dawson RSS

    Trigger to add network log in information

    Jamie Cannon ITSMMVPGroup

      Hi everyone,

      I am really hoping someone out there has this information.  We are getting ready to make Self Service live but I need something HUGE!  I need a trigger that I can add to the database that will add a network log in.

       

      Currently our users use domain\firstinitiallastname to log in.  I have imported all of this data into Service Desk (domain name and SAM name) but I just need to be able to put the two together so I can populate the network login.  Does anyone have this trigger they'd be willing to share?

       

      The attributes that need to be joined are {_DomainName}\{Name}

       

      THANK YOU IN ADVANCE!!

      Jamie

        • 1. Re: Trigger to add network log in information
          JulianWigman Expert

          Hi,

           

          Personally I use a stored procedure to do this and schedule it to run out of hours as a sql agent job.   Came from the community or support in the first place and I tweaked to fit our solution.

           

          Julian

           

          -------------------

           

          USE [LDSD]
          GO
          /****** Object:  StoredProcedure [dbo].[sp_SetNetworkLogin]    Script Date: 11/29/2011 09:45:07 ******/
          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          CREATE procedure [dbo].[sp_SetNetworkLogin] AS
          begin

          declare @Name as varchar(255)
          declare @Domain as varchar(255)
          declare @guid uniqueidentifier
          declare @networkLogin as varchar(255)
          declare @count int
          declare @userType uniqueidentifier

              select @userType =  tps_guid FROM tps_user_type WHERE tps_name = 'EndUser'
          declare UserCursor cursor for
            SELECT tps_guid, tps_name, usr_domainname
            FROM tps_user
               where tps_user_type_guid = @userType
          open UserCursor

          fetch next from UserCursor into @guid, @Name, @Domain
          while (@@FETCH_STATUS = 0)
          begin
            set @networkLogin = @Domain + '\' + @Name
            set @count = ''
            select @count = count(*) from tps_user_network_login
             where tps_network_login = @networkLogin
              group by tps_network_login
            -- print 'User: '+@networkLogin+', count: '+convert(varchar(10),@count)

            if @count = '' and @networkLogin is not null
            begin
             -- uncomment the following line if you want to limit all users to 1 network login.
             -- delete from tps_user_network_login where tps_user_guid = @guid
             insert into tps_user_network_login (tps_guid,tps_user_guid,tps_network_login)
              values (newid(),@guid,@networkLogin)
            end
            else update tps_user_network_login set tps_user_guid = @guid where tps_network_login = @networkLogin

            fetch next from UserCursor into @guid, @Name, @Domain
          end

          close UserCursor
          deallocate UserCursor

          end

          • 2. Re: Trigger to add network log in information
            Expert

            Hi Jamie,

             

            In 7.4 it should also be possible to use a calculation and scheduled action to do this-

             

            netLogin = ""

            if User._DomainName != "":

            netLogin = User._DomainName + "\\" + User.Name

            return netLogin

             

            Add this to a BeforeSave calculation on a String(255) attribute then set up a data import to import from System.User to System.Network Login. Map Name from the source to Name under User on the Target and map the calc'd attribute on the source to Network Login on the target.

             

            Also, the double "\\" is necessary as BOO interprets this as a single \. Without "\\" you end up with DomainNameUserName instead of DomainName\UserName. The stored procedure works fine, and most people use this but I've found as calculations have improved I've tried to move more and more things away from database scripts and into scheduled actions etc in LANDesk.

             

            Cheers,

            Hadyn

            • 3. Re: Trigger to add network log in information
              JulianWigman Expert

              Trouble is that the calculation solution still needs an Import so not very real-time.

               

              J

              • 4. Re: Trigger to add network log in information
                Expert

                Hi Julian,

                 

                Fair point, although if you're adding a single user to the system it wouldn't be overly difficult to just click the Add Network Login action anyway, this is more of an option to sync with user imports from an external source.

                 

                If you wanted to do this real-time on creation of a user without having to hit the add network login action then yes a trigger is the only solution, but where possible I try to avoid triggers as these can cause problems when a database is upgraded.

                 

                Cheers,

                Hadyn

                 

                Edit: Getting back to Jamie's original question, if you wanted this to add a network login immediately upon creation of a user (manual or via import) then you could amend Julian's SP to be an Insert Trigger which would be the instant solution.

                 

                Message was edited by: Hadyn Dawson