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}




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



          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.






          USE [LDSD]
          /****** Object:  StoredProcedure [dbo].[sp_SetNetworkLogin]    Script Date: 11/29/2011 09:45:07 ******/
          CREATE procedure [dbo].[sp_SetNetworkLogin] AS

          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)
            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
             -- 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)
            else update tps_user_network_login set tps_user_guid = @guid where tps_network_login = @networkLogin

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

          close UserCursor
          deallocate UserCursor


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

            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.




            • 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.



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

                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.





                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