Highlighted
Super Contributor.. Super Contributor..
Super Contributor..
361 views

Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Hi Experts,

Please provide an SQL query (Site Admin -> Lab Management) for finding the time slots who booked on what LG/Controller for a particular point of time.

As of now, finding who booked on what host in Lab Mangement -> Timeslots is very tedious task,  filter out the timeslots in Grid view and go through each timeslot for finding a particular host, but there is no option to filter with hosts alone. 

Please provide the SQL query to find the host details who booked on what time. Thanks.

 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Hello

Here is a slightly modified version of a SQL that we use to check the currently running tests. It involves the Siteadmin scheme and the Lab-management scheme (can have different names in your installation) and is working in a SQL Server 2008 R2.

I have added filter on date and hostname in the where-clause as I interpret your needs:

SELECT
  RSV_START_TIME 'Start',
  RSV_END_TIME 'End',
  proj.PROJECT_NAME 'Project',
  RSV_CREATED_BY 'Creator',
  RSV_ID,
  LRN_ID,
  RSV_LINKED_LT_NAME 'Test',
  DATEDIFF(HOUR,RSV_START_TIME,RSV_END_TIME) 'Duration (h)',
  LRN_STATE 'State',
  LRN_DURATION 'Elapsed (min)',
  LRN_VUSERS_INVOLVED 'vUsers',
  LRN_CONTROLLER_NAME 'Controller',
  LEN(LRN_LOAD_GENERATORS+'@')-LEN(REPLACE(LRN_LOAD_GENERATORS+'@',';','')) 'LGs',
  LRN_LOAD_GENERATORS 'Load Generators'
FROM default_lab_project_db.td.lab_reservations res
LEFT OUTER JOIN default_lab_project_db.td.lab_runs run ON res.RSV_CURRENT_LABRUN_ID = run.LRN_ID
LEFT OUTER JOIN pcsiteadmin1220_db.td.projects proj ON run.LRN_PROJECT_ID = proj.PROJECT_ID
WHERE
  RSV_END_TIME >= '2016-05-31 09:20' AND
  RSV_START_TIME <= '2016-05-31 09:20' AND
  LRN_LOAD_GENERATORS LIKE '%40%'
ORDER BY 1 DESC;

 

 

 

 

 

.

View solution in original post

Tags (1)
5 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Timeslot is a very complex area involving multiple DB tables and we do not have such a query at hand.

 

 

Elad
0 Likes
Highlighted
Trusted Contributor.
Trusted Contributor.

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Hi

Just inform you there is a new enhacement in PC 12.53 under Performance Center  Administrator site where you can see  usage of resources associated with a timeslot.

http://alm-help.saas.hpe.com/en/12.53/online_help/Content/LM/ui_reports_timeslot.htm

Regards 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Hello

Here is a slightly modified version of a SQL that we use to check the currently running tests. It involves the Siteadmin scheme and the Lab-management scheme (can have different names in your installation) and is working in a SQL Server 2008 R2.

I have added filter on date and hostname in the where-clause as I interpret your needs:

SELECT
  RSV_START_TIME 'Start',
  RSV_END_TIME 'End',
  proj.PROJECT_NAME 'Project',
  RSV_CREATED_BY 'Creator',
  RSV_ID,
  LRN_ID,
  RSV_LINKED_LT_NAME 'Test',
  DATEDIFF(HOUR,RSV_START_TIME,RSV_END_TIME) 'Duration (h)',
  LRN_STATE 'State',
  LRN_DURATION 'Elapsed (min)',
  LRN_VUSERS_INVOLVED 'vUsers',
  LRN_CONTROLLER_NAME 'Controller',
  LEN(LRN_LOAD_GENERATORS+'@')-LEN(REPLACE(LRN_LOAD_GENERATORS+'@',';','')) 'LGs',
  LRN_LOAD_GENERATORS 'Load Generators'
FROM default_lab_project_db.td.lab_reservations res
LEFT OUTER JOIN default_lab_project_db.td.lab_runs run ON res.RSV_CURRENT_LABRUN_ID = run.LRN_ID
LEFT OUTER JOIN pcsiteadmin1220_db.td.projects proj ON run.LRN_PROJECT_ID = proj.PROJECT_ID
WHERE
  RSV_END_TIME >= '2016-05-31 09:20' AND
  RSV_START_TIME <= '2016-05-31 09:20' AND
  LRN_LOAD_GENERATORS LIKE '%40%'
ORDER BY 1 DESC;

 

 

 

 

 

.

View solution in original post

Tags (1)
Highlighted
Super Contributor.. Super Contributor..
Super Contributor..

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Dear OlaBerggren,

Thank you for your query. Please pardon me for my limited understanding on the query. Seems this will give the results only when the run has started correct, due to it refer from the Lab_Run table. But is it possible to look for future date and time, who booked on particular hosts. Much appreciated for your response. Thank you.

My Schema names:

default_lab_project_db2.dbo.lab_reservations

default_lab_project_db2.dbo.lab_runs

[pcsiteadmin_12_prod_a_db].[dbo].PROJECTS

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Site Admin -> Lab Management SQL Query for Host details

Jump to solution

Hi

I looked it through once more and came to this conclusion.

If the hosts is booked as a specific LG, then it is possible to check who booked it or who is using it, with the SQL below.

But if one project have booked a Timeslot with 5 Automatch LGs and you want to book another timeslot at the same time,  but can't as there are not enough free. Then it is not possible to find by who booked a specific host by any SQL. These check is done by the logic when calculating timeslot availability.

br /Ola

SELECT 
RESERVATION_ID,
RSV_START_TIME 'Start',
RSV_END_TIME 'End',
proj.PROJECT_NAME,
RSV_CREATED_BY 'Creator',
HOSTS
FROM
(
-- From Reservation_demand
SELECT 'Reservation' 'Source', RVD_RESERVATION_ID 'RESERVATION_ID', host_name 'HOSTS'
FROM default_lab_project_db2.dbo.lab_reservations
INNER JOIN default_lab_project_db2.dbo.LAB_HOSTS hosts on hosts.host_name= demands.RVD_FILTERSTRING

UNION -- From Run
SELECT 'Run' 'Source', LRN_RESERVATION_ID 'RESERVATION_ID', LRN_CONTROLLER_NAME + ';'+ LRN_LOAD_GENERATORS 'HOSTS'
FROM default_lab_project_db2.dbo.lab_runs
) AS Valid_Reservation
INNER JOIN default_lab_project_db2.dbo.lab_reservations reservation ON reservation.RSV_ID= Valid_Reservation.RESERVATION_ID
LEFT OUTER JOIN pcsiteadmin_12_prod_a_db.dbo.PROJECTS proj ON reservation.RSV_PROJECT_UID = proj.PROJECT_UID
WHERE
HOSTS LIKE '%HOSTNAME%'
AND RSV_END_TIME >= '2016-05-31 09:20'
AND RSV_START_TIME <= '2016-05-31 09:20'
.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.