Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

SQL Query for custom reports in Workspace Streaming

Created: 10 May 2013 • Updated: 29 May 2013 | 5 comments
This issue has been solved. See solution.

Hey Everyone,

How can you perform a manual custom SQL query based on the out of box reports?  The reason I ask is that our reports are continually failing due to time outs from exceeding the default value.  I noticed that you can change that per the Admin Guide:

 

<AppStr:DAReportConnectionTimeout>180000</AppStr:DAReportConnectionTimeout> in consoleconf.xml under <installedDir>\Console\conf folder.
 

However, performance using the out of box reporting seems sluggish in comparison to just running a report directly on the SQL server.  So, when a report fails and times out, is there a log of the query it is performing so that it can be manually run?  This would be neat as well because then a workflow can be set up to automatically generate a report based on that criteria at regular intervals.  Thanks all.

Operating Systems:

Comments 5 CommentsJump to latest comment

Palvaran's picture

We tried changing the value to a higher value by increasing it a factor of 10 to 1800000 so that reports would timeout after 30 minutes instead of 3.  However, we noticed that regardless of what value is set, reports still timeout after 3 minutes.

I found the reports.xml template at \Workspace Streaming\Server\streamletEngine\conf\reports.xml, but do not see the syntax for a concurrent usage report there.  However, I see the report for licenseConsumedPerUserReport.  Is this the same thing?  Here is the syntax.

 

SELECT
CASE
WHEN (l.user_name IS NOT NULL AND l.user_name <> '''') THEN l.user_name
WHEN (i.last_used_by IS NOT NULL) THEN i.last_used_by
ELSE ''unknown''
END AS licensed_user,
p.package_id as package_id,
p.package_name,
p.package_version,
ld.name as license_descriptor,
h.host_name,
 
ldp.package_guid,
l.host_id, l.last_used
FROM
as_license l
INNER JOIN
as_license_descriptor ld
ON l.license_desc_id = ld.id
INNER JOIN
as_r_ldescr_pkg ldp
ON ld.id = ldp.license_desc_id
INNER JOIN
as_host h
ON l.host_id = h.host_id
INNER JOIN
as_inventory i
ON h.host_id = i.host_id
AND
ldp.package_guid = i.package_guid
INNER JOIN
as_package p
ON i.package_guid = p.package_id
AND
i.package_version = p.package_version
WHERE
l.user_name <> ''SYSTEM'' AND i.last_used_by <> ''SYSTEM''
AND (l.user_name in ({0}) OR i.last_used_by in ({0}))
UNION
SELECT
CASE
WHEN (l.user_name IS NOT NULL AND l.user_name <> '''') THEN l.user_name
WHEN (i.last_used_by IS NOT NULL) THEN i.last_used_by
ELSE ''unknown''
END AS licensed_user,
p.package_name,
p.package_version,
ld.name as license_descriptor,
null host_name ,
 
ldp.package_guid,
l.host_id, l.last_used
FROM
as_license l
INNER JOIN
as_license_descriptor ld
ON l.license_desc_id = ld.id
INNER JOIN
as_r_ldescr_pkg ldp
ON ld.id = ldp.license_desc_id
INNER JOIN
 
as_inventory i
ON  l.user_name = i.last_used_by
AND
ldp.package_guid = i.package_guid
INNER JOIN
as_package p
ON i.package_guid = p.package_id
AND
i.package_version = p.package_version
WHERE
l.user_name <> ''SYSTEM'' AND i.last_used_by <> ''SYSTEM''
AND (l.user_name in ({0}) OR i.last_used_by in ({0}))
ORDER BY
licensed_user,
p.package_name, p.package_version, l.last_used
 
 
Is this the right report?  If I want to Query it to report on usage for a specific period (Jan 3rd 2013 to today) on all concurrent usage, what fields need to be modified?
 

 

Systems Administrator
Rice University

Remember, "The happiness of your life, depends on the quality of your thoughts."

laxmanan's picture
Hi,
 
Concurrent License Usage Report:
 
Example: Consurrent License Usage Report from 2013-05-26 22:00:55 to 2013-05-27 22:00:55
 
SELECT package_name,
    
  CONVERT(varchar(10), timeslot, 101) AS daily,
  MAX(concurrent) AS max_concurrent,
  p.package_id
  FROM
           (SELECT package_id, TS.event_time as timeslot, COUNT(distinct Sessions.host_name) AS concurrent
              FROM
                (
                  /* Use session start and end event occurrences as time grid */
                  SELECT DISTINCT event_time
                    FROM as_rep_event
                   WHERE (event_id=1 OR event_id=4)
                     AND event_time >= {ts '2013-05-26 22:00:55' }
                     AND event_time <  {ts '2013-05-27 22:00:55' } 
                ) TS
                JOIN
                (
                    /* Select local tier non-system sessions, calculate session end time if needed */
                    SELECT  distinct si.host_name, rpkg.package_id,
 
                           e_start.event_time AS session_start,
              { fn IFNULL(e_end.event_time, { fn NOW()} ) } AS session_end
                    FROM 
                           {oj as_rep_session_info si LEFT OUTER JOIN as_rep_event e_end
                   ON si.session_type<>1
                  
                      AND (si.session_id=e_end.session_id and si.node_id=e_end.node_id)
 
                      AND e_end.event_id=4 },
               as_rep_event e_start,
               as_rep_node n, as_component c, as_rep_package rpkg,as_rep_application rapp
             WHERE
               (si.session_id=e_start.session_id AND si.node_id=e_start.node_id AND e_start.event_id=1)
 and   si.app_pk=rapp.app_pk  
        and rapp.package_pk=rpkg.package_pk  
               AND (e_end.event_time IS NULL OR e_end.event_time > {ts '2013-05-26 22:00:55' })
               AND (e_start.event_time <  {ts '2013-05-27 22:00:55' } and e_start.event_time >= convert(datetime,cast(convert(datetime, {ts '2013-05-26 22:00:55' },101)as float) - 3, 101))
                AND n.id=si.node_id 
                AND n.name=c.name       
               AND (c.type=12 and c.parent_id=0) 
 
                                      
                ) Sessions
                ON
                  TS.event_time >= Sessions.session_start  AND
                  TS.event_time <  Sessions.session_end
              GROUP BY Sessions.package_id, TS.event_time
            ) tsapp
   , as_rep_package p
WHERE
 tsapp.package_id=p.package_id
 
GROUP BY
 package_name, p.package_id, CONVERT(varchar(10), timeslot, 101) /* group by day */
ORDER BY
 package_name, daily
 
 
-------------------------------------------------------------------------------------------------------------------------------------------------------
 
Licenses consumed by user:
 
 
Example: Licenses consumed By user - Administrator
 
 
SELECT
CASE
WHEN (l.user_name IS NOT NULL AND l.user_name <> '') THEN l.user_name
WHEN (i.last_used_by IS NOT NULL) THEN i.last_used_by
ELSE 'unknown'
END AS licensed_user,
p.package_name,
ld.name as license_descriptor,
h.host_name,
CONVERT(CHAR(19),l.last_used,121),
ldp.package_guid,
l.host_id
FROM
as_license l
INNER JOIN
as_license_descriptor ld
ON l.license_desc_id = ld.id
INNER JOIN
as_r_ldescr_pkg ldp
ON ld.id = ldp.license_desc_id
INNER JOIN
as_host h
ON l.host_id = h.host_id
INNER JOIN
as_inventory i
ON h.host_id = i.host_id
AND
ldp.package_guid = i.package_guid
INNER JOIN
as_package p
ON i.package_guid = p.package_id
AND
i.package_version = p.package_version
WHERE
l.user_name <> 'SYSTEM' AND i.last_used_by <> 'SYSTEM'
AND (l.user_name in ('Administrator') OR i.last_used_by in ('Administrator'))
UNION
SELECT
CASE
WHEN (l.user_name IS NOT NULL AND l.user_name <> '') THEN l.user_name
WHEN (i.last_used_by IS NOT NULL) THEN i.last_used_by
ELSE 'unknown'
END AS licensed_user,
p.package_name,
ld.name as license_descriptor,
null host_name ,
CONVERT(CHAR(19),l.last_used,121),
ldp.package_guid,
l.host_id
FROM
as_license l
INNER JOIN
as_license_descriptor ld
ON l.license_desc_id = ld.id
INNER JOIN
as_r_ldescr_pkg ldp
ON ld.id = ldp.license_desc_id
INNER JOIN
 
as_inventory i
ON  l.user_name = i.last_used_by
AND
ldp.package_guid = i.package_guid
INNER JOIN
as_package p
ON i.package_guid = p.package_id
AND
i.package_version = p.package_version
WHERE
l.user_name <> 'SYSTEM' AND i.last_used_by <> 'SYSTEM'
AND (l.user_name in ('Administrator') OR i.last_used_by in ('Administrator'))
ORDER BY
licensed_user,
p.package_name

 

SOLUTION
Palvaran's picture

Firstly, welcome to the Symantec forums Laxmanan!

Secondly, I appreciate your assistance.  Thanks a lot for taking the time to look over the coding.

Both queries that you wrote appear to be good, but when we run them inside our SQL servers, we are getting the following errors:

 

  • For the concurrent usage report we receive the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'as_rep_event'.
 
  • For the licenses consumed by user we receive the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'as_license'.

 

Any ideas or thoughts?  I see that you are calling the table for dbo.as_rep_event and querying the column for event_time so I am not sure what is erroring out.

sql2a.jpg

 

Systems Administrator
Rice University

Remember, "The happiness of your life, depends on the quality of your thoughts."

laxmanan's picture

Hi,

Thank you.

You would have given a db user while configuring the database in workspace streaming. You can see the user name in <Server Installed folder>\streamletEngine\da\conf\da.conf.(under MS SQL section)

Try to log in to MS SQL Management studio using that user, else  append the user name to all tables in  the query. 

Example: if the user name is asuser in the configuration file, asuser.as_rep_event.

Palvaran's picture

Thanks Laxmanan!  That did it!  I reran the query using the credentials for asuser and it worked flawlessly.  I noticed that there are 3 sets of dates so I simply changed the first value in each to an earlier time period and it appears to be working.  Thanks much!

Systems Administrator
Rice University

Remember, "The happiness of your life, depends on the quality of your thoughts."