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?
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_APPHISTORY2WHERE SDB_APPHISTORY1.FK_APPNAMEID = SDB_APPHISTORY2.FK_APPNAMEID)
AND SDB_APPHISTORY1.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEIDORDER BY SDB_APPHISTORY1.FK_APPNAMEID ASC
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):
I will have a chat with our DBA's because I bet they can russle up someting interesting.
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.
LU_APPNAME APPNAME1, SDB_SESSION SDB_SESSION1
BY SDB_SESSION1.SESSIONSTART ASC