Category Archives: Edgesight Custom Queries

ExtraHop’s Citrix Solution Architecture Bundle Walk-Thru

I recorded a walk-thru of the Citrix Alpha Bundle now integrated with our latest 4.0 release.  Below is an example of the dashboard features.  Keep in mind, all of this can be done with NO AGENTs installed on your system and NO WMI walking or interrogating your systems.  We are completely passive and can similar detailed information for all of your environments (Database, SOAP/REST, Web and pretty much anything with an IP Addresses).

In the video below I will discuss some of the application containers and how they can be leveraged for troubleshooting (some overlap in information)

If you are interested in checking this out, we offer a free discovery edition or reach out to me at johnsmith@wiredata.net and I will put you in contact with your area team.

Thanks

John

 

Custom User Groups based on Domains

In an hosting environment, you may have multiple customers logging into your Citrix environment. If you have domain trusts set up, the users may be authenticating and launching applications based on their domain credentials. In edgesight you can run reports based on user groups configured based on each individual domain, but first it has to be configured correctly.

The first thing that needs to happen is you have to get the list of domains from the Edgesight database.

SELECT     domain_name
FROM         vw_es_usergroup_ica_users
GROUP BY domain_name
ORDER BY domain_name

You are then presented with results like the following:

Getdomains

The information you need from this query is the text that is presented in the results. Next you will need to go into your edgesight website. GO to Configure –> Company Configuration –> User Groups. On this window you will need to click New User Group. Enter a name for the domain group you are about to create and make sure that “Take me to the Add Members Page” is checked.

CreateGroup

On the next page make sure you select “Queries” and click “Next” A new page will open and ask you to select a query. Here you will want to select “New Query”. This is where you need to remember your results from the first query. Once you click “New Query”, you will have to type in a name for the query, and in the query text window enter the following:

SELECT     sessid
FROM         vw_es_usergroup_ica_users
WHERE     (domain_name = 'Customer')

createquery
Save the query and test it out. You should see all the users in the domain you listed. In the bottom window make sure you click “Add Query” so that the group uses the query you just created. Congratulations! You can now use this user group to run reports.

EdgeSight: Timezone offsets

Intro

If you have implemented any of the ad hoc SQL queries available on this site, you may have noticed that most time queries are offset by –4 or –5 hours. This is due to the fact that the EdgeSight database uses GMT to record time and John and I are located in the U.S. Eastern Time Zone.

In this post we will take a look at some tables in the EdgeSight database that you can utilize to make your queries more local and portable.

seamonsterThere Be Monsters Here!

Most of my experience with EdgeSight has been with the database views that summarize and organize the vast amounts of data that EdgeSight collects. On occasion I’ve gone where few dare to tread to look directly at the tables for the data I need.

EdgeSight’s views are dizzying enough, but the table structure of the EdgeSight database is intimidating to the SQL neophyte. Despite this, I was inspired to look deeper after David did his post on session counts. His query uses the ‘timezone’ table to determine the time offset for his query and this got me curious. How can I utilize this to make my queries easier to maintain and more portable?

Timezone table

Lets take a look at the timezone table

SELECT *
FROM timezone

image

The above picture is only a portion of the table. It consists of 74 rows. Yeah makes  total sense right? Naturally, I had to do some more checking. If you check the company table, we get a clue.

SELECT *
FROM company

image

As you can see in the above picture, each company in the EdgeSight database has an associated Time Zone and Language. In this case, we have a timezone id (tzid) of 13 and a culture_name of en-US. If we cross reference the tzid with the timezone table we get:

image

Looking at the result above, we can see that this is for the U.S. Eastern time zone and includes daylight savings time as well. You can configure this in the EdgeSight console by clicking on the Configure tab. Look under the Server Configuration section and click on Companies to see where to add/edit company information.

image

So for the example above, I have the language set to English and the time zone set to U.S. Eastern Time which has a GMT offset of –5 hours.

How does this help me?

Let’s take a look at a query I’ve posted on this site before:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username'

As you can see above, all the timedate fields are offset by –4 hours. To keep from having to change the offset to –5 or –4 depending on what time of year it was (standard vs. daylight savings time), I developed a simple select query that determines the current offset by checking the timezone table.

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13

In layman’s terms, look at the timezone table where the timezone id (tzid) is equal to 13. If the field ‘use_daylight’ is equal to zero, use the ‘standard_bias’ otherwise use the ‘daylight_bias’.

I’m setting whichever bias this query returns equal to the variable @tzbias. I then use the @tzbias variable in my timedate fields in my queries. If we rewrite the above query with the tzbias variable, we get the following:

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(mi,@tzbias,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111), 'username'

Since the timezone bias is in minutes, I had to change the DATEADD functions to use mi for minutes. Now I can use my queries year around without worrying about daylight savings time changes.

I hope this provides you some options when doing ad hoc queries against the EdgeSight database. As always, I welcome all comments and questions.

Thanks,
Alain

Are You There EdgeSight? It’s me Worker

Intro

If you rely on EdgeSight to provide accurate and timely information about your farm you have to assume that all your EdgeSight Worker Agents are functioning as expected.  Or do you?  In this post, we will review the information that the EdgeSight console provides you as well as creating a dashboard that can give you detailed information on your EdgeSight Worker agents.

EdgeSight Console: Configuration Tab

The first place you can check the health of your EdgeSight server and its agents in under the Configuration Tab

image

Along the left-hand side of this screen you will see way to configure your workers, alerts, and other server settings. We’re going to spotlight some items under Server Configuration and Server Status.

image

Server Configuration: Status
Your first overview of server health comes when you click on Status under Server Configuration.

image

The first line lists the workers that were and were not updated in the current 24 hour period as well as newly added workers.  Right away you see (in this case) that 48 workers updated and 32 did not.  That’s a large number of EdgeSight agents that have not uploaded their data into your database and therefore any reports you are running will not include these systems.  The question becomes which systems did not update and why?

ES_ZQUEUE…Gesundheit!

The service on the EdgeSight server that processes payloads from the worker agents is the es_zqueue (seen under Server Script Host Status).  This process is not reporting any issues and there are no pending payloads to process (we’ll look at this more later).

Server Status: Messages
Message Status lists all the system messages generated by EdgeSight.  This includes Agent errors, payload errors, and new agents alerts to name a few.

image

Here you will see which servers had a payload issue (Data Upload), but not a reason why systems have not updated the database.

Server Status: Server Script Host
Clicking on this in your EdgeSight Console will show you the following screen:

image

The es_zqueue manages the modules that keep the EdgeSight database updated, cleaned, and running smoothly. The core_zpd_loader 1 and 2 manage the data payloads from devices with the EdgeSight agent including errors. Clicking on the triangle will show the following menu.

image

Clicking on View Log will allow us to investigate why a payload might have failed or created an error.

4/13/2012 5:18:03 AM: PayloadLoader: Starting payload load for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd
4/13/2012 5:18:11 AM: PayloadLoader: Payload load completed with errors for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd. Error: -2146233088: Citrix.EdgeSight.Loader. System.Exception

As we can see if this example, the payload completed with an error and we can try searching Citrix to see if there is a resolution related to this error, but we do not see which server failed to upload any data.

I’ve walked through the diagnostic information that is available in the EdgeSight console to show that we still do not have a clear sign of which servers have updated the EdgeSight database recently. To address this issue, I did some digging around in the EdgeSight database and created a query that links the instance, machine, and OS_version tables.

The Query

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
SELECT    i.instid, m.name as 'System', ip_address AS 'IP', product_version AS 'ES Version',
CASE dept_set_type    WHEN 1 THEN 'XenApp' WHEN 2 THEN 'Endpoint' END AS 'ES Agent',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_sync),100) AS 'Last Sync',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_config_start),100) AS 'Last Config Check',
CONVERT(DECIMAL(19, 2),(last_db_size/1048576.0)) AS 'Last FBDB Size (MB)',
CASE o.short_name    WHEN 'Windows Server 2008' THEN 'W2K8'
WHEN 'Windows Server 2008 R2' THEN 'W2K8R2'
WHEN 'Windows Server 2003' THEN 'W2K3'
WHEN 'Windows XP'           THEN 'XP'
ELSE 'Other' END AS 'OS',
CASE o.ptype        WHEN 'Standard x64 Edition' THEN 'Std x64'
WHEN 'Professional'         THEN 'Pro'
WHEN 'Enterprise Edition'   THEN 'Ent'
WHEN 'Standard Edition'     THEN 'Std'
WHEN 'Enterprise x64 Edition' THEN 'Ent x64'
ELSE 'Other' END AS 'Edition',
sp_level,
CONVERT(VARCHAR,DATEADD(mi,@tzbias,tstamp),111) AS 'Date Added',
cps_farm_name,i.cps_product_name, i.cps_product_version, i.cps_product_service_pack
FROM instance i,machine m, os_version o
WHERE m.machid=i.machid and i.osid = o.osid
ORDER BY dateadd(mi,@tzbias,last_sync) DESC

The Report

image

Click on the image to see a larger version..

With this dashboard (I created it based on the query above in SQL Reporting Services) you can quickly see which servers have updated (Last Config Ck) and which have not. Armed with this information you can review your EdgeSight Agent worker schedules or check the agent on the system in question to make sure it is communicating with the EdgeSight server.

As always I welcome all questions and comments.

Thanks,
Alain

Headless Edgesight: Using Stored procedures to have key metrics emailed to you.

If you have been coming to the site now for the last few years you have probably noticed that we kind of specialize in a “Headless” Edgesight deployment. Dave, Alain and I all tend to steer away from the Edgesight console and focus more on running ad hoc queries or specialized reports. In this entry, I want to focus on leveraging stored procedures to provide you with the data you need via an email every morning so that you can validate a few things in your environment and perhaps put yourself in a position to “right the ship” before your onrush of users pile on the to the system at 8am.

What we will be covering today is coupling a stored procedure with an HTML formatted SQL Mail. To do this we require a few pre-requisites.

1.) You have SQL Mail configured (if you do not, there are better blogs than mine to walk you through that).

2.) That you have enough of a basic understanding of SQL Server to schedule a stored procedure to run. (or the wherewithal to follow the directions I provide).

While there are a number of canned reports in Edgesight, navigating through them or getting the exact metric you are looking for can be painstaking. Using the stored procedure method, you can schedule them to run and provide you with key metrics made up of those custom queries you choose in a format that will work both on your desktop outlook as well as be readable on your smart phone.

First Stored Procedure:

Title: SP_CTX_REBOOTS

Purpose: Most of us have a reboot regimen that we put our XenAPP Servers through. This query/stored proc will email you the status of your nightly reboots. It will be up to you and your operations staff to know which servers need to get rebooted but you can at least find out that 10 of your 25 xenapp servers IMA service did not start before you get your morning rush of users.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_REBOOTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
 --Author:        <John Smith>
-- Create date: <04/02/2012>
-- Description:   <Email Reboot Records>
-- =============================================
CREATE PROCEDURE [dbo].[SP_CTX_REBOOTS]
-- Add the parameters for the stored procedure here
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
declare @Result NVARCHAR(MAX) ;
declare @today datetime ;
set @today = convert(varchar, getdate(),111) ;
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +
 N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +
N'<th>Time</th>'+

N'<th>Machine</th>'+

N'<th>Status</th>'+

cast ((Select distinct td=  dateadd(hh,-5,time_stamp),           '               ',

td= Machine_name,             '               ',

td= Service_State_name,             '               '

from vw_ctrx_archive_service_state

where dateadd(hh,-5,time_stamp) > getdate()-1

order by machine_name

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Server Reboots',

@importance = 'High' ;

end ;

Second Stored Procedure:

Title: SP_CTX_Alerts

Purpose: This is actually a very valuable alert, this will provide you with the top errors/popups users receive when they are on your system. This is made up of both Windows and Application level alerts. I have noted a number of application (custom written errors) that show up here because they popped up during the users session. This can serve as a very valuable QA tool for both Systems Admins and Developers to run down the errors that they see.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_ALERTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:        <John Smith>

-- Create date: <04/02/2012>

-- Description:   <Email top Errors and popups>

-- =============================================

CREATE PROCEDURE [dbo].[SP_CTX_ALERTS]

-- Add the parameters for the stored procedure here

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @Result NVARCHAR(MAX) ;

declare @today datetime ;

set @today = convert(varchar, getdate(),111) ;

SET @tableHTML =

N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +

N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +

N'<th>EXE-Name(If Any)</th>'+

N'<th>Alert Text</th>'+

N'<th>Context</th>'+

N'<th>Total</th>'+

cast ((Select td= CASE

WHEN exe_name IS NULL Then 'None'

Else exe_name

END,          '               ',

td= replace(Alert_text,'°',''),             '               ',

td= Context_text,             '               ',

td= count(alert_text),             '               '

from vw_es_archive_alert

where dateadd(hh,-5,time_stamp) > getdate()-1

group by exe_name, alert_text, context_text

having count(alert_text) > 20

order by count(alert_text) desc

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Alerts and Popups',

@importance = 'High' ;

end ;

Scheduling the Stored Procedure:

Step 1: Add a new SQL Server Agent Job.

 

Step 2: Enter in the name of the Stored Procedure


Step 3: Got to “Steps” Configure the Steps by telling it to execute the stored procedure. (Set the Database Name to the Edgesight Database)


Step 4: Schedule the job to run so that the report is waiting for you when you get in that morning.

What does it look like: (You may have saw this at Briforum ’11)

Basically it is a very simple table that can auto size for your smart phone.

Time

Machine

Status

2012-04-05T02:03:15 SERVER19 Machine Boot
2012-04-05T02:05:06 SERVER19 Service Started
2012-04-05T02:03:48 SERVER20 Machine Boot
2012-04-05T02:05:40 SERVER20 Service Started
2012-04-05T02:15:33 SERVER21 Machine Boot
2012-04-05T02:17:27 SERVER21 Service Started
2012-04-05T02:04:28 SERVER22 Machine Boot
2012-04-05T02:06:19 SERVER22 Service Started
2012-04-05T02:05:55 SERVER23 Machine Boot
2012-04-05T02:07:47 SERVER23 Service Started
2012-04-05T02:06:44 SERVER24 Machine Boot
2012-04-05T02:08:36 SERVER24 Service Started
2012-04-05T02:07:38 SERVER25 Machine Boot
2012-04-05T02:09:33 SERVER25 Service Started
2012-04-05T02:10:08 SERVER27 Machine Boot
2012-04-05T02:12:07 SERVER27 Service Started
2012-04-05T02:10:38 SERVER28 Machine Boot
2012-04-05T02:12:31 SERVER28 Service Started
2012-04-05T02:11:46 SERVER29 Machine Boot
2012-04-05T02:13:35 SERVER29 Service Started
2012-04-05T02:12:55 SERVER30 Machine Boot
2012-04-05T02:14:45 SERVER30 Service Started
2012-04-05T02:02:28 SERVER35 Machine Boot
2012-04-05T02:04:18 SERVER35 Service Started
2012-04-05T02:04:59 SERVER35 Service Started
2012-04-05T02:10:50 SERVER35 Service Started

Conclusion:
So, the two queries included in the stored procedures are very useful but the point of the post this time is not to provide a useful query so much as it is to provide a useful method. If you have some rudimentary SQL Skills you should be able to reverse engineer this to include any custom Edgesight Query that you see on this post or that you have made up yourself. Sometimes it is nice to be able to have a simple, formatted report automatically sent to you, a customer or your boss so that they can get the key metrics they need on a consistent basis. I have found Edgesight to actually be more useful in a headless configuration (ad hoc and these stored procs) than to use the console.

Thanks for reading! If you are at Synergy and you see me (big ugly guy w/thick glasses) say “heya”

John

ESUTH Briforum Session now online

My Briforum Session is online for anyone who wants to go check it out.  After viewing the session it appears I said “UH..” about 637 times?  Anyway it was a great experience and an honor to speak in front of so many talented individuals.  If you are curious of what I do when I go in and run queries you can watch this session and basically see how it’s done.  The session also has some custom queries show integrating malware and geospatial data with your Edgesight data to check for infected systems that your antivirus may have missed.

My presentation was somewhat hampered by being told I could not use any of my customer data in the presentation.  One of the hard tasks in teaching Edgesight is that most companies do not want their data made public so doing the presentation in a public forum was a bit tough.  This would be much easier if it were done in a corporate classroom using the customer’s data. 

Anyway, please have a look, if you have never run a SQL query you will see some of that here.

Take care and thanks for watching.  I cannot recommend enough, attending Briforum, it is a true geek-to-geek, vender nuetral conference and was, hands down, the most informative virtualization conference I have ever gone to. 

Thanks guys

John

http://www.brianmadden.com/blogs/videos/archive/2011/08/16/edgesight-under-the-hood_2C00_-a-video-from-BriForum-2011.aspx

 

Average Session Count by Day and Hour: The Report

In my previous article: Average Session Count by Day and Hour: The Query,  I dove into a query that shows the number of sessions on a farm at any given hour.  Next, we go onto the creation of the report. For this I use SQL Server Business Intelligence Development Studio (BIDS).  To begin, I created a new report to contain a matrix which runs the query that I created above.  For this report to run in BIDS and EdgeSight, the entirety of the following should be placed in the query window:

DECLARE @UTCNow DATETIME
DECLARE @LocalStartDate SMALLDATETIME
DECLARE @LocalEndDate SMALLDATETIME
DECLARE @CompId INTEGER
DECLARE @Filter INTEGER

SET @CompID = 1
SET @Filter = 1

SET @UTCNow = GETUTCDATE()
SET @LocalStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 0, @UTCNow)
SET @LocalEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 0, @UTCNow)

SELECT     SUM(vw_ctrx_archive_system_perf.active_sessions_peak + vw_ctrx_archive_system_perf.inactive_sessions_peak) AS TotalSessions,
                      CONVERT(varchar(10), DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 111) AS Date, CONVERT(varchar(10),
                      DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 108) AS Time
FROM         vw_ctrx_archive_system_perf INNER JOIN
                      company ON vw_ctrx_archive_system_perf.compid = company.compid INNER JOIN
                      timezone ON company.tzid = timezone.tzid
GROUP BY vw_ctrx_archive_system_perf.time_stamp, timezone.standard_bias
HAVING      (DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp) >= @LocalStartDate) AND (DATEADD(mi, timezone.standard_bias,
                      vw_ctrx_archive_system_perf.time_stamp) < DATEADD(d, 1, @LocalEndDate))
ORDER BY Date, Time

In the “Design the Matrix Wizard” Add Time2 to the Columns, Date to the Rows, and Total Sessions to the Details value.  Click finish.
Edgesight Matrix wizard

The first necessary items that need to be added to the report in order for this report to run successfully are parameters.  On the data or layout tab of your new report, open the Report Parameters window.  Two parameters need to be added to the report “Start” and “End”.  They both need to be string values.  EdgeSight reads these parameters on the reports and allows you to choose what dates you want to run the report for.  Go ahead and preview the new report.  If it doesn’t work, take a break, watch an episode of Dexter, and be glad you work on computers, where everyone gets a second shot.  Let’s continue down the steps to making this a report with a bit more useful data.

In the rows where the Date is, I needed to add the day of the week to display days like the following: 8/1/2011 – Monday.  To do this I changed the formula for the cell to:  =Fields!Date.Value & ” – ” & WeekdayName(Weekday(Fields!Date.Value))

In the columns where time2 resides I needed to show the hours and not show the integer.  I’m not a big VB guy and am still working on growing my coding.  For that reason I have change the cell to the impeccableformula of: =Fields!Time2.Value & “:00:00”. This shows the hours of the day in military format.

Good enough for me.

In BIDS, to show the average of rows and columns, a bit of code needs to be added to the report.  In the Report Layout window, you can add the subtotals to the rows and columns by simply right clicking on any row and column and selecting “Subtotal”.  BIDS will add a new row or column to your report that shows the subtotal of the row or column.  The problem is that BIDS 2005, I don’t know about any newer version, only subtotals are shown and not the average.  To show the average of the columns instead of the subtotals, open the “report properties” and go to the code tab.  Based on a how-to from All About Business Intelligence add the following code:

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
        If InScope Then
            Return sumValue
        Else
            If Not avgValue Is Nothing Then
                Return String.Format(avgFormat, CDbl(avgValue))
            Else
                Return Nothing
            End If
        End If
    End Function

Next you have to change the details cell to the following code:


=Code.MatrixCellValue(
Sum(Fields!TotalSessions.Value),
Avg(Fields!TotalSessions.Value),
InScope("matrix1_Date") and InScope("matrix1_Time"),
"{0,6:N0}",
"{0,6:N1}")

Go ahead and preview the new results of your report.

After a few aesthetic changes, you can find an example data output of my finished report here:
Download – Session Count by Date and Hour – Please note that this file is set to a .doc and needs to be renamed to a .rdl in order to be uploaded into EdgeSight.

***Update 8/19/11 – I did some playing around with this report and query and have noticed that I only applied the timezone offset to the time and not the date structure.  If a user runs the report on GMT-5:00, then instead of Friday 7:00PM showing up as the time, it will show Saturday 7:00PM.  I am correcting the query as soon as I can, and will update this post and the report once it is corrected.

****Update 8/20/11 – I fixed the query and the report.  The new version of the report converts time correctly.  You can download it here