Friday, April 20, 2012
Tuesday, March 27, 2012
SCSM Service Request - Don't use a Single Review Activity
In Service Manager, we are allowed to have activities in service requests, just like Changes and Incidents. However, don't enter a single activity which is also a review activity. It will not move to "In Progress."
If you create a Service Request or Service Request Template, and you decide to use activities, make sure that if you are going to have an approval (Review Activity), that you also have some type of Manual Activity associated with it. Otherwise, the workflow will run successfully, but it will not change the status from Pending. Now you are stuck, and will have to use Powershell to clean it up.
Don't do this:
If you create a Service Request or Service Request Template, and you decide to use activities, make sure that if you are going to have an approval (Review Activity), that you also have some type of Manual Activity associated with it. Otherwise, the workflow will run successfully, but it will not change the status from Pending. Now you are stuck, and will have to use Powershell to clean it up.
Don't do this:
- New Service Request
- 1 Review Activity
Do this:
- New Service Request
- 1 Review Activity
- 1 Manual Activity
Tuesday, February 14, 2012
SCSM 2012 - Affected User Populates Affected Item Selection
I don't know if many people have noticed this, because I sure didn't. Maybe I am not the most observant person. On the incident form, you see "Affected user CIs:". I have always seen it, but nothing ever "clicked." When you enter an affected user, it gives you a list of CIs for that User.
Assumptions:
You have SCSM 2012 Installed
You have your SCCM Connector Synchronized and it pulls workstation and user information.
You have AD Synchronized for users.
Open the Incident Form
Add an Affected User
Watch the Affected user CIs get populated.
NIICE!
Thursday, February 2, 2012
SCSM Find the Relationship GUID between two Work Items Components
I was recently editing a notification mangement pack and needed to find the GUID for a relationship between two instance so I could send an email to the correct user.
IN SCSM 2012 there is a cmdlet that can be used to find the name if a relationship called get-SCRelationship. However, it doesn't provide the GUID.
If you want to view all relationship types along with there GUID and other details, use this simple database query. Open SQL Management Studio and connect to your Service Manager Database.
Select *
FROM ServiceManager.dbo.RelationshipTypeView
where
LanguageCode = 'ENU'
and (
Name like '%incident%' OR
DisplayName like '%incident%' OR
TargetName like '%incident%' OR
SourceName like '%incident%'
)
Replace "Incident" with different details if needed. For example "PrimaryOwner."
IN SCSM 2012 there is a cmdlet that can be used to find the name if a relationship called get-SCRelationship. However, it doesn't provide the GUID.
If you want to view all relationship types along with there GUID and other details, use this simple database query. Open SQL Management Studio and connect to your Service Manager Database.
Select *
FROM ServiceManager.dbo.RelationshipTypeView
where
LanguageCode = 'ENU'
and (
Name like '%incident%' OR
DisplayName like '%incident%' OR
TargetName like '%incident%' OR
SourceName like '%incident%'
)
Replace "Incident" with different details if needed. For example "PrimaryOwner."
Tuesday, October 25, 2011
SCSM Workflow Date Time Token Error in the Authoring Console
The Authoring Tool workflow has an option of "relative" when using date fields. However, when you check the checkbox and enter a relative token such as [now] the authoring console doesn't like it and will not save. I haven't seen anyone else complain about this, so maybe it is just my environment. Either way, I had to find a workaround. It is pretty simple.
First what might happen is you are creating an on update trigger. You select a date field and select the relate checkbox.
You might be able to click okay, but later down the road when you save you might get this token error.
If this is happening to you, specify the expression that you want, except do not use "relative." For now, insert a static date. Save the management pack and open the xml. Go find your expression. You can see mine below.
Replace <Value>2011-09-25T05:00:00</Value> with <Token>[now]</Token>
That's it.
First what might happen is you are creating an on update trigger. You select a date field and select the relate checkbox.
You might be able to click okay, but later down the road when you save you might get this token error.
If this is happening to you, specify the expression that you want, except do not use "relative." For now, insert a static date. Save the management pack and open the xml. Go find your expression. You can see mine below.
<Expression> <SimpleExpression> <ValueExpression> <Property State="Post">$Context/Property[Type='CustomSystem_WorkItem_Library!System.WorkItem']/ScheduledStartDate$</Property> </ValueExpression> <Operator>LessEqual</Operator> <ValueExpression> <Value>2011-09-25T05:00:00</Value> </ValueExpression> </SimpleExpression> </Expression>
Replace <Value>2011-09-25T05:00:00</Value> with <Token>[now]</Token>
That's it.
SCSM Tokens
[me]
[mygroups]
[now] +|- # s|m|h|d
where:
s => seconds
m => minutes
h => hours
d => days
for example:
[now] + 2 h is now + 2 hours
[now ]+ 3 m is now + 3 minutes
[now] - 2 h is now - 2 hours
[mygroups]
[now] +|- # s|m|h|d
where:
s => seconds
m => minutes
h => hours
d => days
for example:
[now] + 2 h is now + 2 hours
[now ]+ 3 m is now + 3 minutes
[now] - 2 h is now - 2 hours
Monday, October 10, 2011
Monday, September 12, 2011
Quickly find the SCSM Management Group Name
I always assumed the SCSM management group name was in the title of the console until I actually needed one day, then I realized it wasn't there. It seems like you would see the Management Group name every where, but it is not. Use the registry on a SCSM server to find it.
Open regedit:
HKEY_LOCAL_Machine\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Server Management Groups
Open regedit:
HKEY_LOCAL_Machine\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Server Management Groups
Monday, August 29, 2011
Here are some useful SCSM DataMart Views
Create Announcements view from The CMDB
USE [DWDataMart]
GO
/****** Object: View [dbo].[Custom_Announcements] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[Custom_Announcements]
As
Select BaseManagedEntityId,
ExpirationDate_DDB905FF_A923_F97D_D00B_6430C2CD5E95 as Expiration,
P.DisplayName as Priority,
Title_A24FDDD9_5BC0_B3D0_E014_1F98C0B9143E Announcement,
Left(Substring(Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8,Charindex('\ltrch',Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8)+7,250),Charindex('}',Substring(Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8,Charindex('\ltrch',Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8)+7,250))-1) Body
FROM ServiceManager.dbo.MTV_System$Announcement$Item a
Join ServiceManager.dbo.DisplayStringView P on a.Priority_6986BA50_58CF_ABCA_FB58_8FCAB694E6C9 = P.LTStringId and LanguageCode = 'ENU'
GO
Review Activities
/****** Object: View [dbo].[Custom_ReviewActivityList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ReviewActivityList]
AS
Select
RA.Id + Coalesce(Cast(R.ReviewerDimKey as varchar(255)),'') As ActivityandReviewer_Key,
RA.BaseManagedEntityId as ActivityGUID,
RA.Id as ActivityId,
Cast('https://servicemanagerdev/analyst/ReviewActivityDetails.aspx?AID=' + RA.Id AS varchar(255)) ViewActivity,
RA.CreatedDate,
RA.Title,
DecString.DisplayName,
R.DecisionDate,
R.Comments as DecisionComments,
U.FirstName as ReviewerFirstName, U.LastName as ReviewerLastName, U.DisplayName as ReviewerDisplayName, U.UserName as ReviewerUserName,
WI.BaseManagedEntityId as ParentWorkItemGUId,
WI.Id as ParentWorkItemId,
WI.Title as ParentWorkitemTitle,
Cast('https://servicemanagerdev/analyst/ChangeRequestDetails.aspx?CRID=' + WI.Id AS varchar(255)) ViewParent
FROM ActivityDimvw A
Join ReviewActivityDimvw RA on A.BaseManagedEntityId = RA.BaseManagedEntityId
Left Join ReviewActivityhasReviewerFactvw RARF on A.ActivityDimKey = RARF.ActivityDimKey
LEFT Join ReviewerDimvw R on RARF.ReviewActivityHasReviewer_ReviewerDimKey = R.ReviewerDimKey
LEFT Join ReviewerDecisionvw RD ON R.Decision_ReviewerDecisionId = RD.ReviewerDecisionId
Left Join dbo.DisplayStringDimvw as DecString on RD.EnumTypeId = DecString.BaseManagedEntityId and DecString.LanguageCode = 'ENU'
LEFT Join ReviewerIsUserFactvw RU on R.ReviewerDimKey = RU.ReviewerDimKey
LEFT Join UserDimvw U on RU.ReviewerIsUser_UserDimKey = U.UserDimKey
Left Join ActivityAreavw AA With (NOLOCK) on RA.Area_ActivityAreaId= AA.ActivityAreaId
Left Join dbo.DisplayStringDimvw as AAString on AA.EnumTypeId = AAString.BaseManagedEntityId and AAString.LanguageCode = 'ENU'
Left Join ActivityPriorityvw AP With (NOLOCK) on RA.Priority_ActivityPriorityId = AP.ActivityPriorityId
Left Join dbo.DisplayStringDimvw as APString on AP.EnumTypeId = APString.BaseManagedEntityId and APString.LanguageCode = 'ENU'
Left Join ActivityStagevw AStage With (NOLOCK) on RA.Stage_ActivityStageId = AStage.ActivityStageId
Left Join dbo.DisplayStringDimvw as AStageString on AStage.EnumTypeId = AStageString.BaseManagedEntityId and AStageString.LanguageCode = 'ENU'
Left Join ActivityStatusvw AStatus With (NOLOCK) on RA.Status_ActivityStatusId = AStatus.ActivityStatusId
Left Join dbo.DisplayStringDimvw as AStatusString on AStatus.EnumTypeId = AStatusString.BaseManagedEntityId and AStatusString.LanguageCode = 'ENU'
Left Join WorkItemContainsActivityFactvw WICA on A.ActivityDimKey = WICA.WorkItemContainsActivity_ActivityDimKey
Left Join WorkItemDimvw WI on WICA.WorkItemDimKey = WI.WorkItemDimKey
GO
Incidents
/****** Object: View [dbo].[Custom_IncidentList_with_HL] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_IncidentList_with_HL]
AS
Select
I.BaseManagedEntityId as IncidentGUID,
I.Id as IncidentId,
'https://servicemanagerdev/enduser/RequestDetails.aspx?RequestsId=' + Convert(varchar(50),I.BaseManagedEntityId) + '&RequestType=incident'
AS IncidentLink,
I.CreatedDate,
IncidentStatusString.DisplayName as 'Status',
IncidentClassString.DisplayName as 'Classification',
I.Title, I.Description, Escalated,
IncidentImpactString.DisplayName as Impact,
IncidentUrgencyString.DisplayName as Urgency,
Priority,
IncidentResCatString.DisplayName as ResolutionCategory,
ResolutionDescription,
TargetResolutionTime,
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.IncidentDimvw I With (NOLOCK)
Join WorkItemDimvw WI With (NOLOCK) on I.BaseManagedEntityId = WI.BaseManagedEntityId
LEFT Join IncidentStatusvw S With (NOLOCK) on I.Status_IncidentStatusId = s.IncidentStatusId
LEFT Join dbo.DisplayStringDimvw as IncidentStatusString on S.EnumTypeId = IncidentStatusString.BaseManagedEntityId and IncidentStatusString.LanguageCode = 'ENU'
LEFT Join IncidentClassificationvw C on I.Classification_IncidentClassificationId = C.IncidentClassificationId
LEFT Join dbo.DisplayStringDimvw as IncidentClassString on C.EnumTypeId = IncidentClassString.BaseManagedEntityId and IncidentClassString.LanguageCode = 'ENU'
LEFT Join IncidentImpactvw Impact on I.Impact_IncidentImpactId = Impact.IncidentImpactId
LEFT Join dbo.DisplayStringDimvw as IncidentImpactString on Impact.EnumTypeId = IncidentImpactString.BaseManagedEntityId and IncidentImpactString.LanguageCode = 'ENU'
LEFT Join IncidentUrgencyvw Urgency on I.Urgency_IncidentUrgencyId = Urgency.IncidentUrgencyId
LEFT Join dbo.DisplayStringDimvw as IncidentUrgencyString on Urgency.EnumTypeId = IncidentUrgencyString.BaseManagedEntityId and IncidentUrgencyString.LanguageCode = 'ENU'
LEFT Join IncidentResolutionCategoryvw ResCat on I.ResolutionCategory_IncidentResolutionCategoryId = ResCat.IncidentResolutionCategoryId
LEFT Join dbo.DisplayStringDimvw as IncidentResCatString on ResCat.EnumTypeId = IncidentResCatString.BaseManagedEntityId and IncidentResCatString.LanguageCode = 'ENU'
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Where 1=1
and Status <> ('IncidentStatusEnum.Closed')
GO
Another Incident List without a Hyperlink
/****** Object: View [dbo].[Custom_IncidentList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_IncidentList]
AS
Select
I.BaseManagedEntityId as IncidentGUID,
I.Id as IncidentId,
I.CreatedDate,
IncidentStatusString.DisplayName as 'Status',
IncidentClassString.DisplayName as 'Classification',
I.Title, I.Description, Escalated,
IncidentImpactString.DisplayName as Impact,
IncidentUrgencyString.DisplayName as Urgency,
Priority,
IncidentResCatString.DisplayName as ResolutionCategory,
ResolutionDescription,
TargetResolutionTime,
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.IncidentDimvw I With (NOLOCK)
Join WorkItemDimvw WI With (NOLOCK) on I.BaseManagedEntityId = WI.BaseManagedEntityId
LEFT Join IncidentStatusvw S With (NOLOCK) on I.Status_IncidentStatusId = s.IncidentStatusId
LEFT Join dbo.DisplayStringDimvw as IncidentStatusString on S.EnumTypeId = IncidentStatusString.BaseManagedEntityId and IncidentStatusString.LanguageCode = 'ENU'
LEFT Join IncidentClassificationvw C on I.Classification_IncidentClassificationId = C.IncidentClassificationId
LEFT Join dbo.DisplayStringDimvw as IncidentClassString on C.EnumTypeId = IncidentClassString.BaseManagedEntityId and IncidentClassString.LanguageCode = 'ENU'
LEFT Join IncidentImpactvw Impact on I.Impact_IncidentImpactId = Impact.IncidentImpactId
LEFT Join dbo.DisplayStringDimvw as IncidentImpactString on Impact.EnumTypeId = IncidentImpactString.BaseManagedEntityId and IncidentImpactString.LanguageCode = 'ENU'
LEFT Join IncidentUrgencyvw Urgency on I.Urgency_IncidentUrgencyId = Urgency.IncidentUrgencyId
LEFT Join dbo.DisplayStringDimvw as IncidentUrgencyString on Urgency.EnumTypeId = IncidentUrgencyString.BaseManagedEntityId and IncidentUrgencyString.LanguageCode = 'ENU'
LEFT Join IncidentResolutionCategoryvw ResCat on I.ResolutionCategory_IncidentResolutionCategoryId = ResCat.IncidentResolutionCategoryId
LEFT Join dbo.DisplayStringDimvw as IncidentResCatString on ResCat.EnumTypeId = IncidentResCatString.BaseManagedEntityId and IncidentResCatString.LanguageCode = 'ENU'
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Where 1=1
--and I.Id = 'IR124'
--and (
-- AffectedUser.UserName = 'Brody_Kilpatrick' OR
-- Assigneduser.UserName = 'Brody_Kilpatrick' OR
-- CreatedByUser.UserName = 'Brody_Kilpatrick'
-- )
and Status <> ('IncidentStatusEnum.Closed')
GO
Change Requests
/****** Object: View [dbo].[Custom_ChangeList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ChangeList]
AS
Select
CR.BaseManagedEntityId ChangeGUID,
CR.ID ChangeRequestId,
'https://servicemanagerdev/enduser/RequestDetails.aspx?RequestsId=' + Convert(varchar(50),CR.BaseManagedEntityId) + '&RequestType=changeRequest'
AS EndUserPortalLink,
'https://servicemanagerdev/analyst/ChangeRequestDetails.aspx?CRID=' + CONVERT(varchar(50),CR.ID)
AS AnalystPortalLink,
CR.ActualStartDate,
CR.ActualEndDate,
CR.CreatedDate,
CR.Description,
CR.Notes,
CR.PostImplementationReview,
CR.Reason,
CR.RequiredByDate,
CR.Title,
CAString.DisplayName Area,
CCString.DisplayName Category,
CIString.DisplayName Impact,
CImpString.DisplayName ImplementationResults,
CPString.DisplayName Priority,
CRiskString.DisplayName Risk,
CSString.DisplayName 'Status',
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.ChangeRequestDimvw CR
Join dbo.WorkItemDimvw WI on CR.BaseManagedEntityId = WI.BaseManagedEntityId
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Left Join ChangeAreavw CA With (NOLOCK) on CR.Area_ChangeAreaId = CA.ChangeAreaId
Left Join dbo.DisplayStringDimvw as CAString on CA.EnumTypeId = CAString.BaseManagedEntityId and CAString.LanguageCode = 'ENU'
Left Join ChangeCategoryvw CC With (NOLOCK) on CR.Category_ChangeCategoryId = CC.ChangeCategoryId
Left Join dbo.DisplayStringDimvw as CCString on CC.EnumTypeId = CCString.BaseManagedEntityId and CCString.LanguageCode = 'ENU'
Left Join ChangeImpactvw CI With (NOLOCK) on CR.Impact_ChangeImpactId = CI.ChangeImpactId
Left Join dbo.DisplayStringDimvw as CIString on CI.EnumTypeId = CIString.BaseManagedEntityId and CIString.LanguageCode = 'ENU'
Left Join ChangeImplementationResultsvw CImp With (NOLOCK) on CR.ImplementationResults_ChangeImplementationResultsId = CImp.ChangeImplementationResultsId
Left Join dbo.DisplayStringDimvw as CImpString on CImp.EnumTypeId = CImpString.BaseManagedEntityId and CImpString.LanguageCode = 'ENU'
Left Join ChangePriorityvw CP With (NOLOCK) on CR.Priority_ChangePriorityId = CP.ChangePriorityId
Left Join dbo.DisplayStringDimvw as CPString on CP.EnumTypeId = CPString.BaseManagedEntityId and CPString.LanguageCode = 'ENU'
Left Join ChangeRiskvw CRisk With (NOLOCK) on CR.Risk_ChangeRiskId = CRisk.ChangeRiskId
Left Join dbo.DisplayStringDimvw as CRiskString on CRisk.EnumTypeId = CRiskString.BaseManagedEntityId and CRiskString.LanguageCode = 'ENU'
Left Join ChangeStatusvw CS With (NOLOCK) on CR.Status_ChangeStatusId = CS.ChangeStatusId
Left Join dbo.DisplayStringDimvw as CSString on CS.EnumTypeId = CSString.BaseManagedEntityId and CSString.LanguageCode = 'ENU'
GO
All Activity List
/****** Object: View [dbo].[Custom_ActivityList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ActivityList]
AS
Select
A.BaseManagedEntityId ActivityGUID,
A.Id ActivityId,
Case When ra.ActivityId IS NULL
Then 'https://servicemanagerdev/analyst/ManualActivityDetails.aspx?AID=' + CONVERT(varchar(50),A.Id)
Else 'https://servicemanagerdev/analyst/ReviewActivityDetails.aspx?AID=' + CONVERT(varchar(50),A.Id)
End ActivityLink,
A.Title,
ActualStartDate,
ActualEndDate,
A.CreatedDate,
A.Description,
A.Notes,
ScheduledStartDate,
ScheduledEndDate,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName,
AAString.DisplayName Area,
APString.DisplayName Priority,
AStageString.DisplayName Stage,
AStatusString.DisplayName 'Status'
FROM dbo.ActivityDimvw A
Join WorkItemDimvw WI on A.BaseManagedEntityId = WI.BaseManagedEntityId
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Left Join ActivityAreavw AA With (NOLOCK) on A.Area_ActivityAreaId= AA.ActivityAreaId
Left Join dbo.DisplayStringDimvw as AAString on AA.EnumTypeId = AAString.BaseManagedEntityId and AAString.LanguageCode = 'ENU'
Left Join ActivityPriorityvw AP With (NOLOCK) on A.Priority_ActivityPriorityId = AP.ActivityPriorityId
Left Join dbo.DisplayStringDimvw as APString on AP.EnumTypeId = APString.BaseManagedEntityId and APString.LanguageCode = 'ENU'
Left Join ActivityStagevw AStage With (NOLOCK) on A.Stage_ActivityStageId = AStage.ActivityStageId
Left Join dbo.DisplayStringDimvw as AStageString on AStage.EnumTypeId = AStageString.BaseManagedEntityId and AStageString.LanguageCode = 'ENU'
Left Join ActivityStatusvw AStatus With (NOLOCK) on A.Status_ActivityStatusId = AStatus.ActivityStatusId
Left Join dbo.DisplayStringDimvw as AStatusString on AStatus.EnumTypeId = AStatusString.BaseManagedEntityId and AStatusString.LanguageCode = 'ENU'
Left Join Custom_ReviewActivityList ra on A.Id = ra.ActivityId
GO
USE [DWDataMart]
GO
/****** Object: View [dbo].[Custom_Announcements] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[Custom_Announcements]
As
Select BaseManagedEntityId,
ExpirationDate_DDB905FF_A923_F97D_D00B_6430C2CD5E95 as Expiration,
P.DisplayName as Priority,
Title_A24FDDD9_5BC0_B3D0_E014_1F98C0B9143E Announcement,
Left(Substring(Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8,Charindex('\ltrch',Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8)+7,250),Charindex('}',Substring(Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8,Charindex('\ltrch',Body_3D226BC1_0528_14EA_F3FB_3823A132B4F8)+7,250))-1) Body
FROM ServiceManager.dbo.MTV_System$Announcement$Item a
Join ServiceManager.dbo.DisplayStringView P on a.Priority_6986BA50_58CF_ABCA_FB58_8FCAB694E6C9 = P.LTStringId and LanguageCode = 'ENU'
GO
Review Activities
/****** Object: View [dbo].[Custom_ReviewActivityList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ReviewActivityList]
AS
Select
RA.Id + Coalesce(Cast(R.ReviewerDimKey as varchar(255)),'') As ActivityandReviewer_Key,
RA.BaseManagedEntityId as ActivityGUID,
RA.Id as ActivityId,
Cast('https://servicemanagerdev/analyst/ReviewActivityDetails.aspx?AID=' + RA.Id AS varchar(255)) ViewActivity,
RA.CreatedDate,
RA.Title,
DecString.DisplayName,
R.DecisionDate,
R.Comments as DecisionComments,
U.FirstName as ReviewerFirstName, U.LastName as ReviewerLastName, U.DisplayName as ReviewerDisplayName, U.UserName as ReviewerUserName,
WI.BaseManagedEntityId as ParentWorkItemGUId,
WI.Id as ParentWorkItemId,
WI.Title as ParentWorkitemTitle,
Cast('https://servicemanagerdev/analyst/ChangeRequestDetails.aspx?CRID=' + WI.Id AS varchar(255)) ViewParent
FROM ActivityDimvw A
Join ReviewActivityDimvw RA on A.BaseManagedEntityId = RA.BaseManagedEntityId
Left Join ReviewActivityhasReviewerFactvw RARF on A.ActivityDimKey = RARF.ActivityDimKey
LEFT Join ReviewerDimvw R on RARF.ReviewActivityHasReviewer_ReviewerDimKey = R.ReviewerDimKey
LEFT Join ReviewerDecisionvw RD ON R.Decision_ReviewerDecisionId = RD.ReviewerDecisionId
Left Join dbo.DisplayStringDimvw as DecString on RD.EnumTypeId = DecString.BaseManagedEntityId and DecString.LanguageCode = 'ENU'
LEFT Join ReviewerIsUserFactvw RU on R.ReviewerDimKey = RU.ReviewerDimKey
LEFT Join UserDimvw U on RU.ReviewerIsUser_UserDimKey = U.UserDimKey
Left Join ActivityAreavw AA With (NOLOCK) on RA.Area_ActivityAreaId= AA.ActivityAreaId
Left Join dbo.DisplayStringDimvw as AAString on AA.EnumTypeId = AAString.BaseManagedEntityId and AAString.LanguageCode = 'ENU'
Left Join ActivityPriorityvw AP With (NOLOCK) on RA.Priority_ActivityPriorityId = AP.ActivityPriorityId
Left Join dbo.DisplayStringDimvw as APString on AP.EnumTypeId = APString.BaseManagedEntityId and APString.LanguageCode = 'ENU'
Left Join ActivityStagevw AStage With (NOLOCK) on RA.Stage_ActivityStageId = AStage.ActivityStageId
Left Join dbo.DisplayStringDimvw as AStageString on AStage.EnumTypeId = AStageString.BaseManagedEntityId and AStageString.LanguageCode = 'ENU'
Left Join ActivityStatusvw AStatus With (NOLOCK) on RA.Status_ActivityStatusId = AStatus.ActivityStatusId
Left Join dbo.DisplayStringDimvw as AStatusString on AStatus.EnumTypeId = AStatusString.BaseManagedEntityId and AStatusString.LanguageCode = 'ENU'
Left Join WorkItemContainsActivityFactvw WICA on A.ActivityDimKey = WICA.WorkItemContainsActivity_ActivityDimKey
Left Join WorkItemDimvw WI on WICA.WorkItemDimKey = WI.WorkItemDimKey
GO
Incidents
/****** Object: View [dbo].[Custom_IncidentList_with_HL] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_IncidentList_with_HL]
AS
Select
I.BaseManagedEntityId as IncidentGUID,
I.Id as IncidentId,
'https://servicemanagerdev/enduser/RequestDetails.aspx?RequestsId=' + Convert(varchar(50),I.BaseManagedEntityId) + '&RequestType=incident'
AS IncidentLink,
I.CreatedDate,
IncidentStatusString.DisplayName as 'Status',
IncidentClassString.DisplayName as 'Classification',
I.Title, I.Description, Escalated,
IncidentImpactString.DisplayName as Impact,
IncidentUrgencyString.DisplayName as Urgency,
Priority,
IncidentResCatString.DisplayName as ResolutionCategory,
ResolutionDescription,
TargetResolutionTime,
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.IncidentDimvw I With (NOLOCK)
Join WorkItemDimvw WI With (NOLOCK) on I.BaseManagedEntityId = WI.BaseManagedEntityId
LEFT Join IncidentStatusvw S With (NOLOCK) on I.Status_IncidentStatusId = s.IncidentStatusId
LEFT Join dbo.DisplayStringDimvw as IncidentStatusString on S.EnumTypeId = IncidentStatusString.BaseManagedEntityId and IncidentStatusString.LanguageCode = 'ENU'
LEFT Join IncidentClassificationvw C on I.Classification_IncidentClassificationId = C.IncidentClassificationId
LEFT Join dbo.DisplayStringDimvw as IncidentClassString on C.EnumTypeId = IncidentClassString.BaseManagedEntityId and IncidentClassString.LanguageCode = 'ENU'
LEFT Join IncidentImpactvw Impact on I.Impact_IncidentImpactId = Impact.IncidentImpactId
LEFT Join dbo.DisplayStringDimvw as IncidentImpactString on Impact.EnumTypeId = IncidentImpactString.BaseManagedEntityId and IncidentImpactString.LanguageCode = 'ENU'
LEFT Join IncidentUrgencyvw Urgency on I.Urgency_IncidentUrgencyId = Urgency.IncidentUrgencyId
LEFT Join dbo.DisplayStringDimvw as IncidentUrgencyString on Urgency.EnumTypeId = IncidentUrgencyString.BaseManagedEntityId and IncidentUrgencyString.LanguageCode = 'ENU'
LEFT Join IncidentResolutionCategoryvw ResCat on I.ResolutionCategory_IncidentResolutionCategoryId = ResCat.IncidentResolutionCategoryId
LEFT Join dbo.DisplayStringDimvw as IncidentResCatString on ResCat.EnumTypeId = IncidentResCatString.BaseManagedEntityId and IncidentResCatString.LanguageCode = 'ENU'
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Where 1=1
and Status <> ('IncidentStatusEnum.Closed')
GO
Another Incident List without a Hyperlink
/****** Object: View [dbo].[Custom_IncidentList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_IncidentList]
AS
Select
I.BaseManagedEntityId as IncidentGUID,
I.Id as IncidentId,
I.CreatedDate,
IncidentStatusString.DisplayName as 'Status',
IncidentClassString.DisplayName as 'Classification',
I.Title, I.Description, Escalated,
IncidentImpactString.DisplayName as Impact,
IncidentUrgencyString.DisplayName as Urgency,
Priority,
IncidentResCatString.DisplayName as ResolutionCategory,
ResolutionDescription,
TargetResolutionTime,
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.IncidentDimvw I With (NOLOCK)
Join WorkItemDimvw WI With (NOLOCK) on I.BaseManagedEntityId = WI.BaseManagedEntityId
LEFT Join IncidentStatusvw S With (NOLOCK) on I.Status_IncidentStatusId = s.IncidentStatusId
LEFT Join dbo.DisplayStringDimvw as IncidentStatusString on S.EnumTypeId = IncidentStatusString.BaseManagedEntityId and IncidentStatusString.LanguageCode = 'ENU'
LEFT Join IncidentClassificationvw C on I.Classification_IncidentClassificationId = C.IncidentClassificationId
LEFT Join dbo.DisplayStringDimvw as IncidentClassString on C.EnumTypeId = IncidentClassString.BaseManagedEntityId and IncidentClassString.LanguageCode = 'ENU'
LEFT Join IncidentImpactvw Impact on I.Impact_IncidentImpactId = Impact.IncidentImpactId
LEFT Join dbo.DisplayStringDimvw as IncidentImpactString on Impact.EnumTypeId = IncidentImpactString.BaseManagedEntityId and IncidentImpactString.LanguageCode = 'ENU'
LEFT Join IncidentUrgencyvw Urgency on I.Urgency_IncidentUrgencyId = Urgency.IncidentUrgencyId
LEFT Join dbo.DisplayStringDimvw as IncidentUrgencyString on Urgency.EnumTypeId = IncidentUrgencyString.BaseManagedEntityId and IncidentUrgencyString.LanguageCode = 'ENU'
LEFT Join IncidentResolutionCategoryvw ResCat on I.ResolutionCategory_IncidentResolutionCategoryId = ResCat.IncidentResolutionCategoryId
LEFT Join dbo.DisplayStringDimvw as IncidentResCatString on ResCat.EnumTypeId = IncidentResCatString.BaseManagedEntityId and IncidentResCatString.LanguageCode = 'ENU'
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Where 1=1
--and I.Id = 'IR124'
--and (
-- AffectedUser.UserName = 'Brody_Kilpatrick' OR
-- Assigneduser.UserName = 'Brody_Kilpatrick' OR
-- CreatedByUser.UserName = 'Brody_Kilpatrick'
-- )
and Status <> ('IncidentStatusEnum.Closed')
GO
Change Requests
/****** Object: View [dbo].[Custom_ChangeList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ChangeList]
AS
Select
CR.BaseManagedEntityId ChangeGUID,
CR.ID ChangeRequestId,
'https://servicemanagerdev/enduser/RequestDetails.aspx?RequestsId=' + Convert(varchar(50),CR.BaseManagedEntityId) + '&RequestType=changeRequest'
AS EndUserPortalLink,
'https://servicemanagerdev/analyst/ChangeRequestDetails.aspx?CRID=' + CONVERT(varchar(50),CR.ID)
AS AnalystPortalLink,
CR.ActualStartDate,
CR.ActualEndDate,
CR.CreatedDate,
CR.Description,
CR.Notes,
CR.PostImplementationReview,
CR.Reason,
CR.RequiredByDate,
CR.Title,
CAString.DisplayName Area,
CCString.DisplayName Category,
CIString.DisplayName Impact,
CImpString.DisplayName ImplementationResults,
CPString.DisplayName Priority,
CRiskString.DisplayName Risk,
CSString.DisplayName 'Status',
AffectedUser.FirstName as AffectedUserFirstName, AffectedUser.LastName as AffectedUserLastName, AffectedUser.DisplayName as AffectedUserDisplayName, AffectedUser.UserName as AffectedUserName,
ContactMethod as AffectedUserContactMethod,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName
FROM dbo.ChangeRequestDimvw CR
Join dbo.WorkItemDimvw WI on CR.BaseManagedEntityId = WI.BaseManagedEntityId
Left Join WorkItemAffectedUserFactvw WIAF on WI.WorkItemDimKey = WIAF.WorkItemDimKey and WIAF.DeletedDate IS NULL
Left Join UserDimvw AffectedUser on WIAF.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Left Join ChangeAreavw CA With (NOLOCK) on CR.Area_ChangeAreaId = CA.ChangeAreaId
Left Join dbo.DisplayStringDimvw as CAString on CA.EnumTypeId = CAString.BaseManagedEntityId and CAString.LanguageCode = 'ENU'
Left Join ChangeCategoryvw CC With (NOLOCK) on CR.Category_ChangeCategoryId = CC.ChangeCategoryId
Left Join dbo.DisplayStringDimvw as CCString on CC.EnumTypeId = CCString.BaseManagedEntityId and CCString.LanguageCode = 'ENU'
Left Join ChangeImpactvw CI With (NOLOCK) on CR.Impact_ChangeImpactId = CI.ChangeImpactId
Left Join dbo.DisplayStringDimvw as CIString on CI.EnumTypeId = CIString.BaseManagedEntityId and CIString.LanguageCode = 'ENU'
Left Join ChangeImplementationResultsvw CImp With (NOLOCK) on CR.ImplementationResults_ChangeImplementationResultsId = CImp.ChangeImplementationResultsId
Left Join dbo.DisplayStringDimvw as CImpString on CImp.EnumTypeId = CImpString.BaseManagedEntityId and CImpString.LanguageCode = 'ENU'
Left Join ChangePriorityvw CP With (NOLOCK) on CR.Priority_ChangePriorityId = CP.ChangePriorityId
Left Join dbo.DisplayStringDimvw as CPString on CP.EnumTypeId = CPString.BaseManagedEntityId and CPString.LanguageCode = 'ENU'
Left Join ChangeRiskvw CRisk With (NOLOCK) on CR.Risk_ChangeRiskId = CRisk.ChangeRiskId
Left Join dbo.DisplayStringDimvw as CRiskString on CRisk.EnumTypeId = CRiskString.BaseManagedEntityId and CRiskString.LanguageCode = 'ENU'
Left Join ChangeStatusvw CS With (NOLOCK) on CR.Status_ChangeStatusId = CS.ChangeStatusId
Left Join dbo.DisplayStringDimvw as CSString on CS.EnumTypeId = CSString.BaseManagedEntityId and CSString.LanguageCode = 'ENU'
GO
All Activity List
/****** Object: View [dbo].[Custom_ActivityList] Script Date: 08/29/2011 13:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Custom_ActivityList]
AS
Select
A.BaseManagedEntityId ActivityGUID,
A.Id ActivityId,
Case When ra.ActivityId IS NULL
Then 'https://servicemanagerdev/analyst/ManualActivityDetails.aspx?AID=' + CONVERT(varchar(50),A.Id)
Else 'https://servicemanagerdev/analyst/ReviewActivityDetails.aspx?AID=' + CONVERT(varchar(50),A.Id)
End ActivityLink,
A.Title,
ActualStartDate,
ActualEndDate,
A.CreatedDate,
A.Description,
A.Notes,
ScheduledStartDate,
ScheduledEndDate,
AssignedUser.FirstName as AssignedUserFirstName, AssignedUser.LastName as AssignedUserLastName, AssignedUser.DisplayName as AssignedUserDisplayName, AssignedUser.UserName as AssignedUserName,
CreatedByUser.FirstName as CreatedByUserFirstName, CreatedByUser.LastName as CreatedByUserLastName, CreatedByUser.DisplayName as CreatedByUserDisplayName, CreatedByUser.UserName as CreatedByUserName,
AAString.DisplayName Area,
APString.DisplayName Priority,
AStageString.DisplayName Stage,
AStatusString.DisplayName 'Status'
FROM dbo.ActivityDimvw A
Join WorkItemDimvw WI on A.BaseManagedEntityId = WI.BaseManagedEntityId
Left Join WorkItemAssignedToUserFactvw WIAT on WI.WorkItemDimKey = WIAT.WorkItemDimKey and WIAT.DeletedDate IS NULL
Left Join UserDimvw AssignedUser on WIAT.WorkItemAssignedToUser_UserDimKey = AssignedUser.UserDimKey
Left Join WorkItemCreatedByUserFactvw WICB on WI.WorkItemDimKey = WICB.WorkItemDimKey and WICB.DeletedDate IS NULL
Left Join UserDimvw CreatedByUser on WICB.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Left Join ActivityAreavw AA With (NOLOCK) on A.Area_ActivityAreaId= AA.ActivityAreaId
Left Join dbo.DisplayStringDimvw as AAString on AA.EnumTypeId = AAString.BaseManagedEntityId and AAString.LanguageCode = 'ENU'
Left Join ActivityPriorityvw AP With (NOLOCK) on A.Priority_ActivityPriorityId = AP.ActivityPriorityId
Left Join dbo.DisplayStringDimvw as APString on AP.EnumTypeId = APString.BaseManagedEntityId and APString.LanguageCode = 'ENU'
Left Join ActivityStagevw AStage With (NOLOCK) on A.Stage_ActivityStageId = AStage.ActivityStageId
Left Join dbo.DisplayStringDimvw as AStageString on AStage.EnumTypeId = AStageString.BaseManagedEntityId and AStageString.LanguageCode = 'ENU'
Left Join ActivityStatusvw AStatus With (NOLOCK) on A.Status_ActivityStatusId = AStatus.ActivityStatusId
Left Join dbo.DisplayStringDimvw as AStatusString on AStatus.EnumTypeId = AStatusString.BaseManagedEntityId and AStatusString.LanguageCode = 'ENU'
Left Join Custom_ReviewActivityList ra on A.Id = ra.ActivityId
GO
Tuesday, August 16, 2011
Saturday, August 6, 2011
Friday, August 5, 2011
Backing up Management packs when they're modified
(Courtesy of my Co-worker and friend Thomas Bianco)
So i'm warping up the SCSM project at my client, and one of the value add deliverables i wanted to include was an automatic way to backup their configuration in case they modifiy it later and break something. googling around i found This script to backup management packs, but that's not really what I wanted to do. Some quick modifications and I ended up with this script:
Import-module SMLets
powershell.exe -command "& 'C:\Windows\System32\WindowsPowerShell\v1.0\Examples\Backup-SCSMUnsealedMPs.ps1' "
The bonus on this is that the backup will only be created if any pack was changed today, and you get a nice sorted tree by year\month\date-day
So i'm warping up the SCSM project at my client, and one of the value add deliverables i wanted to include was an automatic way to backup their configuration in case they modifiy it later and break something. googling around i found This script to backup management packs, but that's not really what I wanted to do. Some quick modifications and I ended up with this script:
Import-module SMLets
#discover if anything is modified today
$today = Get-Date("{0} 00:00:00" -f (get-date).ToShortDateString());
if (Get-SCSMManagementPack | where-object {$_.LastModified -ge $today}) {
#Inscope Definitions
$OutPutDir = "C:\Management Packs\UnsealedBackups\";
$UnsealedMPs = Get-SCSMManagementPack | ?{ ! $_.Sealed };
[string]$CurrentDate = Get-Date -uformat "%Y\%m\%d-%A";
$CompletePath = ($OutPutDir + $CurrentDate);
if ( ! (test-path $CompletePath)) {
$output = New-Item -Type Directory -Name $CurrentDate -Path $OutPutDir;
};
$UnsealedMPs | Foreach-Object {
" Exporting: {0}" -f $_.Name;
$_ | Export-SCSMManagementPack -targetdirectory "$CompletePath";
};
};
Remove-module SMLets -force;
Then i scheduled a task to run it every night at 9:00 PM Local time.powershell.exe -command "& 'C:\Windows\System32\WindowsPowerShell\v1.0\Examples\Backup-SCSMUnsealedMPs.ps1' "
The bonus on this is that the backup will only be created if any pack was changed today, and you get a nice sorted tree by year\month\date-day
Monday, July 25, 2011
Wednesday, July 20, 2011
Four SCSM Self-Service Portal Solutions (certificate, redirect, alias)
When accessing the Self-Service Portal, typing https://servername/enduser into the browser address isn't acceptable for most organizations. This blog posts addresses 4 concerns of the self-service portal and how to resolve them. The information may be common to many IT Professionals; however, it was not common to me, as I have rarely been required to set up IIS sites in the past. I must also mention and thank the source for most of this information, Thomas Bianco, who has challenged me with (I think uncommon) Service Manager questions and answers.
1. Creating a DNS entry for your Self-Service portal
2. Properly creating a certificate to access the Self-Service Portal
3. Automatically redirecting to the end-user portal when typing in that alias
4. Preventing the need to type in https in front of the site name
For each of these steps, we are going to assume the following:
Self-Service Portal Server Name: SCSM01
Website Name we want to use: ServiceManager.domain.local
To test, simply type servicemanager into your browser. You should be redirected.
1. Creating a DNS entry for your Self-Service portal
2. Properly creating a certificate to access the Self-Service Portal
3. Automatically redirecting to the end-user portal when typing in that alias
4. Preventing the need to type in https in front of the site name
For each of these steps, we are going to assume the following:
Self-Service Portal Server Name: SCSM01
Website Name we want to use: ServiceManager.domain.local
1. Creating a DNS entry for your Self-Service portal
- Log into DNS for your Active Directory Domain
- Go to Forward Lookup Zones
- Select you domain
- Right click on your domain
- Select New Alias (CNAME)
- In the Alias Name box type "ServiceManager" (Without the quotes)
- In the Fully Qualified Domain Box Type "ServiceManager.domain.local" (Without the quotes)
- Click Ok
- To test, Browse to https://servicemanager/enduser
- You will still receive a certificate error, but should be able to click on to the site
2. Properly creating a certificate to access the Self-Service Portal
- Log onto you SCSM Self-Service Portal Server
- Start > Run > MMC > File > Add/Remove Snap-in
- In the list of snap-ins, select Certificates
- Click Add
- A window should pop up stating "This snap-in will always maange certificates for:"
- Select Computer Account > Next
- Select "Local Computer" > Next
- Finish
- Ok
- Expand Certificate (Local Computer) > Personal > Certificates
- Right Click > Request New Certificate
- Select the Certificate Enrollment Policy
- Next
- On the enrollment policy click the link labeled "More information is required to enroll for this certificate. Click here to configure settings."
- On the Subject tab, on the dropdown box under Type: Populate each field and add them as necessary
- Under Alternative name, under Type: Select DNS
- Type in all the names that could be used to access the SSP. For example servicemanager, servicemanager.domain.local, SCSM01, SCSM01.domain.local
- Click Add
- Select the General Tab and populate the fields
- Select the Private Key Tab > Key Options Check the box "Make private key exportable
- Select the Certificate Authority Tab
- Select the correct certificate authority for your organization
- OK
- Back on the Certificate Enrollment Window check the "Web Server" box
- Click Enroll
Update the SSP with the new certificate
- Go to IIS Manager
- Select SCSMPortal
- Select Bindings
- On the HTTPS binding, click edit
- In the SSL certificate box, select you new certificate
- Click OK
- Click Close
The Certificate Error on the Self-Service Portal should no longer exist.
3. Automatically redirecting to the end-user portal when typing in that alias
- Open IIS Manager
- Select SCSMPortal
- Double Click HTTP Redirect (If HTTP Redirect in not installed, go to Roles, Add Features, Select HTTP Redirect)
- Check the box "Redirect requests to this destination"
- Type in "enduser\" (Without the quotes)
- Under redirect behavior make sure "Redirect all requests to exact destination" is UNCHECKED
- Under redirect behavior make sure "Only redirect requests to content in the directory" is CHECKED
- In the Status Code Select "Found (302)"
- Click Apply
- Go to the Analyst and enduser vitual directories under SCSMPortal
- Select HTTP Redirect
- Make sure "redirect requests to this destination" is UNCHECKED
- To Test type in https://servicemanager
- You should automatically be redirected to https://servicemanager/enduser
4. Preventing the need to type in https in front of the site name
From Thomas:
[A Client] had a requirement to silently redirect users from their HTTP support site to the new HTTPS. It took me a few hours, but I found out an easy way to do this without redirecting to an absolute path that would break outside access through the firewall. First, I’m using the same redirect we worked out at [Client], then adding in the URL rewrite add-in from http://www.iis.net/download/URLRewrite. The HTTP redirect moves users who hit the website root down to the enduser virtual directory, and the rewrite directives moves connections from HTTP to HTTPS. The only downside is that people going to the root with HTTP get redirected twice, which takes about 1-4 seconds...Feel free to steal this for your blog.
Here's how:
- Open IIS Manager
- Select SCSMPortal
- Select bindings
- Click Add
- Type: http
- IP address: All unassigned
- Port: 80
- Host Name: Leave Blank
- Click OK
- If you get a warning about Port 80 being using on the default website, you may want to stop the default website or remove the binding from the default website
- Click Close
- Install the URL Rewrite utility from http://www.iis.net/download/URLRewrite
- Browse to the directory of the SCSM Portal
- Backup the web.config file
- Open the Web.config file
- Paste in the following and save the web.config file:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<httpRedirect enabled="true" destination="enduser/" childOnly="true" />
<rewrite>
<rules>
<rule name="HTTP to HTTPS redirect" stopProcessing="true">
<match url="(.*)" /> <!-- Require SSL must be OFF in the site settings -->
<conditions>
<add input="{HTTPS}" pattern="off" ignoreCase="true" />
</conditions>
<action type="Redirect" redirectType="Found" url="https://{HTTP_HOST}{REQUEST_URI}" />
</rule>
</rules>
</rewrite>
</system.webServer>
</configuration>
To test, simply type servicemanager into your browser. You should be redirected.
Monday, July 18, 2011
Showing the Correct Display Name for Related Objects in View Columns - SCSM Engineering Team Blog - Site Home - TechNet Blogs
Showing the Correct Display Name for Related Objects in View Columns - SCSM Engineering Team Blog - Site Home - TechNet Blogs: "Showing the Correct Display Name for Related Objects in View Columns"
Monday, July 11, 2011
Changing SCSM Portal Address
Great article on changing the SCSM portal Address
http://memoexp.wordpress.com/2011/03/30/changing-scsm-portal-address/
http://memoexp.wordpress.com/2011/03/30/changing-scsm-portal-address/
Ever have a customer request that they want the Self Service Portal to be a specific address, different from the one you’ve initially setup? Well, its not possible to change the URL. However what you can do is set up a CNAME in your DNS to accomplish this goal. Here is how to do it.
1. Go to the DC. Open up DNS under Administrative Tools. Under the DC, expand Forward Lookup Zones, then right click the folder with the domain name, select New Alias (CNAME).
2. Under Alias Name, type in the portal URL that you want. Then under target host, drill down and browse to your portal host name, which in my case is SCSM. Click Ok.
3. Go to the server which the Portal is installed. Launch IIS Manager, go to the Portal Sites, in my case, SCSMPortal, click onBindings on the right panel.
4. Click Add.
5. Select Type as https, IP address as All Unassigned, Port as 443. Then select the SSL certificate. Click Ok.
6. While highlighting your portal on the left pane, in my case SCSMPortal, Double click HTTP Redirect.
Note! HTTP Redirect is not installed by default for IIS7. To install it, go to Server Manager > Roles > Web Server (IIS), look for Add Role Services, you’ll find that you will have the option to install HTTP Redirection.
7. Check Redirect requests to this destination. Key in the full address of the Portal Site, click Apply on the right pane. If you’re not sure, its https:// + Full Computer Name + /enduser. So for my case it is https://scsm.systemcenter.local/enduser
(To get your Full Computer Name, Right click My Computer > Properties. You’ll see it there.)
8. Browse to the new URL which in my case is https://servicemanager, the portal should launch without problems.
P/s If you’re getting password prompts when using the new URL, then highlight the portal on the left pane, and double click onAuthentication. Select Basic Authentication and click on Enable on the right pane. Try using the new URL again, you shouldn’t get any prompts now.
Subscribe to:
Posts (Atom)