To all of you who attended to my session at TechEd yesterday – thank you! It was a great experience and I hope you learned something that you can make use for in your Service Manager environment! Now, I promised to post all the queries and links here on the blog, so here we go.
SQL Queries related to History:
-- Get the BasedManagedEntityId for a particular Incident ------------------------------------------------------------ select BaseManagedEntityId, Name, LastModified from BaseManagedEntity where Name = 'IR12740' -- Get the whole history of this object ------------------------------------------------------------ select * from EntityChangeLog where EntityId = '2FD95F73-F940-E434-68AB-1ECDDA86D8D8' order by LastModified desc -- Get all Changes in that transaction ------------------------------------------------------------ select * from EntityChangeLog where EntityTransactionLogId = '181762' -- When and who did the update? ------------------------------------------------------------ select * from EntityTransactionLog where EntityTransactionLogId = '181762' -- Which properties were changed in a particular update? ------------------------------------------------------------ select * from MT_System$WorkItem$Incident_Log where EntityChangeLogId = '825955' -- Which properties has been changed on a certian object? ------------------------------------------------------------ select * from MT_System$WorkItem$Incident_Log where BaseManagedEntityId = '50494CAB-21D5-BFD8-7540-ADDD86D64239'
SQL Queries related to changing the AD connector batch sizes:
Remember! Changes directly to the database is NOT supported!
-- To retrieve the current batch size value for AD connector ------------------------------------------------------------ select DataName,DefaultBatchSize from lfx.datatable where DataName in ( 'AD_User_Out', 'AD_UserPreference_Out', 'v_AD_UserManager', 'AD_UserNotificationPoint_Out', 'AD_GroupNotificationPoint_Out', 'AD_Computer_Out', 'AD_Printer_Out', 'AD_Group_Out') -- To update batch size values for AD connector ------------------------------------------------------------ Update LFX.DataTable Set DefaultBatchSize = 1000 where DataName in ( 'AD_User_Out', 'AD_UserPreference_Out', 'v_AD_UserManager', 'AD_UserNotificationPoint_Out', 'AD_GroupNotificationPoint_Out', 'AD_Computer_Out', 'AD_Printer_Out', 'AD_Group_Out') -- To reset the batch sizes back to default values again ------------------------------------------------------------ Update LFX.DataTable Set DefaultBatchSize = 50 where DataName in ( 'AD_User_Out', 'AD_UserPreference_Out', 'v_AD_UserManager', 'AD_UserNotificationPoint_Out', 'AD_GroupNotificationPoint_Out', 'AD_Group_Out') Update LFX.DataTable Set DefaultBatchSize = 200 where DataName in ( 'AD_Computer_Out', 'AD_Printer_Out')
Useful links:
FirstAssigned Date override (incl. MP)
http://www.scsm.se/?p=853
How to manually execute WorkItem Grooming in Service Manager
http://blogs.technet.com/b/mihai/archive/2012/12/12/how-to-manually-execute-workitem-grooming-in-service-manager.aspx
Modifying the batchsizeof AD and SCCM connector
http://blogs.technet.com/b/mihai/archive/2013/08/14/tweaking-the-ad-and-cm-connectors-in-service-manager-2012.aspx
Implied Permissions (incl. MP)
http://blogs.technet.com/b/servicemanager/archive/2014/03/19/improving-ad-connector-performance.aspx
I’ve also attached the slide deck if you would like to take a look at the slides again.
