1 2 3 4 Previous Next 46 Replies Latest reply on Aug 24, 2016 4:01 AM by MarcelloCentineo

    Unlock Item query

    neil_p1000 Apprentice

      Hi All

       

      Does anybody know where the query for unlocking Incidents is held within the system. I want to sort it by Incident number to make t easier to Unlock calls as at present it seems to be ordered randomly making it hard to find calls.

       

      Thanks

      Neil

        • 1. Re: Unlock Item query
          jkhill Specialist

          The table is tps_lock_item.  A simple query might be:

          select user1.tps_title, lock1.*

          from dbo.tps_lock_item lock1

          join dbo.tps_user user1

          on lock1.tps_user_guid = user1.tps_guid

           

           

           

           

           

           

          • 2. Re: Unlock Item query
            smiddleton Expert

            I believe the Incident Unlock facility is hard coded into the application so you cannot change the column that the information is sorted by, someone from LANDesk can probably verify this.

             

            I can tell you that the incident locks are sorted by when the lock was created rather than the user or reference number which would actually have been useful.

             

            We ended up writing our own .NET app to query the table and allow for a lock to be deleted.

             

            Second major advantage to doing this was I could create a role and give this to team leaders and the app can do a check against the network login and check if the user exists in that role. So I have then been able to give unlock incident rights to non administrators.

             

            Hopefully I can retire this tool once LANDesk get around to giving us the ability to give privs to unlock and allow us to sort the incidents to be unlock in a way that we choose.

             

            Incident Unlocker.JPG

            • 3. Re: Unlock Item query
              Expert

              We did similar but with PHP.

               

              Any analyst can right-click on a locked call and select "Unlock Call".

               

              We use the built in functionality to pass the ID to a PHP webpage which runs the SQL to unlock the call.

               

              (We added a similar button to WebDesk via Javascript)

               

              Works very well.

               

              We have some 200 analysts so it was getting a bit busy unlocking things all the time!

               

              We also run an overnight script to clear all locks.

               

              Cheers - Adam.

              • 4. Re: Unlock Item query
                smiddleton Expert

                I also setup a SQL JOB to run every 30 minutes to clear an incident lock older than 45 mins.

                 

                We were having far too many occasions of analysts leaving themselves in an incident when they went on lunch or left for the day.

                 

                And as we are open 24/7 and didn't fancy a phone call at the weekend or evening just to unlock an incident it keeps the incidents from being locked longer than really needed as the main benefit is to notify that someone else is working on the incident and help to prevent them losing work after typing a long note just because the incident has changed status.

                • 5. Re: Unlock Item query
                  Apprentice

                  I would love to know how either of you guys accomplished this - I really really need my analysts to have the ability to unlock items or to have a sql query to run that would auto-unlock incidents after they have been open for 5mns w/no activity - any assistance would be great!  Thank you!

                  • 6. Re: Unlock Item query
                    jkhill Specialist

                    For what it's worth, you can also opt NOT to lock the incidents in the first place.  That's ultimately what we decided to do:  Object Designer.  Incident Management | Incident.  In the right wnidow, change the Locking policy to None.

                     

                    But if you do want to run a periodic job to delete old locks (say locks that have been open 45 minutes or more), try something like this:

                    delete from tps_lock_item

                    where tps_creation_datetime < dateadd(minute, -45, getdate())

                     

                    Note that I haven't tested the above.  Test it in your dev environment first if you can.  Just set up a SQL job to run that query once every fifteen minutes and you're good to go.

                    • 7. Re: Unlock Item query
                      Apprentice

                      Just out of curiosity - what are the cons of 'not locking' - if an end user and an analyst both have the same incident open at the same time and make changes?  And with that SQL script could we change the time to be like 5 mins?

                      • 8. Re: Unlock Item query
                        smiddleton Expert

                        The SQL job that clears the locks in our database is purely used for if incident locks occur during the night or at the weekend.

                         

                        It is worth noting that the background processing service also clears incident locks in the database it does this every 1 min where the lock is greater than 20mins old and where someones console session has crashed  and this left a lock in the database, what this doesn't cover is where the analyst left there machine on with an incident open and locked the PC. This is still a valid lock so the system won't clear it.

                         

                        Below is the stored procedure from our live database, running the below will create the stored procedure for you in the database you specify.

                         

                         

                        USE [ITBM]
                        GO
                        /****** Object:  StoredProcedure [dbo].[sp_IncLockClear]    Script Date: 09/15/2011 17:20:56 ******/
                        SET ANSI_NULLS ON
                        GO
                        SET QUOTED_IDENTIFIER ON
                        GO
                        -- =============================================
                        -- Author:          <Stephen Middleton>
                        -- Create date: <16/11/2010>
                        -- Description:     <Procedure to clear incident locks greater than 45 mins>
                        -- =============================================
                        CREATE PROCEDURE [dbo].[sp_IncLockClear]
                        AS
                        BEGIN
                        -- SET NOCOUNT ON added to prevent extra result sets from
                        -- interfering with SELECT statements.
                        SET NOCOUNT ON;
                            -- Insert statements for procedure here
                        DECLARE @LOCKCNT int
                        DECLARE @DATE30 DATETIME
                        SET @DATE30 = dateadd(minute, -45, getutcdate())
                        SELECT @LOCKCNT = COUNT(*) from tps_lock_item where tps_creation_datetime < @DATE30
                        PRINT 'There are ' + CONVERT(varchar(3),@LOCKCNT) + ' Incident locks to be cleared'
                        DELETE FROM tps_lock_item
                        WHERE tps_lock_item.tps_creation_datetime < @DATE30
                        END

                         

                        All you should need to do is change the first item in red to the name of your database, then set the second item in red to the time value you would like an incident lock to exist for.

                         

                        After this you will need to create a scheduled job on the SQL server that then runs the stored procedure at the frequency you would like.

                         

                        Theres a few more lines of code than strictly required for this to work, they are there so that when viewing the job history you can see how many locks it is clearing.

                         

                        As suggested you should try this out on a test/development database before applying to live and take a backup of your live database before running this

                         

                        Regards,

                        Steve

                        1 of 1 people found this helpful
                        • 9. Re: Unlock Item query
                          smiddleton Expert

                          Depends a bit on how you expect your analysts and end users to interact and how your analysts manage the incidents you get logged.

                           

                          For example if an incident is logged and the analyst is in it getting ready to update it or move it to another status and the end user opens the incident and adds a note.

                           

                          This could cause the analyst to receive a message telling them that another user has updated the incident and there changes will be lost.

                          • 10. Re: Unlock Item query
                            Apprentice

                            Yes, thats exactly what I am afraid of - so with the SQL script you have, will these clear out ALL locks, regardless of how they were locked?  Like if an analysts locks their pc and it was left open, or if an end user closes their brower w/out closing out of the incident window?

                            • 11. Re: Unlock Item query
                              smiddleton Expert

                              Thats exactly right.

                               

                              As I said though the background processing service will deal with certain locks, like the end user having just closed there browser and does so every 1 min where the lock is greater than 20 min, thats hard coded into it.

                               

                              I wouldn't recommend setting the stored procedure lower than 20 min and having it set to run every 10 mins, this should then keep locks below 30 mins.

                               

                              You need to give the analysts enough time to deal with the incident before clearing the lock or otherwise you can get the behaviour I described.

                               

                              At least thats what i have found.

                              • 12. Re: Unlock Item query
                                jkhill Specialist

                                Yes, these queries will delete all locks regardless of cause.

                                 

                                Regarding Stephen Middleton's comment that the background processing service clears incident locks, that hasn't been my experience, but perhaps there's a configuration setting that I would have needed to change to make it happen.  Before we turned off locking, we had 5-10 locks per day that were caused by console crashes and they never went away.  This was with 7.32 and 7.4.

                                 

                                As far as any support issues caused by turning the locking off, I've only found one:  if you have a complex process that depends a lot on e-mail, it's possible that two e-mails arriving within seconds of one another (which can happen since the inbound service only runs once a minute) can conflict, and one message will be rejected.  This triggers an error event in the application log that I scan for (well, I scan for all error events on my LDSD server) so I can mark the losing message as unread.  Support told me that there's a slight chance that if two people make conflicting changes and save at the same time then an error condition can develop, but they said they've only gotten two calls of that happening in the last few years.

                                 

                                Turning off locking has improved our experience enormously.  For one thing, we use e-mails to change the incident status (if the subject line contains .resolve, e.g., the ticket is moved to the Resolved status), and those actions were failing if someone had the ticket open when the e-mail came in.

                                • 13. Re: Unlock Item query
                                  smiddleton Expert

                                  For anyone interested I thought I would upload the tool I wrote to allow analysts to unlock there own calls and for certain people to unlock any incident.

                                   

                                  Had to pull my finger out and give it a config file so that you can get it to work with your installation, had just been lazy till now and left it hard coded.

                                   

                                  In the attached "Unlock Incident.zip" are 2 files.

                                  1) ITBMUnlock.exe

                                  2) ITBMUnlock.exe.config

                                   

                                  You will need to edit the .config and replace the "XXXXXXXXXXX" entries with values for your environment.

                                   

                                  I ended up publishing a shortcut on the shortcut bar in ITBM that they can launch it from.

                                   

                                  Hope it helps till LANDesk provide the unlock function as we desire it to be within ITBM.

                                   

                                  Message was edited by: Stephen Middleton Updated File Version

                                  • 14. Re: Unlock Item query
                                    Apprentice

                                    That is great!! Thank you for sharing : )

                                    1 2 3 4 Previous Next