SQL query to determine last time a published application was launched, in the Management, Maintenance & Reporting forum on BrianMadden.com
Brian Madden Logo
Your independent source for desktop virtualization, consumerization, and enterprise mobility management.

SQL query to determine last time a published application was launched, in the Management, Maintenance & Reporting forum on BrianMadden.com

rated by 0 users
Answered (Verified) This post has 1 verified answer | 3 Replies | 2 Followers

Top 50 Contributor
Points 5,427
Mark Elliott posted on Fri, Jan 16 2009 7:41 AM

I'm trying to create a SQL query that will query the RM Summary Database and, for each published application, return the last time the application was launched. I know I need to use the LU_APPNAME and SDB_APPHISTORY tables to return LU_APPNAME.APPNAME, SDB_APPHISTORY.STARTTIME, SDB_APPHISTORY.ENDTIME but I can't work out how to only return the row that contains the latest STARTTIME.

I tried the following command which appeared to work but on closer investigation found it was not returning the correct data.

 

SELECT distinct APPNAME1.APPNAME, APPHISTORY1.STARTTIME, APPHISTORY1.ENDTIME

FROM LU_APPNAME APPNAME1, SDB_APPHISTORY APPHISTORY1

WHERE APPHISTORY1.STARTTIME=
 (SELECT max(APPHISTORY2.STARTTIME)
 
  FROM  SDB_APPHISTORY APPHISTORY2
 
  WHERE APPNAME1.PK_APPNAMEID =   APPHISTORY2.FK_APPNAMEID)

ORDER BY APPHISTORY1.STARTTIME ASC

 

Has anyone got any ideas?

  • | Post Points: 20

Answered (Verified) Verified Answer

Top 50 Contributor
Points 5,427
Verified by Mark Elliott

This seemed to do the trick. What threw me was that I was checking the details of the published desktop users use every day but the latest date being displayed was from a month ago. I checked all the rows in SDB_APPHISTORY table that matched this published desktop and found the query was returning the correct result. Now I need to work out why it is not updating the Summary DB

SELECT DISTINCT SDB_APPHISTORY1.FK_APPNAMEID, SDB_APPHISTORY1.STARTTIME, SDB_APPHISTORY1.ENDTIME, LU_APPNAME.APPNAME


FROM SDB_APPHISTORY SDB_APPHISTORY1, LU_APPNAME

WHERE SDB_APPHISTORY1.STARTTIME=
(SELECT max(SDB_APPHISTORY2.STARTTIME)
FROM SDB_APPHISTORY SDB_APPHISTORY2
WHERE SDB_APPHISTORY1.FK_APPNAMEID = SDB_APPHISTORY2.FK_APPNAMEID)

AND SDB_APPHISTORY1.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
ORDER BY SDB_APPHISTORY1.FK_APPNAMEID ASC

  • | Post Points: 40

All Replies

Top 25 Contributor
Points 14,654

This will not help with the SQL script, but you can monitor changes in PS4.5 and XenApp 5 with Configuration Logging(cannot remember if there is avail. on all editions).  Also you can run an output script on the Published Applications and do a diff by date (pubAppRpt.vbs):

http://www.thomaskoetzing.de/index.php?option=com_content&task=view&id=132&Itemid=204

I will have a chat with our DBA's because I bet they can russle up someting interesting.

--Emil

  • | Post Points: 20
Top 50 Contributor
Points 5,427
Verified by Mark Elliott

This seemed to do the trick. What threw me was that I was checking the details of the published desktop users use every day but the latest date being displayed was from a month ago. I checked all the rows in SDB_APPHISTORY table that matched this published desktop and found the query was returning the correct result. Now I need to work out why it is not updating the Summary DB

SELECT DISTINCT SDB_APPHISTORY1.FK_APPNAMEID, SDB_APPHISTORY1.STARTTIME, SDB_APPHISTORY1.ENDTIME, LU_APPNAME.APPNAME


FROM SDB_APPHISTORY SDB_APPHISTORY1, LU_APPNAME

WHERE SDB_APPHISTORY1.STARTTIME=
(SELECT max(SDB_APPHISTORY2.STARTTIME)
FROM SDB_APPHISTORY SDB_APPHISTORY2
WHERE SDB_APPHISTORY1.FK_APPNAMEID = SDB_APPHISTORY2.FK_APPNAMEID)

AND SDB_APPHISTORY1.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
ORDER BY SDB_APPHISTORY1.FK_APPNAMEID ASC

  • | Post Points: 40
Not Ranked
Points 8
Jeremy replied on Wed, Feb 25 2009 12:23 PM

Mark,

hope this isn't too late for you - I only joined today!

If I've understood correctly what you want to achieve then you don't want to use the APPHISTORY table. This table stores the information about when applications were loaded or retired from the farm.

I specialise in extracting valuable MI from both Citrix RM and Edgesite so if you need any more help just give me a call, my number is on my profile.

The following SQL should give you what you need.

 

 

 

 

 

 

SELECT

 

 

distinct APPNAME1.APPNAME, SDB_SESSION1.SESSIONSTART, SDB_SESSION1.SESSIONEND

FROM

 

LU_APPNAME APPNAME1, SDB_SESSION SDB_SESSION1

WHERE

 

SDB_SESSION1.SESSIONSTART=

 

 

(SELECT max(SDB_SESSION2.SESSIONSTART)

 

 

 

FROM SDB_SESSION SDB_SESSION2

 

 

 

WHERE APPNAME1.PK_APPNAMEID = SDB_SESSION2.FK_APPNAMEID)

ORDER

 

 BY SDB_SESSION1.SESSIONSTART ASC

  • | Post Points: 8
Page 1 of 1 (4 items) | RSS