End Point Queue Legth

We are using VCM version 8.1 sp1 and we do have requirement of recording the queue length for each endpoint for all vignette servers(say 16) on a daily basis .We want to generate a report similar to http://host:27110/jobConsole/secure/loadEndpoints.do through standalone code.

Right now we are doing it manually and going forward we want to automate it in order to reduce human errors.

From DB perspective we came to know that vgnsubjob table will hold the details of the current running jobs.But we are unable to group them by end point and get the count for each endpoint.

Please help if there is any alternative to get this count like sample query or vignette api

Find more posts tagged with

Comments

Doug_McLaren

I can't think of anywhere where we've documented the SQL to get these things.

That said, I think the place to start is the JobTool option in the WEM tools. You could make something that parses the html version at (usually) http://whatever:27001/tools/jsp/jobtool.jsp, or you could try the command line version we offer in support/commandline/jobtool.sh -- I've never used it personally, but it looks like it might give what you need.

Beyond that, we could probably look at the source for the jobtool page and work out the SQL you'd need, though that's going to be a bit more involved -- opening a ticket would probably be required.

Evan_Bench

what I've done before is used a stored procedure to get this info and then have it executed from code via program task, listener... Here's an example query that will do what you want but uses job and job history. you could add a filter to parse based on end-point but info woudl be the same.

(Attached is the results of this query)
use vcmsys
go

SELECT distinct
--ep.name as 'Endpoint name'
--,ep.id
job.type as 'Type'
,sj.state as 'State',
items.lastModifier 'Last Modifier'
,itemsInJob.Items
,datediff(ss,job.createDate,job.endDate) as 'Full Duration'
,datediff(ss,job.createDate,sj.startDate) as 'Job Creation Duration'
,datediff(ss,case when sj.inprocessDate>(select max(sj2.endDate) from [vcmsys].[vcmsys].[vgnSubJobHistory] sj2 with(nolock) where sj2.endDate<sj.endDate)
then sj.inprocessDate else (select max(sj2.endDate) from [vcmsys].[vcmsys].[vgnSubJobHistory] sj2 with(nolock) where sj2.endDate<sj.endDate) end,sj.endDate)/itemsInJob.Items as 'Duration/Item P'
,datediff(ss,sj.inprocessDate,sj.endDate) as 'P Duration'
,datediff(ss,case when job.createDate>(select max(job2.endDate) from [vcmsys].[vcmsys].vgnJob job2 with(nolock) where job2.endDate<job.endDate)
then job.createDate else (select max(job2.endDate) from [vcmsys].[vcmsys].vgnJob job2 with(nolock) where job2.endDate<job.endDate) end,job.endDate) as 'from last job'

--,(select max(job2.endDate) from [vcmsys].[vcmsys].vgnJob job2 where job2.endDate<job.endDate) as lastJobEndDate
,job.createDate as 'Job CreateDate'
,sj.inprocessDate as 'P StartDate'
,job.endDate as 'Job endDate'
--,Publish_date
,sj.jobId as 'Job Id'
--,jobItems.primaryReferrerId
--,jobItems.itemId -- sub items
--,subitems.name -- sub items name
--,stage.stageId
--,items.id as 'Parent Item Id'
,items.displayName as 'Comp Name'
,items.Name as 'Title'
,items.Guid as itemID
--,ItemsCount

FROM [vcmsys].[vcmsys].[vgnSubJobHistory] sj with(nolock)
left join [vcmsys].[vcmsys].[vgnEpInfo] ep with(nolock) on sj.epId = ep.id
left join [vcmsys].[vcmsys].vgnJob job with(nolock) on sj.jobId=job.id
left join vcmsys.vcmsys.vgnJobItemOrder jobItems with(nolock) on sj.jobId=jobItems.jobId
left join (select jobId,count(itemId) as Items from vcmsys.vcmsys.vgnJobItemStage with(nolock) group by jobId) itemsInJob on sj.jobId=itemsInJob.jobId
--left join [vcmsys].[vcmsys].[vgnAsMoMetaData] subitems on jobItems.itemId=subitems.[contentMgmtId]
--left join [vcmsys].[vcmsys].vgnJobItemAutoStage stage on jobItems.jobId=stage.jobId
left join
( SELECT guid.[id],cType.name as 'Fname',cType.displayName
,replace(guid.[name],guid.[id],'') as 'Name'
,guid.[id] as 'Guid'
,DATEADD(s, meta.publishDate/1000, '19700101 00:00:00') as 'Publish_date'
,guid.[creationTime]
,guid.[lastModTime]
,guid.[lastModifier]
,cType.[dataSourceLabel]
,guid.[creator]
FROM [vcmsys].[vcmsys].[vgnCMSObject] as guid with(nolock)
inner join [vcmsys].[vcmsys].[vgnAsMoMetaData] as meta with(nolock)
on guid.[id] =meta.[contentMgmtId]
inner join [vcmsys].[vcmsys].[vgnAsObjectType] as cType with(nolock)
on cType.id=meta.[objectTypeId]
) items
on jobItems.primaryReferrerId=items.id
left join
(select jobId,count(itemId) as ItemsCount
from [vcmsys].[vcmsys].vgnJobItemOrder with(nolock)
group by jobId) Subitems
on sj.jobId=Subitems.jobId
where datediff(ss,job.createDate,getdate())<7000 and
ep.name='Appsvcs'
--and 'Duration/Item P' > '10'
--and items.displayName = N'אייטם אקורדיון'
--and items.Guid = 'fb63585f56125210VgnVCM100000290c10acRCRD'
--and items.lastModifier = 'iditnk'
--and datediff(ss,job.createDate,job.endDate)>50
--where items.lastModifier like N'%maay%'
--and
--items.displayName like N'%?????%'
order by job.createDate DESC
--order by job.endDate DESC
OPTION (MAXDOP 1)
--select URL,min(FirstClickTimestamp )
--from [SQLNODE1\SQLINSTANCE1].[KNM_PRD].dbo.UrlClickCounters
--where Channel='3d385dd2dd5d4110' and PortletId='731294849'
--group by URL
--order by min(FirstClickTimestamp) desc