Workflow Service Monitor

See my previous post about number of workflow instances sitting in the queue to be processed. You can via a single query determine how many instances are waiting and usually this number is very close to a zero – this is how WorkflowService operates. Unless it is broken and does not process the queue. I found it very beneficial to know when the workflow service is down so I can be there fixing the problem before my clients come to me reporting the problem.

So with some PowerShell you can set up a simple monitoring: if queue has more than X number of instances waiting -> send an email to whoever is responsible. Have this running on a schedule every 5 minutes and viola – you have an early warning system for health of your HR.Net.

Well, that was my initial idea with PowerShell. When I decided to try if the target server has all the PowerShell modules installed I discovered that it barely has anything installed on it. And installing all the required dependencies were not really an option. So I quickly opened Visual Studio and made a 100-line application in C# that does pretty much what I describe above with a bit more meat behind it – some error logging and email with the list of workflows waiting. And some other nice to have stuff.

I’m not going to publish the source code here as it has some bits that I can’t disclose – because NDA. But if you are interested in such monitoring application, please get in touch, I’ll remove all sensitive parts and will provide instructions how to install it.

Posted in Uncategorized | Leave a comment

Workflow service borked! Again!

Workflow service is extremely useful and extremely fragile. About once a month I get a call from a customer with the problem that boils down to Workflow Service not operating.

Today I just had to see what is sitting in the workflow queue to know the problem. But looking on the workflow queue that is pending transaction is tricky. Here is SQL for that:

select
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
    w.WorkflowName,
	wi.TriggerName,
	w.TABLENAME,
	wi.RECORDPK,
	wi.starteddatetime,
	wi.NEXTRUNDATE
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
    left join OC_ADMINCONSOLE_FOLDERS f on w.folderid = f.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0
order by wi.STARTEDDATETIME

And this will give you a count of workflows in the queue to be processed:

select count(*)
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0

Posted in Uncategorized | Tagged , , | Leave a comment

Restart HR.Net services

HR.Net is full of WTF moments. This whole blog is based on these moments: from ancient version of jQuery to obscure bugs in places where “things should just work”.

This time I run into Workflow service that sometimes gets stuck: the actual windows service gets hanged and stop responding and processing any new workflows. But the whole system heavily depends on workflows being processed in a timely manner, the system just crashes and I have a lot of unhappy customers calling me in panic. The fix is simple – restart the workflow service. Though the fix does not show signs of life – it works. HR.Net queues workflows in a table and the service picks up one workflow at a time and runs it. Usually by the time I get a phone call there are hundreds of unprocessed workflows already and the service needs some time to crunch through them. This bit of SQL will show you how many workflow instances are there

select count(*) from OC_WORKFLOWINSTANCES
where STARTEDDATETIME > '2017-07-10 00:00:00.000'

replace the date with your current date to show how many have been queued during your day.

Solution to this problem is to restart HR.Net Workflow Service regularly. Do this via commands:

net stop "HR.net Workflow Service"
net Start "HR.net Workflow Service"

Put this as a scheduled task and be done with this. I’ve scheduled this command to be executed every hour, but I suggest you start from every 24 hours. I already had HR.Net Reports service restarted every hour because it caused problems too. And I’ve included workflow service there as well to save myself some typing -)

So my whole bat file looks like this:

@@ECHO OFF

ECHO ***STOPPING HR.NET SERVICES***
net stop "HR.net Reports Service"
net stop "HR.net Workflow Service"

ECHO ***STARTING HR.NET SERVICES***
net Start "HR.net Reports Service"
net Start "HR.net Workflow Service"

EXIT
Posted in Uncategorized | Tagged , , | 4 Comments

Find workflows/mailers with particular subject

Sometimes I get support requests quoting an email received from HR.Net and asking to modify that email text or add more recipients. And then I need need to find that workflow containing the mailer that sent out that particular message. And given thousands of workflows, it can take days to find the exact workflow (unless you know exactly where to look for)

So I wrote this little SQL query that matches email subject to a workflow name/folder:


select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart
 from OC_WORKFLOWMAILER mailer
  inner join OC_WORKFLOWS flow on mailer.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where mailer.htmlsubject like '%my subject%'
Posted in Uncategorized | Tagged , , | Leave a comment

Custom Script Validation on Screens… Again!

HR.Net is a mysterious beast! Something may work in one place, but same copy-pasted might not work in the other place. Last couple hours I have spent fighting Screen Validation again. I have already written about this feature a few times and this time none of these methods worked for me, even though I have not done anything different. No idea what happened with HR.Net and why old versions work in old screens, but not in new screens. Because HR.Net is full of shitty bugs, sometimes javascript function Page_ClientValidate is not available on a screen – I noticed that it disappears as soon as you add a grid to a screen. And in all my previous validations I was relying on this function to be present and called by the framework (“Page_ClientValidate” function which is provided by underlying .Net framework).

So after a lot of detective work I have found that save button triggers event HRnet.Form.UI.Buttons.Save.action and I could override this function. But “Save And Close” button was triggering different event HRnet.Form.UI.Buttons.SaveAndClose.action, so both of these should be overriden if both buttons are available.

Here is the basic script that ensures that some fields are filled in on the screen:

// override click on Save button
(function() {
  var proxied = HRnet.Form.UI.Buttons.Save.action;
  HRnet.Form.UI.Buttons.Save.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();

// override click on Save And Close button
(function() {
  var proxied = HRnet.Form.UI.Buttons.SaveAndClose.action;
  HRnet.Form.UI.Buttons.SaveAndClose.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();



function MyValidate(){
    var field1 = $HRnet("Field1").getDisplayValue();
    var field2 = $HRnet("Field2").getDisplayValue();
    var field2 = $HRnet("Field3").getDisplayValue();

    var ErrMsg ="";
    var validate = true;

    if (isBlank(field1)){
        ErrMsg += "You need to fill in 'Field 1'\n";
        validate = false;
    }

    if (isBlank(field2)){
        ErrMsg += "You need to fill in 'Field 2'\n";
        validate = false;
    }
   
    if (isBlank(field3)){
        ErrMsg += "You need to fill in 'Field 3'\n";
        validate = false;
    }
            
    if (!validate){
        alert(ErrMsg);
        return validate;
    }
}


function isBlank(val){
    if(val==null){
        return true;
    }
    for(var i=0;i
							
Posted in Uncategorized | Tagged , , | 2 Comments

Java Screen Field Validation

It has been a while since I had to do JavaScript in screens. And even longer since I done a validation on the screen. Since then there were changes in HR.Net which I have missed. And my old way to validate screens no longer works. But the fix is simple enough – all you need to do is add a parameter to the old script:

var orig_ValidatorOnSubmit = window.ValidatorOnSubmit;

window.ValidatorOnSubmit = function(validationGroup){
  var ErrMsg ='';
  var validate = true;
 
  // your custom validation goes here
	if (!validate){
          alert(ErrMsg);
		return false
	}
	return orig_ValidatorOnSubmit(validationGroup);
}
Posted in Uncategorized | Tagged , | 1 Comment

Find all users in a role

You know, HR.Net users have a primary role that defines their level of access to the data. Also there are many additional roles that allow to do other stuff, like using of navigator. But additional roles don’t give any data-permissions. That’s the way HR.Net security is done.

And sometimes you need to see all the users with a specific additional role. Here is the script:

select 
    OC_USERS.USERID, 
    primaryRole.ROLENAME as PrimaryRole
from [dbo].[OC_ROLELANGUAGES] 
    inner join OC_USERROLES on OC_ROLELANGUAGES.ROLEID = OC_USERROLES.ROLEID
    inner join OC_USERS on OC_USERS.USERID=OC_USERROLES.USERID
    inner join [OC_ROLELANGUAGES] primaryRole on primaryRole.ROLEID = Oc_users.ROLEID
where OC_ROLELANGUAGES.rolename='My Role'
order by primaryRole.ROLENAME
Posted in Uncategorized | Tagged , | Leave a comment

Updating of tables ends up with a messy error message about incompatible type

After modifying one of tables in C.Net I ended up with an error message saying “Incompatible type blah-blah-blah” on table save (sorry, don’t have exact words or screenshot). Turned out that I have changed type of one of the fields (from computed Text to Pick-List). This drove mad the security permission model and HR.Net could not re-generate the security views it uses in the background.

To figure out what exact permission was causing issues I came up with this script:

select 
    vs.name,
    definition
from sys.objects o
    join sys.sql_modules m on m.object_id = o.object_id
    cross join sys.views vs 
where o.object_id = object_id(vs.name)
  and o.type = 'V'
  and vs.name like '%[_]tablename[_]%'

This script outputs a list of all views generated by the system and outputs actual SQL that is behind the view. That way I could easily identify what role and what permission was causing the issue and could modify.

I was lucky I remembered the table name I modified, otherwise I would have to look through a gazillion of SQL Views and figure out which one is causing issues to fragile internals of HR.Net

Posted in Uncategorized | Tagged , | Leave a comment

Convert Report to be Landscape

Today I had to make a new report in HR.Net Document Explorer. Unfortunately version of HR.Net I’m using is buggy.

Every time you try to view a report preview, it crashes:

2014-11-03 00_54_03

Vizual claims that this error is because I’m running 64-bit OS, but their software is only supposed to work on 32-bit. But if I create a sub-report, I can run a preview on that. I think lousy coding standards are at fault here and nothing to do with 32/64-bit compatibility. And Vizual support just tries to make themselves look less bad.

Anyway, after some poking about, I have found a database table where these report definitions are stored as XML. And we can manipulate XML as we wish! One of the properties report XML-definition is <Orientation>. It is set to 1 for Portrait page layout and 2 for Landscape (I did compare different reports). So now all we have to do is replace this value in the database:

update [dbo].[OC_REPORTS_REPORTDEFINITIONS]
set DEFINITIONDATA = cast(REPLACE(cast(DEFINITIONDATA as nvarchar(max)), '1', '2') as ntext)
from [dbo].[OC_REPORTS_REPORTDEFINITIONS] repdef
   inner join OC_DOCUMENTS docs on repdef.DOCUMENTID = docs.DOCUMENTID
where docs.DOCUMENTNAME = 'My Report Name'

Replace “My Report Name” with name of your report. Before running this query, please make a copy of your report – there is no guarantee that this will work, also there is a chance that your report might be messed up beyond recognition.

Also if you have sub-reports, they also will become Landscape. If this is your goal, then no problem. If not, you’ll have to go through all sub-reports and modify them back – you are in luck here. Document Console allows to modify the sub-report orientation without throwing errors.

Posted in Uncategorized | Tagged , , | 5 Comments

Find a Report By Name

It’s been a while since the last post. But the blog is not dead, I keep working with HR.Net and when I find good enough reason to post here, I do.

Here is the new gem I had to discover. In attempt to delete an unused column from an unused table, I was present with dialog saying “Can’t delete field, it is used in a report”. Good on Vizual that they are showing reports that depend on fields. But how they do it:

2014-10-20 00_08_50-Ts - ts.cornerstone.org.uk - Remote Desktop Connection

Yep, thanks. This field is used in report “Test Blah”. But where the hell is this report? In the system I support just now there are over a thousand of reports with about a hundred of folders.

So this script will help you show the location of a report in your system:

select 
    pppf.foldername as [Folder],
    ppf.foldername as [Folder],
    pf.foldername as [Folder],
    f.foldername as [Folder],
    d.*
from OC_DOCUMENTS d
	left join dbo.OC_DOCUMENTFOLDERS f on d.folderid = f.folderid
	left join dbo.OC_DOCUMENTFOLDERS pf on f.PARENTFOLDERID = pf.folderid
	left join dbo.OC_DOCUMENTFOLDERS ppf on pf.PARENTFOLDERID = ppf.folderid
	left join dbo.OC_DOCUMENTFOLDERS pppf on ppf.PARENTFOLDERID = pppf.folderid
where d.DOCUMENTNAME = 'Test Blah'
Posted in Uncategorized | Tagged , | Leave a comment