Data dictionary?
lehrsj24
Posts: 13 Bronze 2
I'd like to do some reports against some of the data in the SQLMonitor database. Is there any type of documentation on how the data all relates? Some of the data is pretty self explanetory but others is not.
thanks,
Sherry
thanks,
Sherry
Comments
We're going to provide some sample SSRS reports soon, to help people get started, but you're right - though some of the schema is obvious, not all of it is. NB: We've added a number of VIEWs to make some of the obvious joins so that you don't have to (for example, joining various data tables to the server reference tables).
I'll add a data dictionary to the plans - are there any particular pieces of data you were after in the mean time?
Ben
As an example, I have alertID 70009 that is for a SQL server job that failed. On the web page when I click on the alert I see the error message for the failed job:
Job outcome message: The job failed. The Job was invoked by Schedule 13 (Sundays). The last step to run was step 3 (CaptureProject). NOTE: Failed to notify ....
There is a view called data.cluster_sqlserver_agent_job_history_instances_view.
In this view I see ID 906119 that a field for Cluster_SQLServer_Agent_Job_History_Message, which is the error message for this alert. It matches the server from the alert, the time, etc.(actually I started in this view, found an error, and then traced it back to the alert).
If I have the AlertID from the alert.Alert table how can I get to this view for the error message?
Thanks
I'm taking a look at this request, though I'm afraid it's not as trivial as some other possible reports! I'll post back as soon as I've put some T-SQL together..
Apologies for the delay.
Regards
Ben
Below is some code I've put together linking the Alerts VIEW [alert].[Alert_Current] and the Job History VIEW [data].[Cluster_SqlServer_Agent_Job_History_Instances_View]. I've put the most relevant info in the first few columns (alert ID, error message, date/time of alert), but have also included other columns in case you're interested in these.
NB: I've put this together quite hastily - let me know if you have any problems with this code and I'll try to fix it!
Hope this helps.
Ben Rees
SELECT AlertID , Cluster_SqlServer_Agent_Job_History_Message , DateTimeRaised , Id AS Job_History_Id , IdCollectionDate , Cluster_SqlServer_Agent_Job_History_RunDuration , Cluster_SqlServer_Agent_Job_History_RunStatus , Cluster_SqlServer_Agent_Job_History_SqlMessageId , Cluster_SqlServer_Agent_Job_History_SqlSeverity , CollectionDate , CollectionDate_DateTime , Cluster_Name , Cluster_SqlServer_Name , Cluster_SqlServer_Agent_Job_Id , Cluster_SqlServer_Agent_Job_History_Id , Cluster_SqlServer_Agent_Job_History_RunDate , Cluster_SqlServer_Agent_Job_History_RunDate_DateTime , AlertType , AlertDescription , AlertTicks FROM ( SELECT [Id] , [IdCollectionDate] , [Cluster_SqlServer_Agent_Job_History_Message] , [Cluster_SqlServer_Agent_Job_History_RunDuration] , [Cluster_SqlServer_Agent_Job_History_RunStatus] , [Cluster_SqlServer_Agent_Job_History_SqlMessageId] , [Cluster_SqlServer_Agent_Job_History_SqlSeverity] , [CollectionDate] , [CollectionDate_DateTime] , [Cluster_Name] , [Cluster_SqlServer_Name] , [Cluster_SqlServer_Agent_Job_Id] , [Cluster_SqlServer_Agent_Job_History_Id] , [Cluster_SqlServer_Agent_Job_History_RunDate] , [Cluster_SqlServer_Agent_Job_History_RunDate_DateTime] FROM [data].[Cluster_SqlServer_Agent_Job_History_Instances_View] ) AS jh INNER JOIN ( SELECT SUBSTRING(TargetObject, 21 + PATINDEX('%:%', SUBSTRING(TargetObject, 21, 4)), CAST(SUBSTRING(TargetObject, 21, PATINDEX('%:%', SUBSTRING(TargetObject, 21, 4)) - 1) AS INT)) AS ClusterName , CASE WHEN PATINDEX('%7:Machine,1,4:Name%', TargetObject) > 0 THEN SUBSTRING(TargetObject, PATINDEX('%7:Machine,1,4:Name%', TargetObject) + 20 + PATINDEX('%:%', SUBSTRING(TargetObject, PATINDEX('%7:Machine,1,4:Name%', TargetObject) + 20, 4)), CAST(SUBSTRING(TargetObject, PATINDEX('%7:Machine,1,4:Name%', TargetObject) + 20, PATINDEX('%:%', SUBSTRING(TargetObject, PATINDEX('%7:Machine,1,4:Name%', TargetObject) + 20, 4)) - 1) AS INT)) ELSE '' END AS MachineName , CASE WHEN PATINDEX('%9:SqlServer,1,4:Name%', TargetObject) > 0 THEN SUBSTRING(TargetObject, PATINDEX('%9:SqlServer,1,4:Name%', TargetObject) + 22 + PATINDEX('%:%', SUBSTRING(TargetObject, PATINDEX('%9:SqlServer,1,4:Name%', TargetObject) + 22, 4)), CAST(SUBSTRING(TargetObject, PATINDEX('%9:SqlServer,1,4:Name%', TargetObject) + 22, PATINDEX('%:%', SUBSTRING(TargetObject, PATINDEX('%9:SqlServer,1,4:Name%', TargetObject) + 22, 4)) - 1) AS INT)) ELSE '' END AS SQLServerName , at.Name AS AlertType , at.Description AS AlertDescription , [utils].[TicksToDateTime](ai.Raised) AS DateTimeRaised , ai.Raised AS AlertTicks , ai.AlertId AS AlertID FROM alert.Alert_Current AS ai INNER JOIN alert.Alert_Type AS at ON ai.AlertType = at.AlertType ) AS ad ON ad.ClusterName = jh.Cluster_Name AND ad.SQLServerName = jh.Cluster_SqlServer_Name WHERE ad.AlertType = 'Job failed' AND AlertTicks = Cluster_SqlServer_Agent_Job_History_RunDate ORDER BY ad.DateTimeRaised DESCWe have put in a number of VIEWs which should, hopefully, help you create reports or queries for the sort of data you’re looking for.
The most interesting VIEWs can be found under [data].xxxxx_View. A lot of these should be self-explanatory. For example, looking at the information that you mention:
Monitored Servers with OS info etc
This can be found in the view
Mem Disk Space
Memory values can be found in Disk space can be found in
Databases
Lists of databases can be found in though this needs to be grouped (there is more than one row per database as this table also includes some availability information.)
Database Size
This can be found in
NB: There’s also a couple of functions to convert between Ticks (as used in many views and tables) and proper date times, and
Hope that helps!
Ben
The column seems like it should match the sum of the values in for all of the files on the drive; however these values are way off. Could you shed some light?
Here is my code, in case it is useful to anyone (this does create values that seem to match what is in Activity Monitor, but I haven't done any in-depth validation):
DECLARE @servername VARCHAR(100) , @disk CHAR(1) , @pStart DATETIME , @pEnd DATETIME SELECT @servername = 'myserver' SELECT @pEnd = '20110915' SELECT @pStart = '20110914' SELECT @disk = 'I' ; --Set Default Dates if none provided IF @pEnd IS NULL SELECT @pEnd = GETDATE() ; IF @pStart IS NULL SELECT @pStart = DATEADD(HOUR, -2, @pEnd) --Set disk and server to proper case used by DB SELECT @disk = UPPER(@disk) , @servername = LOWER(@servername) ; --Create CTE with IO info for time period WITH Disk_CTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CollectionDate DESC ) AS rownum , cs.CollectionDate , cs.Cluster_Machine_LogicalDisk_CumulativeReadBytes , cs.Cluster_Machine_LogicalDisk_CumulativeWriteBytes FROM data.[Cluster_Machine_LogicalDisk_UnstableSamples_View] AS cs WHERE cs.Cluster_Name = @servername AND cs.Cluster_Machine_LogicalDisk_Name = @disk + ':' AND CollectionDate BETWEEN utils.DateTimeToTicks(@pStart) AND utils.DateTimeToTicks(@pEnd) ), --Create CTE with Deltas between measurements Delta_CTE AS ( SELECT ( CAST(( DATEDIFF(ms, utils.DateTimeToTicks(earlier.CollectionDate), utils.DateTimeToTicks(later.CollectionDate) )) AS FLOAT) / 1000 ) AS TimeDiff , later.CollectionDate AS DateInt , ( ( later.Cluster_Machine_LogicalDisk_CumulativeReadBytes - earlier.Cluster_Machine_LogicalDisk_CumulativeReadBytes ) / ( 1024 * 1024 ) ) AS MBReadDiff , ( ( later.Cluster_Machine_LogicalDisk_CumulativeWriteBytes - earlier.Cluster_Machine_LogicalDisk_CumulativeWriteBytes ) / ( 1024 * 1024 ) ) AS MBWriteDiff , CONVERT(CHAR(12), utils.DateTimeToTicks(later.CollectionDate), 106) AS CollectionDate , CONVERT(SMALLDATETIME, utils.DateTimeToTicks(earlier.CollectionDate), 106) AS CollectionDateTime , DATEADD(mi, ( DATEPART(mi, utils.DateTimeToTicks(earlier.CollectionDate)) / 10 ) * 10, DATEADD(hh, DATEDIFF(hh, 0, utils.DateTimeToTicks(earlier.CollectionDate)), 0)) AS [Ten Min] , DATEADD(mi, ( DATEPART(mi, utils.DateTimeToTicks(earlier.CollectionDate)) / 30 ) * 30, DATEADD(hh, DATEDIFF(hh, 0, utils.DateTimeToTicks(earlier.CollectionDate)), 0)) AS [Thirty Min] , DATEADD(mi, ( DATEPART(mi, utils.DateTimeToTicks(earlier.CollectionDate)) / 60 ) * 60, DATEADD(hh, DATEDIFF(hh, 0, utils.DateTimeToTicks(earlier.CollectionDate)), 0)) AS [Hour] FROM disk_CTE AS later INNER JOIN disk_CTE AS earlier ON later.rownum = earlier.rownum - 1 ) --Create the final Dataset to be consumed by SSRS SELECT Delta_CTE.CollectionDateTime , CASE WHEN TimeDiff = 0 THEN 0 ELSE CAST(Delta_CTE.MBReadDiff / TimeDiff AS NUMERIC(18, 2)) END AS MBReadPerSec , CASE WHEN TimeDiff = 0 THEN 0 ELSE CAST(Delta_CTE.MBWriteDiff / TimeDiff AS NUMERIC(18, 2)) END AS MBWritePerSec , Delta_CTE.[Ten Min] , Delta_CTE.[Thirty Min] , Delta_CTE.[Hour] FROM Delta_CTE ORDER BY Delta_CTE.DateInt ASCI haven't looked at your code but as far as I understand sys.dm_io_virtual_file_stats returns I/O stats only for data and log files whereas data stored in Logical Disk performance objects relates to everything on that disk which will include non SQL Server data and log files too.
Thanks,
Priya
Project Manager
Red Gate Software