SQL Server Basic tutorial

leh ladakh and himalayas road and passess

2016-09-19T21:33:00.001-07:00

SCSM query to veiw Service Requests Details

2016-03-24T00:20:00.000-07:00

As if you have followed my last post regarding SCSM incident analysis. So thought of sharing a new post on which i will try to show case some details on Service Request tables and views.

Ok, to start work on the SCSM data warehouse database database and the tables and views its features for Service Request information details. As earlier I will follow the same old tactics. Putting a filter on the table as well as on the views with services.

Here again the most important table is ServiceRequestDim and from the name again it can be understood that its a dimensional table and will have multiple other related tables and also related facts tables on which we will discuss. So lets see what inside the table ServiceRequestDim and the view on top of that table is ServiceRequestDimVw.

select sd.Id ,sd.Status ,sd.CreatedDate ,sd.Description ,sd.DisplayName,sd.Title,sd.Status,sd.SupportGroup

,sd.SupportGroup_ServiceRequestSupportGroupId,sd.ClosedDate,sd.CompletedDate,sd.EntityDimKey,sd.FirstAssignedDate,sd.FirstResponseDate,

sd.ImplementationResults,sd.Priority

from ServiceRequestDim sd

Above is the first basic query from which we get basic details of all informatons related to Service Request.As again we can put and filter on create date to see list of SR got created after a certain date or certain time.

SCSM Reports creation. SCSM warehouse database. SCSM incident details reports

2016-03-23T00:45:00.000-07:00

Working in an environment where Microsoft system center Service manager (SCSM) is used. Need to work with the management to work on various reports .SCSM product is used across the whole environment for

· Incident creation.

· Problem management

· Change management

· Service Request.

So ticket are raised via email, portal and web.

So need to work as which tables are to be targeted and the relation with other tables to get related incident information.

So to start with Incident. Below are the tables and views can be used for. Microsoft provide lots of table and views and store procedure to get details information’s.

select

I.Id,i.Title ,i.Description,i.DisplayName ,i.FirstAssignedDate, i.CreatedDate,i.ClosedDate,i.EntityDimKey,i.Escalated,i.FirstResponseDate,

i.DisplayName,i.Priority ,i.Source ,

i.Status,i.TierQueue, i.TierQueue_IncidentTierQueuesId,i.Impact,

i.ResolutionCategory,i.ResolvedDate,i.ClosedDate

from IncidentDim I

where i.CreatedDate >'2015-12-04'

order by i.CreatedDate desc

The above query will be give all related information for all the incident came after 4th dec 2015. though this table has lot of many more columns with many more details which you can always check for your self. I have used most used once.

Ok let me also mention to people who are relatively new to SCSM. About SCSM architecture. When you install SCSM application from the front end portal many activities can be done.

Like Incident creation or Services request creation and these activities will not impact SCSM database warehouse directly. Initially all these information will got to ServiceManager database and this database can be hosted in a different instance. But writing query directly to this database is not always advisable depends on your requirement.

Like if want to generate a query which will give a run time view of incident flow and its priorities.
For that below query can help.

SELECT I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID], I.DisplayName AS [Name], L.LTValue AS [Support Group] ,

i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as create_date ,e.enumtypename,

i.Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794 into new_incident

FROM MT_System$WorkItem$Incident I (nolock)

INNER JOIN EnumType E (nolock) ON I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = E.EnumTypeId

INNER JOIN LocalizedText L (nolock) ON L.ElementName = E.EnumTypeName

where i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >='2015-12-25'

and l.LTValue like '%_SSCIT_%'

and I .CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 > dateadd(MI ,-80,CURRENT_TIMESTAMP)

order by i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 desc

This above query can be run on the ServiceManager database and it will give number of incident came in last 80 minutes.

But from the above query you can understand that it not very user friendly to work with. The data getting generated in ServiceManager database can be also called as SCSM internal database. Are moved to SCSM datawarehouse databases. The warehouse database is called as DataMart.

Ok let me give you an approach which I followed when I first worked into it. Put a filter on table tab with incident and it will which give you all tables name related to incident same approach can be used in view.

Now, let me give you a case study and lets move one step ahead now for the above query you got all basic information related to incident. Now your manager ask to get a report on how much time the incident was kept in pending status and what are the reasons for the incident to be kept in pending status.

Below is the query which will give you all related informations.

select i.id,i.CreatedDate ,i.displayname,i.Description ,i.firstAssignedDate,i.ResolvedDate ,i.Priority

,DATEDIFF([hour],i.CreatedDate, i.FirstAssignedDate) as TtRespond

,DATEDIFF([hour],i.CreatedDate, i.ResolvedDate) as TtResolve,

ActiveDuration = SUM(

CASE

WHEN statusEnum.ID = 'IncidentStatusEnum.Active'

THEN dFact.TotalTimeMeasure

ELSE 0

END

PendingDuration = SUM(

CASE

WHEN statusEnum.ID = 'IncidentStatusEnum.Active.Pending'

THEN dFact.TotalTimeMeasure

ELSE 0

END

PendingThirdParty=SUM(

CASE

when statusenum.IncidentStatusId='12'

then dfact.TotalTimeMeasure

ELSE 0

END

AssignedStatus=SUM(

CASE

when statusenum.IncidentStatusId='10'

then dfact.TotalTimeMeasure

ELSE 0

END

InProgress=SUM(

CASE

when statusenum.IncidentStatusId='7'

then dfact.TotalTimeMeasure

ELSE 0

END

Resolution_Verification=SUM(

CASE

when statusenum.IncidentStatusId='9'

then dfact.TotalTimeMeasure

ELSE 0

END

PendingChangeRequestImplemnetation=SUM(

CASE

when statusenum.IncidentStatusId='8'

then dfact.TotalTimeMeasure

ELSE 0

END

dFact.IncidentDimKey into pending_sla

from

dbo.IncidentStatusDurationFactvw dFact,

dbo.IncidentStatusvw statusEnum,

dbo.IncidentDimvw i

where statusEnum.IncidentStatusId = dFact.IncidentStatusId

and dfact.incidentdimkey =i.incidentdimkey

and i.CreatedDate >='2015-12-01'

Group by i.Priority,dfact.IncidentDimKey,i.id,i.CreatedDate ,i.displayname,i.Description ,

i.firstAssignedDate,i.ResolvedDate--,ttrespond,ttresolve

order by i.CreatedDate desc

As Now you have the query don’t just copy paste it try to understand the login on this. Please notice where in the above query views are used. Please remember View ending with VW, these views are IncidentDimVW, IncidentstatusVW, IncidentStatusDurationFactVW. As already earlier informed that we are writing the queries into data warehouse database and hope that you have some idea on datawarehousing as from the view name it can be understood that incidentDimVW is a Dimensional and IncidentstatusDurationFactVW refers to a fact table IncidentstatusDurationFact.

Ok hopefully you are still here now lets move one more step ahead and the below query will use multiple table to get most of the information for incident.

SELECT DISTINCT

incident.Id

,incident.Title

,incident.Description

,incident.ResolutionDescription

,stringsSupportGroup.DisplayName as SupportGroup

,incident.CreatedDate

,MONTH(incident.CreatedDate) as CreatedDateMonth

,YEAR(incident.CreatedDate) as CreatedDateYear

,DATEPART(q,incident.CreatedDate) as CreatedDateQuarter

,incident.FirstAssignedDate

,assignedUser.DisplayName as AssignedToUserName

,incident.ResolvedDate

,DATEDIFF([hour],incident.CreatedDate, incident.FirstAssignedDate) as TtRespond

,DATEDIFF([hour],incident.CreatedDate, incident.ResolvedDate) as TtResolve

,resolvedByUser.DisplayName as ResolvedByUserName

,stringsStatus.DisplayName as [Status]

,stringsSource.DisplayName as [Source]

,incident.Priority

,classi.IncidentClassificationValue as Classification

,incident.Escalated

,affectedUser.DisplayName as AffectedUserName

,affectedUser.Country as AffectedUserCountry

,affectedUser.City as AffectedUserCity

,affectedUser.Department as AffectedUserDepartment

,affectedUser.Company as AffectedUserCompany

,stringsResoltuionCategory.DisplayName as ResolutionCategory

--,tw.TimeWorked

into tabl1

FROM IncidentDim incident

LEFT JOIN WorkItemDim workitem on incident.EntityDimKey = workItem.EntityDimKey

LEFT JOIN IncidentTierQueues as tq ON incident.TierQueue_IncidentTierQueuesId = tq.IncidentTierQueuesId

LEFT JOIN DisplayStringDimvw as stringsSupportGroup ON tq.EnumTypeId=stringsSupportGroup.BaseManagedEntityId and stringsSupportGroup.LanguageCode = 'ENU'

LEFT JOIN IncidentStatusvw stat on incident.Status = stat.ID

LEFT JOIN DisplayStringDimvw as stringsStatus ON stat.EnumTypeId = stringsStatus.BaseManagedEntityId and stringsStatus.LanguageCode = 'ENU'

LEFT JOIN IncidentClassification as classi ON incident.Classification_IncidentClassificationId = classi.IncidentClassificationId

LEFT JOIN IncidentSourcevw source on incident.Source = source.ID

LEFT JOIN DisplayStringDimvw as stringsSource ON source.EnumTypeId = stringsSource.BaseManagedEntityId and stringsSource.LanguageCode = 'ENU'

LEFT JOIN WorkItemAffectedUserFactvw as affectUserFact ON affectUserFact.WorkItemDimKey = workitem.WorkItemDimKey --and affectUserFact.DeletedDate is null

AND affectUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM WorkItemAffectedUserFactvw as tmp WHERE tmp.WorkItemDimKey=workitem.WorkItemDimKey)

LEFT JOIN UserDimvw as affectedUser ON affectUserFact.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT JOIN WorkItemAssignedToUserFactvw as assignedUserFact ON assignedUserFact.WorkItemDimKey = workitem.WorkItemDimKey

AND assignedUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM dbo.WorkItemAssignedToUserFactvw as tmp WHERE tmp.WorkItemDimKey=workitem.WorkItemDimKey)

LEFT JOIN UserDimvw as assignedUser ON assignedUserFact.WorkItemAssignedToUser_UserDimKey = assignedUser.UserDimKey

LEFT JOIN IncidentResolutionCategoryvw as resultionCat ON resultionCat.ID = incident.ResolutionCategory

LEFT JOIN DisplayStringDimvw as stringsResoltuionCategory ON resultionCat.EnumTypeId = stringsResoltuionCategory.BaseManagedEntityId and stringsResoltuionCategory.LanguageCode = 'ENU'

LEFT JOIN IncidentResolvedByUserFactvw as resolvedByUserFact ON incident.IncidentDimKey = resolvedByUserFact.IncidentDimKey

AND resolvedByUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM IncidentResolvedByUserFactvw as tmp WHERE tmp.IncidentDimKey=incident.IncidentDimKey)

LEFT JOIN UserDimvw as resolvedByUser ON resolvedByUserFact.TroubleTicketResolvedByUser_UserDimKey = resolvedByUser.UserDimKey

Best of luck hope above query are helpfull.

http://sqlsignature.blog

FILESTREAM on SQL SERVER 2008.

2013-09-14T08:54:00.000-07:00

For storing unstructured data like word doc, images, video files in database on SQL Server 2008. We need to use Filestream.

1. Need to enable filestream feature if not enable at the time of installation. It can be enable from SQL configuration manager.

Right Click on the SQL Service then property. then to FileStream tab.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

2. Creating a FILESTREAM database. Can be also done through GUI.

e.g below normal database creation just adding a file stream file group.

CREATE DATABASE Archive

PRIMARY ( NAME = Arch1,

FILENAME = 'j:\test\archdat1.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,

FILENAME = 'j:\test\filestream1')

LOG ON ( NAME = Archlog1,

FILENAME = 'j:\test\archlog1.ldf')

2^nd table:

CREATE TABLE [dbo].[Images](

[ID] uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,

[Image] [varbinary](max) filestream default NULL

) ON [PRIMARY]

3. Inserting data into this table. In the below example I have inserting one image w.jpg file into the database.

INSERT INTO [Images]

([ID],[Image])

VALUES

(NEWID(),

(SELECT * FROM OPENROWSET

(BULK 'j:\w.jpg',

SINGLE_BLOB) AS [Image]))

SSIS packesges can be also used to insert this type of data into database.

4. To view data for the table now.

If we run

select * from images

It will run successfully but will fetch the image it above format.

I have us SSRS to display the Image.

5. In BIDS create an SSRS project solution to display the same.

MySQL in Linux

2012-11-29T05:41:00.001-08:00

I am presently working in mysql environment.. MySQL is platform independent so it can be installed on any os e.g Windows, Linux, Unix, Solaries..
Installing MySQL on commands is a new challenging task..

removing public permission from master and msdb databases in sql server 2000

2012-07-19T05:31:00.000-07:00

Today I was runing a Squirral scan on a sql server 2000 instance found many security isssues so I bolg out the fixes done to that setting..

EXEC sp_MSforeachdb 'USE [?];
SELECT * FROM sysusers;'
GO

USE <Database Name>
GO
EXEC sp_revokedbaccess 'guest'
GO

use master revoke execute on xp_regread to public
use master revoke execute on xp_instance_regread to public

use msdb; revoke execute on sp_add_job to public; use master

use msdb; revoke execute on sp_add_jobserver to public; use master

use msdb; revoke execute on sp_add_jobstep to public; use master

use msdb; revoke execute on sp_start_job to public; use master

use msdb
revoke execute on sp_purge_jobhistory to public
revoke execute on sp_help_jobhistory to public
revoke execute on sp_delete_jobserver to public
revoke execute on sp_help_jobserver to public
revoke execute on sp_get_jobstep_db_username to public
revoke execute on sp_update_jobstep to public
revoke execute on sp_delete_jobstep to public
revoke execute on sp_help_jobstep to public
revoke execute on sp_add_jobschedule to public
revoke execute on sp_update_jobschedule to public
revoke execute on sp_delete_jobschedule to public
revoke execute on sp_help_jobschedule to public
revoke execute on sp_add_job to public
revoke execute on sp_update_job to public
revoke execute on sp_delete_job to public
revoke execute on sp_help_job to public
revoke execute on sp_get_job_alerts to public
revoke execute on sp_stop_job to public
revoke execute on sp_check_for_owned_jobs to public
revoke execute on sp_check_for_owned_jobsteps to public
use master
use master revoke execute on sp_readwebtask to public

use master revoke execute on sp_runwebtask to public
use master
exec sp_dropextendedproc 'sp_OACreate'
exec sp_dropextendedproc 'sp_OADestroy'
exec sp_dropextendedproc 'sp_OAGetErrorInfo'
exec sp_dropextendedproc 'sp_OAGetProperty'
exec sp_dropextendedproc 'sp_OAMethod'
exec sp_dropextendedproc 'sp_OASetProperty'
exec sp_dropextendedproc 'sp_OAStop'

SQL Mail Configuration

2012-07-18T22:52:00.002-07:00

I plan to post the thing where I faced some kind of problem.. As I am working as a DBA and trying to learn new things everyday and like to share the same with you and hoping this might help someone some way.
I am configuring a SQL Mail on SQL Server 2008 R2 .
Steps to be followed.
1.SSMS-->Management-->Database Mail
2. Right Click configure Database Mail-->

Database Core Concept

2012-07-14T23:08:00.001-07:00

Three-Schema Architecture

Internal Schema

· Description of Physical Storage structure of the database.

· It has an internal Schema

· It descried the details of the store records and access method used to achieve efficiency to data.

Conceptual level

· This hides the details of physical storage structure

· This level is both independent of software and hardware

External Level or view level

· This the outer most layer

· This layer is closet to the users

· Data can be view by individual users

Dbms must have a programming language

Dbms Language
DDL---Data Defination Language
DML—Data Manupulation language

Data Manupulation Language:

Basic SQL Querry

2012-07-12T08:23:00.000-07:00

Microsoft SQL Server is a RDMS software ppackage developed by Microsoft. Its a database, so its primary function is to store and retrieve data as requested by other applications.

A DBMS package is a complex set of Software Programs that Controls the organization, storage and modifying and retrieval of data in a database:

· It contains collection of data.

· Set of programs to access those data.

· A complete definition of the database structure and constrain.

Overview of Microsoft SQL Server and Version Details.

9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure DB	CloudDB
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11.0	2012	SQL Server 2012	Denali

Database users:

1.Database Administrator:

· Database is one of the many primary resources that are used by many people in an organization.

· DBMS and related software are the secondary resources. Administrating this secondary resources is the primary responsibility of a Database administrator.

· He has the complete authority to access and monitoring a database .

· He is responsible for for creating modifying and maintain the database

· He grant permission to the users of the database .

· He defines procedure to recover database

Other users

2.Database designer

3.End Users

Ok Now Lets work on everyday work of a DBA. As in current world role of DBA is evolving very fast.
you can be taking care of Application and then will be called as application DBA. Your databases can be hosted in cloud in various cloud technologies for microsoft its Azure . Then you will be called as Cloud DBA and many more but if I drill down we all DB administrator gets few common issues which i will discuss as those comes under everyday tasks for most dba's.

Please let us know did it help....

To have more understanding of SQL server below links can help:

<a href="http://www.quackit.com/sql_server/tutorial/%22%3ESQL Server Tutorial</a> from Quackit.com

SQL Clustering on windows

2011-11-10T07:10:00.000-08:00

Clustering means to virtuallize multiple server in clustering term called node as a single server. SQL is a Cluster aware software. On Installing SQL server 2005 or 2008 it recognise the cluster environemnt and enables to install failover on clustering inside SQL Server Setup.

Sql Cluster installation is easy if the windows cluster configuration is good the set will install server server into all the nodes.
In SQL server 2005 when installation Starts in a cluster environment you will get the option to check failover cluster
then it will ask for the cluster name and you will require two provide 2 ip
1 private IP
2. Public IP

Hardware require for Clustering.
More that one same configuration server.
Share disk storage space like SAN.
Multiple LAN ports.

Server Drivers should also needs to be same else might through error. To troobleshoot clustering is bit difficult. Any standard OS required .e.g win 2003, 2008 R2 server are good cluster aware os.