Archive for: ‘May 2014’

DCIM-B308: Advanced Lessons learned from Implementing System Center 2012 R2 Service Manager

May 13, 2014 Posted by Anders Asp

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.

DCIM-B308