Orchestrator - Misrepresented and Misunderstood

Purpose:

1.       Talk about the misrepresentation of Orchestrator.

2.       Point out some things about Orchestrator that people don't quite understand.

3.       Convince people to give Orchestrator a try.

Summary:

System Center Orchestrator is a product in the System Center Suite. If you have heard of Orchestrator, you have probably heard it along with other buzzwords like "automation" and "cloud." This text is not about writing a cool runbook, automation, pitching cloud services, or discussing how System Center can solve all of the world's IT problems (it can though). I want to discuss a misconstrued view of Orchestrator that I continue to see over and over, which I think prevents IT Organizations from using it or trying it out.

I think that Orchestrator is both misrepresented and misunderstood.

What was my original perception of Orchestrator?

As an Operations Manager and Service Manager guy, I knew that I could build automation for nearly anything without ever relying on any products outside thoese two systems. I have been using Operations Manager to automate IT tasks, and perform cleanup as a result of alerts for years.

Service Manager provides the tools to take a ticket system and turn it into an automation machine using the authoring console, Powershell, and the native flexibility of the system.

So when Microsoft began touting Orchestrator, my first reaction was, “why in the world do I need that?” The simple answer is, I don't…

As a matter of fact, I have never had to use Orchestrator for anything…ever. I still haven't found a task, workflow, process, or anything else I couldn't complete by using the other System Center products that were already implemented, even when they were communicating outside the realm of Microsoft. (Thanks PowerShell!)

So, in a sense, Orchestrator almost seems like a novelty - an unnecessary System Center Product…but it's not.

Why use a complex system when a simple system is available?

Let's take a look at Operations Manager and Service Manager. We know they both can do just about anything that we want in terms of process and automation. When you get down deep, both products can get somewhat complicated. They both run complex operations on the back-end, which allows it to use a simple front-end. I mean, have you looked at the databases or stored procedures? My point is, there is a lot going on that we don't see. Even without much of anything configured, the systems are churning away.

The beauty in Orchestrator is its back-end simplicity. What is Orchestrator doing? Nothing. A little maintenance here or there, checking schedules, checking the clock, etc. What are the others doing? Constantly performing complex operations even at base load. And what are they doing when you begin automating your processes, performing remediation, or waiting for a criteria match? Even more. So much in fact, you can easily drop 40 GB of ram or more with several separated disks on the SQL server to maintain an acceptable level of performance while the systems are performing their operations.

So why are so many people taking such complex operations and stuffing them full of more complex operations creating a memory and disk eater, when we have this nice, little calculator waiting for its command? A nice little calculator that will do anything you tell it to do, and communicate with any machine you want to communicate with. Yet a lot of people still don't use it.

I think the answer is simply misrepresentation.

Everyone talks about all the cool, complex things Orchestrator can do, all the systems it can talk to, and all the integration packs that are available. It actually sounds kind of scary. I mean, who has time to do all that?

But, at its base, Orchestrator is not much more than a scheduling engine - a simple calculator.

"Simple" is what Orchestrator was meant to be all along. Take a complex operation and break it down into simple steps.

Do this…

o    Find something you would like to do – such as automation, remediation, communication between 2 systems. Whatever it is, start out with a goal.

o    Document the exact technical process that should happen on paper. If you can't write your process on paper, you can't write it with a computer.

o    Read through Kevin Holman's Orchestrator quick start guide.

§  http://blogs.technet.com/b/kevinholman/archive/2013/10/18/orchestrator-2012-r2-quickstart-deployment-guide.aspx

o    Take 15 minutes and install Orchestrator 2012 R2.

o    Take another 10 minutes to download and install the integration packs.

§  http://www.microsoft.com/en-us/download/details.aspx?id=39622

o    Create a Runbook

Remember this…

1.       You will stumble through your first runbook, but keep at it, it will get easier.

2.       The more complex operations you remove from other systems and enter into Orchestrator, the easier it will be to maintain, document, and transfer knowledge.

3.       Don't make it complicated. Don't write a giant PowerShell script and enter it into Orchestrator; this defeats the purpose of simplicity.  Break out your steps into multiple activities.

Query ALL Service Manager ENUMS and their Hierarchy

I find myself listing out all of the enumerations for lists in Service Manager quite a bit. Rather than spending time doing this over and over, I wrote a query that retrieves all of the enumeration items from Service Manager. I tried to keep it simple so anyone could adjust to his or her needs. It does not require the DW, as I am pulling directly from the ServiceManager database.

   

  SELECT [EnumType].[EnumTypeId] AS Id,
      [EnumType].[ManagementPackId] AS ManagementPackId,
      ep.EnumTypeName,
      [EnumType].[EnumTypeName] AS Name,
      [EnumType].[EnumTypeAccessibility] AS Accessibility,
      [EnumType].[ParentEnumTypeId] AS ParentId,
      DisplayName
  into #eview
  FROM dbo.EnumType
  LEFT Join dbo.EnumType ep on EnumType.EnumTypeId = ep.EnumTypeId and ep.ParentEnumTypeId IS NULL
  LEFT OUTER JOIN DisplayStringView DS1 ON DS1.LTStringId = dbo.[EnumType].[EnumTypeId] AND DS1.LanguageCode = 'ENU'
 
  INNER JOIN dbo.ManagementPack
   ON dbo.ManagementPack.ManagementPackId = [EnumType].ManagementPackId AND dbo.ManagementPack.ContentReadable = 1;
   
  with tree as (
  SELECT ManagementPackid, Id, name,
  cast(DisplayName as varchar(max)) as Hierarchy,
  DisplayName,
  ParentId
  FROM #eview
  Where ParentId IS NULL and displayName IS NOT NULL
  UNION ALL
  SELECT c.ManagementPackId, c.Id, c.name,
  p.hierarchy + ', ' + cast(c.DisplayName as varchar(max)),
  c.DisplayName, c.ParentId
  FROM #eview c
  join tree p on p.Id = c.parentID
  WHERE c.displayName IS NOT NULL
  )
select ManagementPackid, parentid, Name, Hierarchy, DisplayName
from tree
order by 3
drop table #eview

Get Parent Affected User for Notifications

This is more of a note for myself. For an activity, this will get the affected user of the parent work item.

$Context/Path[Relationship='CoreActivity!System.WorkItemContainsActivity' SeedRole='Target' TypeConstraint='WorkItem!System.WorkItem']/Path[Relationship='WorkItem!System.WorkItemAffectedUser' TypeConstraint='System!System.User']/Property[Type='System!System.User']/FirstName$

Obviously it is only the first name and the references will need to be changed to match the reference alias in the MP.

The is a great solution and worthy of a repost.

http://www.expiscornovus.com/2012/05/06/scsm2012-self-service-portal-service-category-color-customization/

SCSM 2012: Self Service Portal Service category color customization

1 Reply

Lately I’ve been wandering a bit more on the Technet Forums, this has been pretty useful. Friday I came across this thread from Bart Timmermans about customization in the Self Service Portal of the product System Center Service Manager 2012. He asked if it was possible to adjust the styling of Service category headers. Of course I accepted the challenge.

Analysis
The Self Service Portal is a solution on SharePoint 2010 which deploys some web parts. Like Travis Wright described in his latest Self Service Portal blogpost those web parts use Silverlight .xap files. After some .NET Reflector work on the Portal.BasicResources DLL. I found that a lot of the color styling for the portal is being done by using brushes.

Brushes
A brush is an Silverlight object which can be used to paint for example solid colors or linear gradient. In that DLL I found a .xaml file which defined some solidcolorbrushes and they had a key. In Silverlight they use a 8 digit notation for the color, this is a RGBA value.

Settings.xml
The Self Service Portal actually has a settings.xml file which can be used to define some basic settings. I noticed it also had some setting keys for colors. This triggered me to add a key for one of the brushes, ExpanderHeaderBgBrush. My attempt worked. After adjusting the Settings.xml and clearing my browsers cache I saw a new green color!

Solution
1. Go to C:\inetpub\wwwroot\System Center Service Manager Portal\ContentHost\Clientbin (or another location if your installation directory was different
2. Open the Settings.xml file
3. Add a setting key for ExpanderHeaderBgBrush with your desired RGBA color:

Happy customizing!

Isolating Powershell Sessions in Workflows in Service Manager

Problem:
One common issue I have ran into with writing workflows for Service Manager is the powershell sessions seem to be shared. When I call a set of cmdlets, such as the Active Directory cmdlets, they do not always load/unload properly, causing issues with subsequent scripts.

Solution:
We can isolate the powershell sessions inside the Service Manager Workflows. While powershell experts may know this, most of us don't, so here is my non-expert explanation.

• When you run Service Manager Workflows, they run in the same process. 
• If these workflows are running powershell, the powershell sessions are sometimes (or always) shared. 
• By "running powershell inside a powershell" we can isolate our scripts, preventing issues between shared sessions.
• Once the script is complete, it cleans itself up, and completely closes the sessions.

The only issue I see with this method is that it might take the workflow a second or two longer to run because of having to open the new session - plan accordingly.

How we do it:

Take your completed script, and simply wrap the script in powershell.

For example, if my script is: 

Import-Module Activedirectory

Get-User

I simply wrap it like this:

Powershell {

Import-Module Activedirectory

Get-User

}

Another Example with Parameters:
Powershell {
param($Name, $pcc);
get-process $Name -ComputerName $pcc;} -args "explorer", "localhost"

Thanks goes out to Thomas Bianco for coming up with this simple workaround as a way to isolate Powershell Sessions.

Additional information was retrieved from http://systemcenterblog.blogspot.com/2012/12/orchestrator-vs-powershell-i-win.html

Create and Assign Service Manager Incidents Directly from SCOM on Demand

The Issue

If you use Operations Manager and Service Manager, you know by now that SCOM will automatically create Incidents in Service Manager. However, for most organizations, this just doesn’t make sense because they do not have a 1-to-1 Alert-to-Action ratio. You can set up basic criteria to limit the automatic creation, but this usually still results in too many unnecessary incidents. As a result, most organizations do not utilize this connector, which at one point was one of the most requested features of SCOM – to do really cool things with ticketing systems.

The Solution

So, instead, I have created a solution that will allow you to create incidents on demand directly from a SCOM Alert, while utilizing all the cool features of the Service Manager SCOM Alert connector. All you have to do is right click the alert(s) to create the on-demand tickets.

What are some features of the solution in conjunction with the native Connector:

• Right click one more multiple alerts and assign incidents directly to the specified group/user
• Closing the alert closes the ticket and vice-versa
• The Assigned User and the Ticket Id are maintained in the alert as sourced from SCSM
• The affected component in SCOM is automatically added as a related configuration item in SCSM
• Easily can be extended to do more fun stuff with only basic PowerShell Knowledge

How Does it Work

The solution utilizes the following components:

1. SCOM and SCSM obviously
2. A very small PowerShell Script
3. SCOM CMDLETS

Workflow:

1. A user right clicks the alert and sets the resolution State.
2. A Command Subscription triggers based on the resolution state, sets a couple of custom fields, and changes the resolution state to “Generate Incident” a
3. The SCSM Alert connector triggers based on the new resolution state, generates an incident, and applies an incident template based on data in the custom fields.

How to Implement the Solution

These Steps need to be performed in SCOM

Step One

Copy the following PowerShell script code and save on your SCOM management server as UpdateCustomFieldPowershell.ps1. (I took this code from another blog online and modified it as my own. Unfortunately, I don’t know who wrote the original script.)

Param($alertid) 

$alertid = $alertid.toString()

write-eventlog -logname "Operations Manager" -source "Health Service Script" -eventID 1234 -entrytype "Information" -message "Running UpdateCustomFieldPowershell"

Import-Module OperationsManager; "C:\Program Files\System Center 2012\Operations Manager\Powershell\OperationsManager\Functions.ps1"; "C:\Program Files\System Center 2012\Operations Manager\Powershell\OperationsManager\Startup.ps1"

$alert = Get-SCOMAlert -Criteria "Id = '$alertid'"

write-host $alert

If ($alert.CustomField2 -ne "AlertProcessed")

    {

$AlertResState = (get-SCOMAlertResolutionState -ResolutionStateCode ($Alert.ResolutionState)).Name

$AlertResState

   # $alert.CustomField1 = $alert.NetBIOSComputerName

     $alert.CustomField1 = $AlertResState

     $alert.CustomField2 = "AlertProcessed"

$alert.ResolutionState  = 254

    $alert.Update("")

    }

exit

Step Two

We need to create some new alert resolution states. The alert resolution states will trigger the script. You want to create a resolution state for each support group you would assign an alert. You can use whatever format you want. I used the format of “Assign to GROUPNAME”. Also keep in mind the Resolution State Ids and order you will use. I made my alphabetical. DO NOT use the resolution state 0,1,254, or 255.

To create new resolution states:

• Go to the SCOM Console
• Go to the Administration Workspace
• Go to Settings
• Select Alerts
• Select the new button, create a resolution state and assign an Id. Resolution states will always be ordered by their Id
• Repeat for each resolution state

After you create your alert resolution states, you will need to create one more that triggers the SCSM Connect. Name this Alert Resolution State “Generate Incident.” Also, make sure this is the exact name as the script requires. If you want to change the name, you will have to update the script. Also, set the Id to 254.

Step Three

We need to set up a command channel and subscription that will trigger and run the script.

• Open the SCOM Console
• Go the the Administration Workspace
• Go to Channels
• Create a new Command Channel
• Enter the full path of the above script
• Enter the command line parameters as shown in the example below (Be sure the use the double and single quotes correctly)
  • "C:\OpsMgrProductionScripts\SCOMUpdateCustomField.ps1" '$Data/Context/DataItem/AlertId$'
• Enter the startup folder as C:\windows\system32\windowspowershell\v1.0\
• Save the new Channel

Next, we need to set up the subscriber for the command channel.

• Open the SCOM Console
• Go the the Administration Workspace
• Open subscribers
• Create a new subscriber
• In the addresses tab, click Add
• In the subscriber address, set the channel type to command and then select the channel you set up in the previous steps.
• Save the address and the subscriber

Next, we need to set up the Command Subscription

• Open the SCOM Console
• Go the the Administration Workspace
• Open Subscriptions
• Create a new Subscription
• On the subscription criteria, check the checkbox “with a specific resolution state”
• Select all the new resolution states except “Generate Incident” (Do not select anything other than the assignment states)
• On the subscribers, add the new subscriber you created in the previous steps
• On the Channels, add the new channel you created in the previous steps
• Save the subscription

Step Four

The last thing we have to do in SCOM is set up the Alert connector. The alert connector will be triggered based on the resolution status of “Generate Incident”.

• Open the SCOM Console
• Go the the Administration Workspace
• Go to connectors and select Internal Connectors
• Open the SCSM Alert Connector
• Create a new subscription in the connector
• In the criteria of the subscription

These Steps need to be performed in SCSM 

Step One

The first thing you want to do is enable and connect your SCSM SCOM Alert Connector. If you do not know how to do that, you can refer to technet. http://technet.microsoft.com/en-us/library/hh524325.aspx. Verify it works before moving any further.

Step Two

• Create a new Management Pack dedicated to storing the SCOM Incident Templates in SCSM
• Create a SCOM incident template for each group that you want to assign via SCOM. Typically, this is about 10-20 templates. For testing purposes, I would just start with one or two.
• Add the correct group as the assigned to in each template. It is not necessary to fill any other information.

Step Three

• In SCSM open the SCOM Alert Connector
• Go to the alert routing rules and add a new rule
  • For each rule select one of the templates that you created
  • On the select criteria type, select the Custom Field radio button
  • For custom field one, enter the exact name of the resolution state you used in SCOM. For example, if you are going to assign to the server team, and the name of resolution state is called “Assign to ServerTeam”, this is the exact phrase you need to enter into Custom Field one.
• Select Custom Field two from the drop down
• For custom field two, enter “AlertProcessed”
• Click OK
• Repeat for each template

Time for Testing! 

Now you are ready to test. Find an alert in SCOM, right click the alert and set it to a resolution state for assignment. Give the subscription time to run and the SCSM connector time to run. Usually, if the connector is running every 2 minutes, it takes the total process about 5 minutes to complete. While the actual workflows are running in a second, it simply takes time for both of them to trigger.

 

Troubleshooting

If there are any issues with the configuration, the event logs will usually tell you about failures. If it is not working, but you don’t see any failures, your criteria probably do not match.

Conclusion

This is a great alternative solution to automatically creating tickets from SCOM. You can still automatically create tickets as well simply by adding subscriptions to the SCSM SCOM Alert connector. If you have any issues, question, leave a comment.

Notes Regarding SCSM 2012 Upgrade

I just wanted to share some notes regarding the Service Manager 2012 SP1 Upgrade, that might not be obvious unless you thoroughly read the documentation. I hope these notes help prevent some problems.

Release Notes:
http://technet.microsoft.com/en-us/library/jj614520.aspx

The SCSM Console New Requirement:
Upgrade Note2-core 2.0 GHz CPU
4 GB of RAM
10 GB of available disk spaces
new requirement of Microsoft SQL Server 2012 Analysis Management Objects (AMO). Microsoft SQL Server 2012 AMO is supported on SQL Server 2008 and SQL Server 2012


Self-Service Portal: Web Content Server with SharePoint Web Parts
8-Core 2.66 GHz CPU
8-core, 64-bit CPU for medium deployments
16 GB of RAM for 20,000 users, 32 GB of RAM for 50,000 users (See the Hardware Performance section in this guide.)
80 GB of available hard disk space


When you upgrade from System Center 2012 – Service Manager, you perform an in-place upgrade of the Self-Service Portal. - This is the only thing the documentation says. I am not sure what it means.

Authoring Tool Workflows
When you use the Service Manager SP1 version of the Authoring tool to create a workflow, then custom scripts using Windows PowerShell cmdlets called by the workflow fail. This is due to a problem in the Service Manager MonitoringHost.exe.config file.

To work around this problem, update the MonitoringHost.exe.config XML file using the following steps.

1.     Navigate to %ProgramFiles%\Microsoft System Center 2012\Service Manager\ or the location where you installed Service Manager.

2.     Edit the MonitoringHost.exe.config file and add the section in italic type from the example below in the corresponding section of your file. You must insert the section before <publisherPolicy apply="yes" />.

3.     Save your changes to the file.

4.     Restart the System Center Management service on the Service Manager management server.

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="uri" type="System.Configuration.UriSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <uri>
    <iriParsing enabled="true" />
  </uri>  
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Mom.Modules.DataTypes" publicKeyToken="31bf3856ad364e35" />
        <publisherPolicy apply="no" />
        <bindingRedirect oldVersion="6.0.4900.0" newVersion="7.0.5000.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.EnterpriseManagement.HealthService.Modules.WorkflowFoundation" publicKeyToken="31bf3856ad364e35" />
        <publisherPolicy apply="no" />
        <bindingRedirect oldVersion="6.0.4900.0" newVersion="7.0.5000.0" />
      </dependentAssembly>
  <dependentAssembly> 
         <assemblyIdentity name="Microsoft.EnterpriseManagement.Modules.PowerShell" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="6.0.4900.0" newVersion="7.0.5000.0" />
     </dependentAssembly> 
      <publisherPolicy apply="yes" />
      <probing privatePath="" />
    </assemblyBinding>
    <gcConcurrent enabled="true" />
  </runtime>
</configuration>

SCOM Agent Supported in SCSM 2012 SP1

System Center 2012 – Operations Manager

System Center 2012 – Operations Manager agents were not supported with System Center 2012 – Service Manager. However, the agent that is automatically installed by System Center 2012 – Service Manager SP1 is compatible with System Center 2012 – Operations Manager and System Center 2012 – Operations Manager SP1.  After Service Manager Setup completes, you must manually configure the agent to communicate with the Operations Manager management server.

To validate that the Operations Manager Agent was installed, open Control Panel and verify that the Operations Manager Agent is present. To manually configure the Operations Manager agent, see Configuring Agents.

You can upgrade Service Manager servers in the presence of an System Center 2012 – Operations Manager console.

Source: MS Documentation

http://www.microsoft.com/en-us/download/details.aspx?id=27850

SCSM Cube Processing and Analysis Services is a Beast

If you are using the Service Manager DW and cubes, you may have ran into some issues with the cubes not processing, failing, data issue, or something else. I have provided a couple of resources to help with troubleshooting at the bottom of my post, but I want give a little insight on my experience in dealing with what could possibly become a maintenance headache.

Back story: I am currently working on a DEV and PRD SCSM 2012 RTM environment for a client. Each environment is up. DEV is being heavily used, but PRD is not. They have slightly different configurations, and each cube processing issue was resolved using two different methods.

Because development is non-impacting, after troubleshooting for a few hours and not being about to resolve the issue, i figured it was best to reinstall the DW. After uninstall, reinstall, and re-sync everything works ALL data intact.


DEV Steps:

1. Go into the console > Administration
2. Unregister the DW
3. On the DW Server, Go to Add/Remove Programs > Uninstall System Center 2012 Service Manager
1. If you get an error about a log not being found, do this:
1. Shift Right Click "Add/Remove Programs" and select run in a different process
4. After the uninstall, restart the machine
5. After the machine restarts, go into the registry of the DW Management Server and remove the following keys and all sub keys:
1. System Center
2. Microsoft Operations Manager
6. Restart the Machine again
7. Go REMOVE/DELETE the DW databases
8. Go REMOVE/DELETE the DW Analysis services database
9. On the DW Management Server, perform a fresh install, following the prompts and creating new databases.
10. Once the install is complete, re-register SCSM to the DW
11. Leave it Alone for 24 hours
12. After 24 hours check to see if all the jobs and cubes have processed

The steps above (for dev) were quicker than troubleshooting. Hope this helps.

PRD Steps - Actualy troubleshooting, not re-install

Analysis services is install on the DW server. I noticed in the event log, I was receiving event 33573. One of the events stated "The operation has been cancelled due to memory pressure." This seemed pretty obvious, so I opened task manager, attempted to process the cube, and noticed that it maxed out my 8GB of memory in a couple of minutes, then the memory utilization dropped. I checked the event log again, and I received the same error. So, I increased the memory 16 GB, and processed again - No More Memory Errors. 4 of the 6 cubes processed. I still have two that are failing, but not because of memory. You might need to increase your memory above 16GB depending on the number of work and config items.

After fixing the memory issue, I noticed the following events:

					Message : An Exception was encountered while trying to process a cube.  Cube Name: SystemCenterChangeAndActivityManagementCube Exception Message: An exception occurred while processing the cube.  Please see the event viewer log for more information.  Cube:  SystemCenterChangeAndActivityManagementCube Stack Trace:    at Microsoft.SystemCenter.Warehouse.Olap.OlapCube.Process(ManagementPackCube mpCube).
Warning	7/11/2012 7:53	Data Warehouse	33573	None	Message : An Exception was encountered while trying during cube processing.  Message=  Processing warning encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'ActivityAssignedToUser', Column: 'ActivityDimKey', Value: '2'. The attribute is 'ActivityDimKey'..
Warning	7/11/2012 7:53	Data Warehouse	33574	None	Message : Cube Processing workitem has failed. This is most likely caused by the DWDataMart (primary datamart) being out of sync from other marts. This is an intermittent problem and will resolve on its own as the Load jobs complete their runs. However, to work around this issue, administrators can manually start the Load.Common load job, wait for it to complete and then start the Cube processing job.
Error	7/11/2012 7:53	Data Warehouse	33573	None	Message : An Exception was encountered while trying during cube processing.  Message=  Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1054932986, Description: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation..       Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1054932978, Description: Errors in the OLAP storage engine: An error occurred while processing the 'ActivityAssignedToUser' partition of the 'ActivityAssignedToUser' measure group for the 'SystemCenterChangeAndActivityManagementCube' cube from the DWASDataBase database..       Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1054932986, Description: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation..       Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1056964601, Description: Internal error: The operation terminated unsuccessfully..       Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1055129598, Description: Server: The operation has been cancelled..

Exchange Connector 3.0 RC Released! - System Center: Service Manager Engineering Team Blog - Site Home - TechNet Blogs

Exchange Connector 3.0 RC Released! - System Center: Service Manager Engineering Team Blog - Site Home - TechNet Blogs

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:

• New Service Request
• 1 Review Activity

Do this:

• New Service Request
• 1 Review Activity
• 1 Manual Activity

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!

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."

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.

<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

Suppressing Duplicate SCSM Workflow Monitoring Alerts/Health State Changes in SCOM - SCSM Engineering Team Blog - Site Home - TechNet Blogs

Suppressing Duplicate SCSM Workflow Monitoring Alerts/Health State Changes in SCOM - SCSM Engineering Team Blog - Site Home - TechNet Blogs

Action Log, History, and Auditing in Service Manager - SCSM Engineering Team Blog - Site Home - TechNet Blogs

Action Log, History, and Auditing in Service Manager - SCSM Engineering Team Blog - Site Home - TechNet Blogs:

'via Blog this'

Microsoft System Center Suite: List current values for a list enumeration in Service Manager

Microsoft System Center Suite: List current values for a list enumeration in Service Manager:

'via Blog this'

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 

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