Sunday, June 12, 2011

Workaround for Citrix EdgeSight Process Usage report showing average times instead of time sums and a quick insight into using MS SQL Report Builder with queries to generate custom SQL reports


Why oh why did Citrix botch up their great "Process Usage" report? Why do software vendors tamper with great product features instead of building upon them? Let's set a baseline on what we'll try to achieve in this post:


The Issue

After EdgeSight 4.5 the "Process Usage" report has been tampered with and instead of displaying the total 'Active Time' and 'Running Time' (i.e. the sum) a process has been used in a period of time, it's displaying a vague and ambiguous 'Average Active Time' and 'Average Active Time'. As a result, we can no longer have a clear view of how long a certain user has worked on a certain process for.


Why it is business critical

Citrix has two major advantages (IMO) over Remote Desktop Services; the rich reporting of EdgeSight and the ability to record videos of user sessions through SmartAuditor. Knowing what a user has been doing during a period of time and for how long is an extremely useful feature, applicable to a multitude of business scenarios. A company could be using this report to charge clients or pay employees depending on how much time they spent using a certain program (i.e. a process). This was the only report to provide the total (i.e. the sum) time a user spent working on a process within a given time window. Switching it to average times has struck a critical blow to the business process.


Ridiculous support

When in doubt Google it, right? Well sure, but results are not guaranteed. All search results that were worth a look pointed to the Citrix Support site, specifically the forums. Well, some poor souls wanted to know what happened to that report but either got ridiculous answers or were told to "modify the report to use sum instead of average" http://forums.citrix.com/message.jspa?messageID=1417709 (also a ridiculous answer, if they knew how to do it they wouldn't ask in the first place). Now, I would gladly re-post the same question hoping to press for a useful answer... but see in order to post you have to sign in, and to do that you need to register, for which you need to have a company account, which requires a sales contract number, and yes you've guessed it, you'll need to actually buy Citrix to get any sort of support!!!!! Of course you could be in the process of evaluating, as in my case, which is the process of solving issues and having your questions answered before you actually have to buy anything. A paradox obviously lost on people running Citrix support.


Troubleshooting attempt #1

Ok, change the 'Active Time Average' to active time sum, how hard can it be? Sure enough I loaded up the 'Process Usage' report on Report Builder, had a look at the DataSets and spotted 'active_secs' and 'running_secs' under 'edgesight' dataset. I thought, problem solved! But when I had a look at the actual query, on the first select statement I saw:
sum(active_secs) as active_secs, sum(running_secs) as running_secs
Here's a screen:

So if the query actually IS summing the records in the active_secs and running_secs columns, then we have a bigger problem related to database table design. Time for some alternative thinking.


Logical workaround?

Let's go back to the business need; we need to know how much time a user has spent using a specific program. Or do we? Let's step back and examine the logic behind what we need to achieve. Citrix can publish two things: Applications and Desktops. If you are publishing an application, the total time of the session is identical to the total process usage time, where process = published application.

Now if you're publishing a desktop,  the client/employee obviously needs to have access to more than a single application in order to do their work. So business-wise there is no point in measuring process usage, again, what makes much more sense is to measure the total session time.

In other words, we need a report that will be asking us for:
  • a username
  • a start time/date
  • an end time/date
 ...and then show us a sum of all session times for the selected user in the selected time period.

You see where I'm getting at, right? What other citrix feature records user session times? Yep, SmartAuditor.




Locating the info & building the query 


OK, here's the logical breakdown. In SmartAuditor Player, I'm randomly selecting a recorded video and in its properties I'm looking for some sort of unique identifier... bingo! It's called 'File ID'




Next step would be to find out where inside the SmartAuditor database is this information stored. But we're not going to look for 'File ID' as this might not be the acual column name or there may be several such columns in various tables. Instead we're goint to look for the actual data: 'e4e0d818-63ff-41c1-ab7b-6a58985e13b2'. Sure SmartAuditor doensn't have a huge database and we could do a column-by-column search, but that's just not ITGuy-ish.

So, here's a nifty stored procedure courtesy of SQLDennis @ http://blogs.lessthandot.com

CREATE PROCEDURE FindMyData_String
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO

So, execute this as a query on the CitrixSmartAuditor database, then execute the following query:


Exec FindMyData_string 'e4e0d818-63ff-41c1-ab7b-6a58985e13b2', 0




Struck gold! That 'File ID' is the actual recorded video file name and it exists on the 'FilePath' column of the ICLFile table. The same table holds almost all info we require such as StartTime and EndTime and Duration. There is ony one last piece of info needed to stitch everything together, the username. So, let's look inside the CitrixSmartAuditor database for columns that include 'name' in their name...

USE CitrixSmartAuditor
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%name%'
ORDER BY schema_name, table_name;




Down at the bottom is a table 'UserAccount' with a column name 'Name' which looks like the one we're looking. A quick view at the table contents confirms that the column 'Name' actually holds the username records. Now all that's needed is a common link between the two tables. I'm guessing that 'UserAccountID' from ICLFile and ID from UserAccount are the most likely candidates, so let's confirm this hypothesis:


select distinct UserAccountID from dbo.ICLFile

select distinct ID from dbo.UserAccount




We now have proof that the two tables are linked, so we have all the information we need to form our query. To formulate and test the query, we will use some set values which later at the Report Builder stage will become variables. These values are:
  • StartTime = '2011-06-02 00:00:00.000'
  • EndTime = '2011-06-02 23:59:59.000'
  • UserName = '0011234'
So, this is the query:


SUM(DURATION) from dbo.ICLFile
where StartTime >= '2011-06-02 00:00:00.000' and EndTime <= '2011-06-02 23:59:59.000' and
UserAccountID in
(select ID from dbo.UserAccount where Name = '0011234')

select StartTime, EndTime, filepath, Duration from dbo.ICLFile
where StartTime >= '2011-06-02 00:00:00.000' and EndTime <= '2011-06-02 23:59:59.000' and
UserAccountID in
(select ID from dbo.UserAccount where Name = '0011234')
order by StartTime



And these are the results:



Which is exactly what we need:
  • The total duration (in seconds)
  • the individual sessions with start & end time, recorded video location and filename, plus duration




Building the Report in Report Builder


We're almost there. Let's open MS SQL Report Builder and make sure we have a connection to our SQL Reporting Services server. Click the round button, go to 'Options' and under 'Settings' fill in the first field with your Report Server URL (e.g. http://myreportserver/reportserver). Click OK and then under 'Report Data' click on 'New' and then 'Data Source'.

Name the data source, then click on 'Browse' and choose 'Citrix Smart Auditor'



Click on OK abd you've got your data source set. Time to set up the data sets, we'll need two:

Right-click on the datasource and choose Dataset. Name the first dataset 'ListSessions' and click on 'Query Designer'. Click 'Edit asText' and paste the second half of the query described above, but change the actual values of StartTime, EndTime and Name with @StartTime, @EndTime and @UserName, as such:


select StartTime, EndTime, FilePath, Duration from dbo.ICLFile
where StartTime >= @StartTime and EndTime <= @EndTime and
UserAccountID in
(select ID from dbo.UserAccount where Name = @UserName)
order by StartTime






Click OK and two things happen: Your first dataset is ready and under Parameters you've got three new entries. Grand! Now for the second dataset which will sum the individual session times. Create a new dataset as described above, name it SumTime and paste the modified query below to Query Designer:

select SUM(DURATION) from dbo.ICLFile
where StartTime >= @StartTime and EndTime <= @EndTime and
UserAccountID in
(select ID from dbo.UserAccount where Name = @UserName)

Now that we've set the groundwork, we're ready to form the report. Modify the Report Title, then delete that 'Click to add data' box. Enlarge that work area, go to 'Insert', click on 'Table' and then 'Insert Table'. Draw a simple table, change the header row as shown in the image below, and on each of the boxes in the data row type [ID]. Now, on the center and right ID boxes, right click and choose 'fx Expression'. In the 'value' field type /60 on the center box and /3600 on the right box, as shown below:





Now right-click on each of the bottom row text boxes, choose 'Text Box Properties', go to 'Number' make the changes shown on the image below and click OK.



That should tidy-up the duration output in the report.

Now, using the same methodology described above, create a second table as shown in the following image:



And that was it! The report's ready. And this is what the output looks like:



Nothing fancy, but gets the job done!

Yours,


the IT Guy.






1 comment:

  1. TҺe flexibility Drupal offers аllows administrators tо quіckly shyape theіr CMS to tɦeir existing organizational
    chart. Іf you wаnt too dominate at Farm - Ville
    οn Facebook tɦеn I highly recommend picking սp a Farm -
    Vile Facebook Strategy Guide. ӏn thе map thеre are
    tҺree sections whee еach team "bottlenecks".

    Мy weblog hack for android

    ReplyDelete

Total Pageviews

Labels

Search This Blog

Popular Posts