Gather a report from WVS job monitor

rsnider-2 · ‎Sep 29, 2015

It would be very useful if a report could query/report/something that would spit out if a job was successful or not, when it happened and what type of object it was that was needed to publish for visualization.

Rex Snider

BAE Systems

TomU · ‎Sep 29, 2015

I have to believe this is possible somehow. I see most of the publishing information is stored in the "PublishingSummary" table in the database. I'm not sure how these records get linked back to the related parent object (EPMDocument / Document). Maybe Binesh Kumar‌ would know. Once you figure that out it should be pretty straight forward to put an SQL query together.

rsnider-2 · ‎Sep 30, 2015

I really like your response regarding an SQL query. I have not played in SQL in a very long time. I wonder what such a query script would look like. I too believe there must be a method to do this since there is that PublishingSummary. I can pull that any time. If all the states of a publish job can be pulled into that one summary then why can't it individualize states of a publish into a listing of those objects by name or number, failed or not with dates and times and which worker and queue. The only issue I see is that such a log running constantly would be awful to maintain as it would grow exponentially. That being said it would need to be turned on when wanted and removed so as not to eat up storage space on the data systems.

TomU · ‎Oct 07, 2015

I've dug into this deeper and it looks like you're going to need that product idea. From what I can tell everything you see on the WVS Job Monitor page is actually stored in BLOB data in the QueueEntry table of the database. The only way to read anything other than the job number will require you to extract and then manually parse each row's BLOB data.(not practical) When you delete a row from the WVS Job Monitor page, the corresponding row is also removed from the QueueEntry table, including the BLOB data. Nothing on the WVS page is dynamically built from other database tables. (This is why you there aren't links to anything and also why you can't customize the columns displayed.) There is a separate PublishingSummary table in the database that does keep a subset of this information forever, but unfortunately it does not contain enough information to identify either the actual job or the object that was published.

BineshKumar1 · ‎Oct 08, 2015

I too did some research, Tom. There is no way you can link publish summary with corresponding doc through a query

rsnider-2 · ‎Oct 16, 2015

Tom,

WOW you are great to do this deep study. I really appreciate it. The information you provided can be useful for developers on your end for sure. Can you imagine the code that would need to be created to address this? I was able to come up with the information necessary to build my justification for my project. I will not say it was easy. It was very time consuming. The information I gathered gave me a level of supporting data that I think those I present to can accept even though I am making a great many assumptions. Thank you for this follow up. I will keep it in my docs so we can review and discuss. Perhaps at the next convention take this up again there.

TomU · ‎Nov 18, 2015

PTC released an article today confirming that it is not possible to query the information contained on the WVS Job Monitor page.

https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS114587

ywyffels · ‎Mar 03, 2016

I developped the folowing report. This does not get things from the blobs, but from the publish history. For my customer, this was sufficient, since he wanted to know how long the jobs took, how much failed, etc. adding some charts to the result, boosts this up.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE qml SYSTEM "/wt/query/qml/qml.dtd">

<query>

heading="Context (containerName)" propertyName="containerName">

</object>

<column alias="wt.viewmarkup.PublishingSummary"

heading="wt.viewmarkup.PublishingSummary Job Success"

propertyName="jobSuccess" type="java.lang.Boolean">jobSuccess</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Job Submit Time"

propertyName="jobSubmitTime" type="java.sql.Timestamp">jobSubmitTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Job Start Time" propertyName="jobStartTime" type="java.sql.Timestamp">jobStartTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Job End Time" propertyName="jobEndTime" type="java.sql.Timestamp">jobEndTime</column>

<function heading="Time Difference(hours)"

name="TIME_DIFFERENCE_IN_HOUR" type="java.math.BigDecimal">

<column

alias="wt.viewmarkup.PublishingSummary"

heading="wt.viewmarkup.PublishingSummary Job End Time"

propertyName="jobEndTime" type="java.sql.Timestamp">jobEndTime</column>

<column

alias="wt.viewmarkup.PublishingSummary"

heading="wt.viewmarkup.PublishingSummary Job Start Time"

propertyName="jobStartTime" type="java.sql.Timestamp">jobStartTime</column>

</function>

</function>

heading="wt.viewmarkup.PublishingSummary" propertyName=""/>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Authoring Application"

propertyName="authoringApplication" type="java.lang.String">authoringApplication</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Job Request Queue"

propertyName="jobRequestQueue" type="java.lang.String">jobRequestQueue</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker Name" propertyName="workerName" type="java.lang.String">workerName</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker Start Exec Time"

propertyName="workerStartExecTime" type="java.sql.Timestamp">workerStartExecTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker Start Time"

propertyName="workerStartTime" type="java.sql.Timestamp">workerStartTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker Start Transfer Time"

propertyName="workerStartTransferTime" type="java.sql.Timestamp">workerStartTransferTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker End Exec Time"

propertyName="workerEndExecTime" type="java.sql.Timestamp">workerEndExecTime</column>

<column alias="wt.viewmarkup.PublishingSummary"

heading="Worker End Time"

propertyName="workerEndTime" type="java.sql.Timestamp">workerEndTime</column>

heading="Persist Info" propertyName="persistInfo.objectIdentifier">

</property>

</object>

</select>

<from>

<table alias="wt.viewmarkup.PublishingSummary">wt.viewmarkup.PublishingSummary</table>

</from>

</query>

</statement>

</qml>

Prohibit republishing for EPM Documents only upon folder change

STEVEG · ‎Mar 03, 2016

I've been trying to get it linked to show the EPM/CAD file or the Document file name but haven't been able to. Anyone know what it could be?

mschulze · ‎Apr 13, 2017

Isn't what you are looking for in the "Job Statistics" (#WVS Job Monitor #Job Summery #Actions #Job Statistics)?

On the "Job Statistics" page you can also export the statistics, which gives a similar report as written by ywyffels above.

rheinz · ‎May 11, 2017

Fully Agree with Mathias.

I also was asked for an analyze of the past executed Jobs, e.g. executing time, waiting time, ... and with the statictics Export the resulting csv file is easy to filter and getting the result you hopefully looking for.

My dmeands were satified with it and made whitin 5 minutes.

Regards

Robert

PTCModerator · ‎Jun 11, 2018

smishra-3 · ‎Aug 09, 2019

WVS Job Monitor page is actually stored in BLOB data in the QueueEntry table of the database.

I have developed the Utility using PublishQueueHelper.extractProcessingJob(QueueEntry) API.

I had extract the blob data from that API So it can give us all the details like below picture.

if you do not want to write the external BLOB extraction code that existing API is very useful.

All other input parameter like date queue number worker type can be easily implemented on query directly.

Name ,number and version stored in BLOB so if we want that report we have to use that API only.

d_graham · ‎Jul 19, 2021

BLOB data can be displayed using the Query Builder.

What everyone is talking about is doable with Query Builder.

All the info related to the Job is in the BLOB, that's true. However, BLOB data can be displayed using query builder.

See image below.

Not only is the Job Status and the Representable (in this case a CAD Drawing) displayed but to show how flexible this is I also display the entire log associated with the publishing job.

David

Marco_Tosin · ‎Jul 19, 2021

How to display BLOB data in Query Builder reports

d_graham · ‎Jul 19, 2021

@Marco_Tosin The article re: displaying BLOB data in Query Builder works for somethings but but I don't think it's the right choice for this use case.

That said, it does point you in the right direction which is "Java Method".

In the report I made all but the first column use "Java Method".

I wrote my own helper class with several methods to return the data I want to display in each column.

The methods dig the required data out of the BLOB and return it to the report.

I'm not using wt.pds.ConversionHelper at all. Though I have used it in the past I would go a different route for this particular use case.

David

AndreaC · ‎Sep 07, 2022

@d_graham is it possible to share query builder you created to extract the data from the WVS queue? I would to try on my environment to find out the EPM docs number

Thank you