Skip navigation
1622 Views 4 Replies Latest reply: Dec 21, 2011 3:17 PM by Hadyn Dawson RSS
Jamie Cannon ITSMMVPGroup 1,356 posts since
Feb 28, 2008

Has received 7 of 9 achievements.
Currently Being Moderated

Nov 28, 2011 8:16 AM

Trigger to add network log in information

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

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    1. Nov 29, 2011 2:30 AM (in response to Jamie Cannon)
    Re: Trigger to add network log in information

    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

  • Expert 233 posts since
    Jan 27, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    2. Dec 20, 2011 3:34 PM (in response to Jamie Cannon)
    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.

     

    Cheers,

    Hadyn

  • JulianWigman Expert 585 posts since
    Apr 24, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    3. Dec 21, 2011 1:25 AM (in response to Hadyn Dawson)
    Re: Trigger to add network log in information

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

     

    J

  • Expert 233 posts since
    Jan 27, 2009

    Has received 7 of 9 achievements.
    Currently Being Moderated
    4. Dec 21, 2011 3:17 PM (in response to JulianWigman)
    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.

     

    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

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 20 points
  • Helpful Answers - 10 points
LANDESK Community powered by Jive SBS® 4.5.7.1  |  Legal Notices  |  Privacy Policy  |  Icon 

TweeterOn Twitter  |  Icon FacebookOn Facebook © 2007 LANDESK Software