SQL View to pull Change Request Report in SCSM 2012, which can be integrated to QlikView (BI reporting tool)

Hello Folks,

Many times we used to think, why cant we have a separate view in SQL (DWDatamart) in SCSM dw db to pull all information on CR and Incident, as we do in reports.

Reports doesn't provide feasibility to integrate with some third party BI tools such as QlikView. But all the BI tools will support input from SQL DB directly.

In order to get wonderful reports from other BI reporting tools, we must create Views in SQL to pull all data.

I have created a View for CR and working on Incident request view to populate all data in a BI tool.

Below is the view for CR,which will pull all the information as the report does. To avoid duplication, we have added Distinct when selecting the columns. Also we have added on "Assigned to" user "Is Deleted = Null" to avoid getting multiple rows for the view.


CREATE VIEW [dbo].[Qlikview_ChangeRequest] AS

Select distinct changerequest.ID
    , changeRequest.Title
   ,
   changerequest.ChangeRequestDimKey,
   changerequest.CreatedDate,
   changerequest.ScheduledStartDate,
   changerequest.ScheduledEndDate,
   changerequest.ActualStartDate,
   changerequest.ActualEndDate,
     ISNULL(AssignedToUserDim.DisplayName, AssignedToUserDim.UserName) AS AssignedToUser,
    ISNULL(CreatedByUserDim.DisplayName, CreatedbyuserDim.UserName) AS CreatedByUser,
        PriorityEnumDS.DisplayName AS 'Priority',
ImpactEnumDS.DisplayName AS 'Impact',
ISNULL(StatusEnumDS.DisplayName, StatusEnum.ChangeStatusValue) AS Status,
AreaEnumDS.DisplayName AS 'Area',
ISNULL(CategoryEnumDs.DisplayName, CategoryEnum.ChangeCategoryValue) AS Category

    
from    ChangeRequestDimvw changerequest 
Left Outer Join    WorkItemDimvw workitem on changerequest.EntityDimKey = workitem.EntityDimKey 

Left Outer Join WorkItemAssignedToUserFactvw WIATU ON
Workitem.WorkItemDimKey = WIATU.WorkItemDimKey AND WIATU.DeletedDate IS NULL
Left Outer Join    UserDimvw AS AssignedToUserDim ON 
WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey 

Left Outer Join    WorkItemCreatedByUserFactvw WICBU ON
Workitem.WorkItemDimKey = WICBU.WorkItemDimKey
Left Outer Join    UserDimvw AS CreatedByUserDim ON 
WICBU.WorkItemCreatedByUser_UserDimKey = CreatedByUserDim.UserDimKey 


Left Outer Join    WorkItemAboutConfigItemFactvw AS WIACI ON
        WIACI.WorkItemDimKey = WorkItem.WorkItemDimKey

Left Outer Join ChangePriorityvw AS PriorityEnum ON 
PriorityEnum.ChangePriorityId = Changerequest.Priority_ChangePriorityId 
Left Outer Join    DisplayStringDimvw AS PriorityEnumDS ON
PriorityEnum.EnumTypeId = PriorityEnumDS.BaseManagedEntityId AND PriorityEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeStatusvw AS StatusEnum ON 
StatusEnum.ChangeStatusId = Changerequest.Status_ChangeStatusId
Left Outer Join DisplayStringDimvw AS StatusEnumDS ON
StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId AND StatusEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeAreavw AS AreaEnum ON 
AreaEnum.ChangeAreaId = Changerequest.Area_ChangeAreaId 
Left Outer Join DisplayStringDimvw AS AreaEnumDS ON
AreaEnum.EnumTypeId = AreaEnumDS.BaseManagedEntityId AND AreaEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeImpactvw AS ImpactEnum ON 
ImpactEnum.ChangeImpactId = Changerequest.Impact_ChangeImpactId
Left Outer Join DisplayStringDimvw AS ImpactEnumDS ON
ImpactEnum.EnumTypeId = ImpactEnumDS.BaseManagedEntityId AND ImpactEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeCategoryvw AS CategoryEnum ON 
CategoryEnum.ChangeCategoryId = ChangeRequest.Category_ChangeCategoryId 

Left Outer Join DisplayStringDimvw AS CategoryEnumDS ON
CategoryEnum.EnumTypeId = CategoryEnumDS.BaseManagedEntityId AND CategoryEnumDS.LanguageCode = 'ENU'



GO


Thanks,
Dinesh

Role Based Access in SCCM 2012

http://powershelldistrict.com/wp-content/uploads/2014/09/rba-matrix.jpg

One stop shop for all ur SCCM 2012 search

http://social.technet.microsoft.com/wiki/contents/articles/7075.system-center-2012-configuration-manager-survival-guide.aspx?CommentPosted=true

All POWERSHELL Scripts !!!!!!

• ConfigMgr 2007 (SCCM)
• DPM 2007
• DPM 2010
• OpsMgr 2007 (SCOM)
• SCE 2007 / 2010 (Essentials) 
• SCVMM / Hyper-V
• Service Manager (SCSM) 2010 / 2012
• Miscellaneous

ConfigMgr 2007 (SCCM)

• Using Powershell with Configuration Manager 2007 (Part 1)
• Using Powershell with Configuration Manager 2007: Part 2 – Manipulating Client Behavior
• How to list and query for installed hotfixes with PowerShell
• PowerShell to change ConfigMgr Client Cache size
• SCCM 2007: How to monitor inboxes in Configuration Manager 2007 using Powershell 
• Enumerate members of a collection with PowerShell
• ConfigMgr 2007: Creating a query-based collection with PowerShell (added Sep 2011)
• Cloud: Dynamic creation of ConfigMgr collection hierarchy with query-based collections via PowerShell (added Sep 2011)
• PowerShell: ConfigMgr 2007 – Create Maintenance Window (added Dec 2011)
• SCCM 2007 : Get Service Window with PowerShell / WMI (added Dec 2011)
• PowerShell Community Extensions for PowerShell (added Dec 2011)
• How to list ConfigMgr package structure in PowerShell (added Dec 2011)
• Count of client / manafacturer models in PowerShell (added Dec 2011)

 Top of Page
DPM 2007 (DPM 2010/2012 Compatible)

• SQL Recovery Point Restore Script for SCDPM
• CLI Script: Manual detailed inventory of unknown tapes
• CLI Script: Run consistency check on invalid datasources
• CLI Script: Force mark tape as free
• CLI Script: Recover to latest point in time for a SQL DB
• CLI Script: Copy latest point in time of SQL DB to a folder
• DPM CLI: Quick reference help
• DPM CLI Scripts
• CLI: Grow your replica and shadow copy volume sizes automatically!
• CLI Script: Job object fundamentals
• CLI: Create new Recovery points on tape for all datasources in a PG
• CLI script: Create protection groups for Disk based backups
• DPM CLI> Hello World
• DPM CLI Tips 'n Tricks: Powershell Basics
• DPM CLI Tips 'n Tricks: Powershell Basics - Part II
• DPM PowerShell Script -- invoking a Consistency Check
• DPM 2007 Share Restore Recovery Point Script
• DPM 2007 Individual File Share Recovery Point Script
• SQL Self Service Recovery using DPM 2010

Top of Page
OpsMgr 2007 (SCOM)

• Export Icons from All SCOM Management Packs (added Feb 2011)
• Uninstall Multiple Management Packs including Relationships - PowerShell Script
• OpsMgr: Finding Grey Agents with Powershell
• Most Common Alerts Report (rule and monitor generated) with Powershell
• New-OpsMgrRequest and Install-OpsMgrCertificate for SCOM 2007 (added Aug 2010)
• Reset Monitor in SCOM 2007 (added Aug 2010)
• Retrieve Instance of a Monitor in a Critical State (added Aug 2010)
• Dump Overrides to a CSV File (added Aug 2010)
• OpsMgr: Managing Global Settings from PowerShell
• OpsMgr Powershell: Reset monitoring state of closed alerts in SCOM
• A PowerShell script for resetting state of "manual reset" monitors
• Powershell: Get View display properties from UserRole
• OpsMgr: Recurring Agent Proxy Bulk Update for a Group of Computers
• Working with Active Alerts in OpsMgr 2007
• Powershell Tip: Removing Discovery Data in Operations Manager 2007 SP1
• How to create a PowerShell task for OpsMgr using the Authoring Console
• Powershell: OpsMgr Alert-to-Speech (cool idea from Gary Broadwater ...
• Automatically Starting Maintenance Mode When Servers are Rebooted
• Put any object type in maintenance mode in OpsMgr using Powershell 
• Resolve Alerts Older Than X Days in SCOM 2007
• Get-IsAlertMonitor and other one-liners for OpsMgr (added Aug 2010)
• OpsMgr: Exporting override details into a readable Overrides Report with PowerShell (added Aug 2010)  
• OpsMgr: How to execute an OpsMgr 2007 task from PowerShell (added Oct 2011)
• How to create a two state script monitor using PowerShell. Here's part 1: http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/44971/Default.aspx

Automating Agent Discovery and Failover Configuration

• How to Schedule OpsMgr Discovery and Agent Deployment without AD
• How to automate daily discovery and agent installation in OpsMgr
• OpsMgr 2007: Updating Primary and Failover Mgmt Server settings
• Powershell: Updating Proxy Agent for Multiple SNMP-enabled Devices ...
• Powershell: Updating Proxy Agent for Multiple SNMP-enabled Devices (part 2) (added Nov 2010) 

OpsMgr Groups

• Group members via PowerShell
• Create OpsMgr Groups in PowerShell from Excel File
• OpsMgr: Listing all OpsMgr groups for which a computer is a member

Series: Exploring OpsMgr Class Lineage

• Part 1 - OpsMgr 2007: Exploring Class Lineage in Powershell
• Part 2 - OpsMgr 2007 - Exploring Class Lineage in Powershell: Recursive Enumeration
• Part 3 - OpsMgr 2007 - Exploring Class Lineage in Powershell: Relationships
• Part 4 - OpsMgr 2007 - Exploring Class Lineage in Powershell: Recursive Enumeration of Classes and Relationships
• Part 5: OpsMgr 2007 and Powershell and the SDK – What MP does this class come from?

MP Authoring and SDK
Series: How to create two-state monitors in OpsMgr with R2 PowerShell modules

• How to create a Two-State PowerShell Script Monitor using the Authoring Console (Part 1)
• How to create a Two-State PowerShell Script Monitor using the Authoring Console (Part 2)
• How to create a Two-State PowerShell Script Monitor using the Authoring Console (Part 3)
• How to create a Two-State PowerShell Script Monitor using the Authoring Console (Part 4)

Series: How to create two-state monitors in OpsMgr with R2 PowerShell modules

• Part 1: Intro
• Part 2: Service Model Design
• Part 3: Discoveries with PowerShell
• Part 4: SCOM Performance data from PowerShell Scripts

Additional Authoring and SDK

• How to create a PowerShell Script Discovery including On-Demand Discovery Task using the Authoring Console – Act I
• Generic Performance Data Connector using .csv files
• Eventcollection with Floodingprevention
• Discovery Write Action Modules for On-Demand Discovery Agent Tasks
• Create Performance Rules from CSV: (added Aug 2010)
• Create Overrides in Bulk (added Aug 2010)
• Export Icons from Management Packs (added Aug 2010)
• List Management Pack Dependencies (added Aug 2010)
• Invoke External Programs and Scripts in SCOM Tasks (added Oct 2010)
• Unseal Multiple OpsMgr Management Pack Files WITHOUT a Live Management Group (added Oct 2010)

Monitoring
FTP Monitoring

• Validating FTP File Download in OpsMgr Using PowerShell (added Oct 2010)
• Monitoring FTP Site Availability in OpsMgr Using PowerShell (added Oct 2010)

Top of Page
SCE 2007 / 2010 (Essentials) 

• SCE 2007 Custom Command Shell

Top of Page
SCVMM / Hyper-V

• Create VM from Template SCVMM Powershell Script
• VMM2008 - P2V Script
• VMM2008 - Add New Host Script
• Hyper-V Host & Guest Maintenance Script
• Create VM from Template SCVMM Powershell Script

Hyper-V PowerShell Series

• Hyper-V and PowerShell Part 1 (added Oct 2010)
• Hyper-V and PowerShell via Part 2 (added Oct 2010)
• Hyper-V and PowerShell: Part 3, Loading the Modules (added Oct 2010)
• Hyper-V and PowerShell Part 4:  (added Oct 2010)

Top of Page
Service Manager 2010 / 2012

• Creating PowerShell Cmdlets for Service Manager – Announcements and Remove-SCSM ManagementPack Examples
• SCSM PowerShell Cmdlets v0.1
• Service Manager ‘SMLets’ CodePlex Project Expanded
• SMLets Beta 2 Released
• New SCSM CodePlex Project- SCSM Façade
• Properly Querying SCSM Using SMLets Get-SCSMObject cmdlet
• Updating a Change Request when Activities are Updated Using PowerShell in a Workflow
• SMLets Beta 3 Released!
• Using SMLets Beta 3 Post #1 – Using Get-SCSMObject, Get-SCSMClass to Dump Data from SCSM
• Using SMLets Beta 3 Post #2–Using Get-SCSMEnumeration, Get-SCSMRelationshipObject, Get-SCSMRelat
• Using SMLets Beta 3 Post #3–Using Set-SCSMObject to Bulk Update Properties on Objects
• Using SMLets Beta 3 Post #4–Using New-SCSMObject to Create Objects
• Using SMLets Beta 3 Post #5–Getting the GUID ID of an Object
• Using SMLets Beta 3 Post #6–Getting the Owner of a Service
• Using SMLets Beta 3 Post #7–Deleting Any Object in the UI
• Using SMLets Beta 3 Post #8–Getting the GUID of an Enumeration
• Removing Management Packs with Powershell
• Using SMLets Beta 3 Post #9–Deleting Objects
• Using SMLets Beta 3 Post #10–Getting a User’s Manager and a Manager’s Reports
• Using SMLets Beta 3 Post #11–Getting a List of All the Classes in a Management Pack
• Using SMLets Beta 3 Post #12–Getting a GUID ID of an Object
• User Roles PowerShell Report Using SMLets
• Viewing the Data Model Using PowerShell
• SCSM2012 Beta – Powershell improvements

Top of Page
Miscellaneous
File System

• Recursively Delete Read Only Directories, Subdirectories and Files with No Prompt in Powershell

SQL

• PowerShell: How to a remote SQL database and retrieve a data set

Admin and Automation

• Powershell Script to Reboot a List of Computers in a Text File 
• Script Tip: Create multiple registry keys and values in Powershell
• PowerShell Tip: How to pass multiple values from CSV file as inputs (added Aug 2010)

FTP

• Download Multiple Files to Remote FTP Site with PowerShell (added Oct 2010)

System Center (DPM) 2012 CTP for Windows Server "8" Beta support

The CTP enables the Data Protection Manager component's repository and agents to run on Windows Server ‘8” as well as providing protection in Windows Server “8” environments. The CTP also adds protection for new features in Windows Server “8”:

• Hyper-V Virtual Machines on Cluster Shared Volumes 2.0 (CSV2.0)
• Hyper-V Virtual Machines on remote SMB share
• Files on De-Duplicated Volumes

More and download here

DPM - Replica inconsistent and insufficient disk space on BMR (Bare Metal Recovery) Backup

System Center Data Protection Manager 2010 (DPM 2010) offers with its new console in the creation of a protection group in System Protection, a new option called "Bare Metal Recovery".

This option saves the operating system files (System State) and critical volumes (excluding user data) to restore it very quickly to new hardware.  This feature existed in DPM 2007 with Software Removal Tool.

The problem is that sometimes the BMR backup "Bare Metal Recovery" is not working properly and fails with the message "Replica inconsistent" caused by "insufficient disk space".

Cause:

DPM 2010 does not calculate the size of BMR data source, but assumes 30 GB for all servers. Admins should change the value as per the size of BMR backups expected on their environments.

The size of BMR backup is approximately equal to the amount of space used on all critical volumes.

Critical volumes = Boot Volume + System Volume + Volume hosting system state data such as AD DIT/log volumes.

Solution:

We need to run the BMR backup Manually (without DPM) to know the disk size needed on DPM:

1. Create a shared folder with enough space to store the BMR backup, for example \ \ server_name \ BMR
2. On the production server, launch the command prompt with administrative privileges
3. Then type the following command:

wbadmin.exe start backup -allcritical -quiet -backuptarget:\\server_name\bmrshare 

4. Once the backup is complete, check the disk space consumed by the BMR backup and make sure that the same amount of disk space or more have been allocated to the replica volume.
5. Also increase the disk space of volume recovery point.
6. Then in the DPM console, starts the consistency check on our replica inconsistent

Once these operations are completed the BMR backup will be created and recovery points are available.

External Link :
http://technet.microsoft.com/en-us/library/ff634208.aspx

DPM - EUR not working in combination with DFS

It is not possible for users to restore (their own) files using EUR in situations where DFS (Distributed File System) is in place for the file servers.

Users receive the error message: "There are no previous versions available."

CausePrevious versions of a file or of a folder in a DFS share are not listed if you access the share through a nested DFS link from a computer that is running Windows 7 or Windows Server 2008 R2.

Hotfix
A supported hotfix is available from Microsoft for the following Operating Systems.

• Windows 7
• Windows 7 Service Pack 1 (SP1)
• Windows Server 2008 R2
• Windows Server 2008 R2 Service Pack 1 (SP1)

The hotfix is available using this Support link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;2466048

Our experienceThe support article describes that this issue only occurs when you try to access the share by DFS name. <\\DFS\fileshare> When you access the share by the file server name it should work. <\\fileserver\fileshare>. However we experienced the issue also when accessing the share using the server name. Both issues where solved by the hotfix.

More info about End User Recovery (EUR)