OYE VEY!!! Published Desktops in Edgesight 5.3

Reporting on Published Desktops in Edgesight 5.3

We recently made the move to published XenAPP Desktops in our environment and after the initial rollout I thought I would consult edgesight and see what kind of metrics and performance numbers were in place and see how many users were using the new published desktop. What I found was that published desktops do now show up in several of the views/tables in Edgesight and the data is basically missing for a number of canned reports.

After a call with Citrix and finding out that they will not have a fix for some time, I decided to see if I could find the data somewhere in the vast archives of Edgesight Data Model. What I found is that information about published Desktops does, in fact, appear to be there. It is in a table (not a view) called ctrx_session. Running the command

select * from ctrx_session where app_name = ‘#%desktopName%’
This will give you a list of published applications, if you don’t know the name of your published desktop than use the ‘like’ function to find it.

Once you know your Desktop name you will need to cross reference the sessid with the view vw_ctrx_archive_session_perf and compare the sessid column type the following query to get counts on your published desktop. (Inner join folks can type it their way as well, note the alias of “s” and “p” for the tables)

select convert(varchar(10),dateadd(hh,-4,s.dtlast),101), count(distinct [user])
from ctrx_session s , vw_ctrx_archive_session_perf p
where p.sessid = s.sessid
and s.app_name = ‘#%virtual Desktop’
group by convert(varchar(10),dateadd(hh,-4,s.dtlast),101)
order by convert(varchar(10),dateadd(hh,-4,s.dtlast),101)

If you want to get the users who have accessed your virtual desktop you would type the following query

select distinct [user]
from ctrx_session s, vw_ctrx_archive_session_perf p
where p.sessid = s.sessid
and s.app_name like ‘%virtual%’

Comparing the “sessid” and specifying the “#Virtual Desktop” in your queries will also get you user names, system statistics such as CPU, Memory, etc but it does not get you any latency statistics which really sucks! I am hoping Citrix makes it a priority to get this fixed. Basically, you will not be able to tell a user who complains about their XenAPP Session that they had a latency of 500ms. One of the nice things about Edgesight was the ability to gather latency data, bummer!

Conclusion:
I know this may not be much of a consolation prize but if you want to report usage, usernames, etc you at least can grab some of that information. If there is a specific metric (that does not have to do with latency) please let me know and I will try to send you a query that will work for you. I have been able to answer all but one question that has been sent to me.

Thanks for reading!

John

ALSO NOTE: I am on eastern standard time so my query of the dtlast column has a “dateadd(hh,-4,dtlast). Adjust this according to your time zone.

2 thoughts on “OYE VEY!!! Published Desktops in Edgesight 5.3”

  1. Citrix Systems
    9.5in
    7.71in
    0in
    0in
    0in
    0in
    7.71in

    2pt
    2pt
    2pt
    2pt
    700
    Verdana
    #666699
    8pt
    White
    Middle

    0.25in
    5.71in
    true
    =Globals!ReportName

    2pt
    2pt
    2pt
    2pt
    700
    Verdana
    #666699
    7pt
    White
    Middle
    Right
    Underline

    0.25in
    5.71in
    2.02in
    true
    Related Reports

    =”javascript:void(window.showModalDialog(‘” & Parameters!BaseUrl.Value & “/smgr/linkpicker.aspx?” & Code.ConvertToString(Parameters!ParamString.Value) & “&report=” & Globals!ReportName & “‘, self, ‘dialogHeight:350px;dialogWidth:350px;help:no;center:yes;edge:raised;resizable:no;scroll:no;status=no;copyhistory=yes;’))”

    =”Click to see Related Reports”

    2pt
    Verdana
    #e6e4d4

    #D1CCB6

    Solid

    7pt
    2pt
    2pt
    2pt
    Middle

    0.25in
    7.73in
    true
    =

    ” ” & “Usage” &

    ” for ” & Parameters!FilterName.Value &
    ” and process ” & ControlChars.Quote & Parameters!Application.Value &
    ” (” & Parameters!Exe.Value &
    Iif(Len(Iif((Parameters!ImageFlags.Value AND 16) = 16, “(x64)”, “”)) > 0, “, ” & Iif((Parameters!ImageFlags.Value AND 16) = 16, “(x64)”, “”), “”) &
    Iif(Len(Parameters!AppVer.Value) > 0, “, ” & Parameters!AppVer.Value, “”) &
    “)” & ControlChars.Quote &

    ” between ” & Format(today.AddDays(Parameters!Start.Value),”d”) &
    ” and ” & Format(today.AddDays(Parameters!End.Value),”d”) & “.”

    .25in

    true
    true

    0.5in

    Usage for a Process by Day

    3.075in

    #d1ccb6

    Solid

    0.25in

    2

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Left
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Date

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Right
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Devices

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Right
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Sessions

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Right
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Avg Running Cnt

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Right
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Active Time

    2

    Verdana
    #E9E7D8

    #d1ccb6

    Solid
    Solid

    Middle
    Right
    7pt
    700
    Black
    2pt
    2pt
    2pt
    2pt

    true
    Running Time

    true

    0.2in

    White

    #D1CCB6

    None

    =”javascript:void(window.showModalDialog(‘” & Parameters!BaseUrl.Value & “/smgr/linkpicker.aspx?selected=” & Code.StripField(“Fields!device.Value”) & “&” & Code.ConvertToString(Parameters!ParamString.Value) & “&report=” & Globals!ReportName & “&rp:” & Code.StripField(“Fields!device.Value”) & “=” & Code.ConvertToString(Fields!device.Value) & “&rp:” & Code.StripField(“Fields!day.Value”) & “=” & Code.ConvertToString(Fields!day.Value) & “‘, self, ‘dialogHeight:350px;dialogWidth:350px;help:no;center:yes;edge:raised;resizable:no;scroll:no;status=no;copyhistory=yes;’))”

    =”Click for Related Reports for this ” & Code.StripField(“Fields!device.Value”)

    Verdana
    7pt

    #D1CCB6

    Solid

    Underline
    White
    Middle
    Left
    2pt
    2pt
    2pt
    2pt

    =Fields!device.Value
    true

    =”javascript:void(window.showModalDialog(‘” & Parameters!BaseUrl.Value & “/smgr/linkpicker.aspx?selected=” & Code.StripField(“Fields!user.Value”) & “&” & Code.ConvertToString(Parameters!ParamString.Value) & “&report=” & Globals!ReportName & “&rp:” & Code.StripField(“Fields!user.Value”) & “=” & Code.ConvertToString(Fields!user.Value) & “&rp:” & Code.StripField(“Fields!day.Value”) & “=” & Code.ConvertToString(Fields!day.Value) & “‘, self, ‘dialogHeight:350px;dialogWidth:350px;help:no;center:yes;edge:raised;resizable:no;scroll:no;status=no;copyhistory=yes;’))”

    =”Click for Related Reports for this ” & Code.StripField(“Fields!user.Value”)

    Verdana
    7pt

    #D1CCB6

    Solid

    Underline
    White
    Middle
    Left
    2pt
    2pt
    2pt
    2pt

    =Fields!user.Value
    true

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    =Fields!sessions.Value
    true

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    #0.0
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    =Fields!running_count.Value
    true

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    =Code.SecsToString(Fields!active_secs.Value)
    true

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    =Code.SecsToString(Fields!running_secs.Value)
    true

    White

    None

    Date
    true

    =Fields!day.Value
    Ascending

    0.25in

    2

    =”javascript:void(window.showModalDialog(‘” & Parameters!BaseUrl.Value & “/smgr/linkpicker.aspx?selected=” & Code.StripField(“Fields!day.Value”) & “&” & Code.ConvertToString(Parameters!ParamString.Value) & “&report=” & Globals!ReportName & “&rp:” & Code.StripField(“Fields!day.Value”) & “=” & Code.ConvertToString(Fields!day.Value) & “‘, self, ‘dialogHeight:350px;dialogWidth:350px;help:no;center:yes;edge:raised;resizable:no;scroll:no;status=no;copyhistory=yes;’))”

    =”Click for Related Reports for this ” & Code.StripField(“Fields!day.Value”)

    Verdana
    7pt

    #D1CCB6

    Solid

    Underline
    White
    d
    Middle
    Left
    2pt
    2pt
    2pt
    2pt

    true
    =Fields!day.Value

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    #,##
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    true
    =CountDistinct(Fields!instid.Value)

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    #,##
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    true
    =sum(Fields!sessions.Value)

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    #0.0
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    true
    =avg(Fields!running_count.Value)

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    true
    =Code.SecsToString(sum(Fields!active_secs.Value))

    2

    Verdana
    7pt

    #D1CCB6

    Solid

    White
    Middle
    Right
    2pt
    2pt
    2pt
    2pt

    true
    =Code.SecsToString(sum(Fields!running_secs.Value))

    0.05in

    White

    None

    White

    None

    White

    None

    White

    None

    White

    None

    White

    None

    White

    None

    White

    None

    Date
    true

    0.2in

    White

    None

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Left
    Black
    2pt
    2pt
    2pt
    2pt

    Device

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Left
    Black
    2pt
    2pt
    2pt
    2pt

    User

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Right
    Black
    2pt
    2pt
    2pt
    2pt

    Sessions

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Right
    Black
    2pt
    2pt
    2pt
    2pt

    Avg Running Cnt

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Right
    Black
    2pt
    2pt
    2pt
    2pt

    Active Time

    Verdana

    #808080

    #D9D7CA

    Solid
    Solid
    Solid
    Solid

    7pt
    Middle
    Right
    Black
    2pt
    2pt
    2pt
    2pt

    Running Time

    White

    None

    Date
    true

    =Fields!day.Value

    =Fields!day.Value
    Ascending

    edgesight

    0.1in

    1.9in

    1.5in

    0.9in

    1.2in

    1.0in

    1.0in

    0.1in

    0.1in
    No Data Available
    2.875in
    Excel
    edgesight
    0
    Column

    false

    Verdana
    10pt
    100pt
    Center
    #E9E7D8

    #D1CCB6

    Solid

    true

    Solid

    7pt
    Verdana

    BottomCenter
    Row

    =sum(Fields!active_secs.Value)/60

    8pt
    Verdana

    Solid

    6pt

    =sum(Fields!running_secs.Value)/60

    8pt
    Verdana

    Solid

    6pt

    Verdana
    7pt

    true

    DarkGray

    Solid

    Solid

    Outside
    0
    true
    true

    =Fields!day.Value

    =Fields!day.Value
    Ascending

    =Format(Fields!day.Value,”d”)

    Active Time

    Running Time

    White

    Solid

    Minutes

    Verdana
    8pt

    Verdana
    7pt

    true

    Solid

    true

    DarkGray

    Dashed

    Outside
    0
    true
    true
    true

    White

    0.5in

    edgesight

    System.Web, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

    Function SecsToString(totalSecs as Double) as String
    dim days, hours, mins, secs, toReturn

    If (totalSecs > 0) Then

    totalSecs = Math.Round(totalSecs)

    secs = totalSecs mod 60
    totalSecs -= secs

    mins = totalSecs mod 3600
    totalSecs -= mins
    mins = mins/60

    hours = totalSecs mod 86400
    totalSecs -= hours
    hours = hours/3600

    days = totalSecs/86400

    If (days = 1) Then
    toReturn = “1 day, ”
    Else If (days > 0) Then
    toReturn = days & ” days, ”
    End If

    toReturn &= hours & “:”
    If (mins < 10)
    toReturn &= “0”
    End If

    toReturn &= mins & “:”
    If (secs < 10)
    toReturn &= “0”
    End If

    toReturn &= secs
    Else
    toReturn = “”
    End If

    Return toReturn
    End Function

    Function TrimReportName(sReportName as String) as String
    Return Mid(sReportName, InStr(sReportName, “/”) + 1)
    End Function

    Function ConvertToString(obj)
    If (obj.GetType().ToString() = “System.DateTime”) Then
    Return Format(obj, “yyyy-MM-dd”)
    Else

    Return obj.ToString()

    End If
    End Function

    Function StripField(field)
    Return StrConv(field.ToString().SubString(field.ToString().IndexOf(“!”) + 1, (field.ToString().IndexOf(“.”) – field.ToString().IndexOf(“!”) – 1)), VbStrConv.ProperCase)
    End Function

    instid

    sessions

    active_secs

    running_secs

    running_count

    device

    user

    day

    edgesight

    select u.instid as instid,
    count(distinct u.sessid) as sessions,
    sum(u.active_time) as active_secs,
    sum(u.running_time) as running_secs,
    avg(running_count) as running_count,
    m.name as device,
    p.domain_name + ‘\’ + p.account_name as [user],
    dt.local_day as day
    from usage u
    join [image] im on (im.imid = u.imid)
    join instance i on (i.instid = u.instid)
    join core_dtperiod dt on (dt.dtid = u.dtid and dt.compid = i.compid)
    join machine m on (m.machid = i.machid)
    join session s on (s.sessid = u.sessid)
    join principal p on (p.prid = s.prid)
    where dt.local_day between convert(char,dateadd(d,@Start, getutcdate()),112) and convert(char,dateadd(d,@End, getutcdate()),112)
    and i.instid in (select * from udf_core_sub_inst(@Filter))
    and im.description = @Application and im.filename = @Exe
    and ((Len(@AppVer) = 0) or (im.file_version = @AppVer))
    and im.flags & @ImageFlags != 0
    and (u.running_time >= u.active_time)
    group by u.instid, dt.local_day, m.name, p.domain_name, p.account_name
    order by dt.local_day

    =Parameters!Filter.Value

    =Parameters!Application.Value

    =Parameters!Exe.Value

    =Parameters!AppVer.Value

    =Parameters!ImageFlags.Value

    =Parameters!Start.Value

    =Parameters!End.Value

    =Parameters!BaseUrl.Value

    name

    edgesight

    if (dbo.udf_core_filter_is_group(@Filter) <> 0) begin
    select ‘group “‘ + [name] + ‘”‘ as name from [group] where groupid = dbo.udf_core_filter_to_group(@Filter)
    end
    else begin
    if ((convert(int, @Filter) & 0x40000000) <> 0) begin
    select ‘department “‘ + path + ‘”‘ as name from dept where deptid = (convert(int, @filter) & ~convert(int, 0x40000000))
    end
    else begin
    select ‘department “‘ + path + ‘”‘ as name from dept where deptid = @Filter
    end
    end

    =Parameters!Filter.Value

    Integer
    false
    Filter

    String
    false
    Process

    String
    false
    FileName

    String
    true
    true
    Version

    String
    false
    ImageFlags

    Integer
    false
    Start

    Integer
    false
    End

    String

    FilterName
    name

    FilterName

    String
    true
    true
    BaseUrl

    String
    true
    true
    ParamString

    0.1in
    0in
    7.73in

    Solid

    2pt
    Verdana
    7pt
    Left
    2pt
    2pt
    2pt

    0.1in
    true
    = “Generated ” & Format(Globals!ExecutionTime,”d”) & ” | EdgeSight 4.5 | © Citrix Systems, Inc. | Server ” & Globals!ReportServerUrl.SubString(7,(Globals!ReportServerUrl.IndexOf(“/”,8) ) -7 )
    6in

    2pt
    Verdana
    7pt
    Right
    2pt
    2pt
    2pt

    0.1in
    true
    =”Page ” & Globals!PageNumber & ” of ” & Globals!TotalPages
    6in
    1.71in

    true
    true

    0.5in

    ______________

    This custom report does not work on 5.3 can you help me identify errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s