Reporting on users who do not have a division in their organisation

[AlexOnTheHill]

I am looking into a problem where our user sync has been unable to map users organisation details fully.

I can see the reason is because a number of new divisions have been created internally which do not yet exist within Hornbill.

In order to identify each of these missing divisions it would be really useful if I could run a report that shows each user who is missing theirs.

Our structure is Company - Department - Division

Affected users are just showing Company - Department

I've been trying a few things but not having much success.

Any help is appreciated.

[Steve Giller]

You might be able to adapt Neil's solution to a similar question for this.

 

[AlexOnTheHill]

I've had a go at modifying this one but wasn't successful.

I don't think I'd have any difficulty reporting on users without any roles as roles are a field I can point to directly.

The problem I have is that I want to say "show me the users who don't have an organisation type 4"

Perhaps this is better submitted as a database direct query?

[yelyah.nodrog]

@AlexOnTheHill Would love to know the answer to this as well as we have a bunch of departments that haven't been input into HB so the users don't have them set. I cannot seem to get it to report on Type '2' (Department) where its NULL/Empty on the reports side or Database Direct

Did you get anywhere with this?

H

[AlexOnTheHill]

I didn't make any headway with this one sadly.

I had hoped there would be something in h_sys_accounts but there isn't.

The groups are under h_sys_account_groups and the group types are under h_sys_groups so you'd need to link each of these.

I'm terrible at SQL but I was able to create a quick search on database direct: 

SELECT * FROM h_sys_account_groups 
JOIN h_sys_groups on h_group_id = h_id 
WHERE h_user_id = '[enter ID here]'
AND h_type = '2'

That shows me if someone has got a department, now I think we just need to find the inverse of that

[yelyah.nodrog]

Yes, we are stuck on the inverse of that aswell, I think its becasue all of the Org stuff is held in that field so the only time we get a result on null is if they have no cost centre, directorate or Department etc

Thankyou though!

H 

[Met]

Hi @yelyah.nodrog @AlexOnTheHill

Try the following

SELECT 
  DISTINCT h_user_id 
FROM 
  h_sys_account_groups 
WHERE 
  h_user_id NOT IN (
    SELECT 
      h_user_id 
    FROM 
      h_sys_account_groups 
      JOIN h_sys_groups on h_group_id = h_id 
    WHERE 
      h_type = '<ID>'
  )

The second SELECT statement is yours and returns a list of users who do belong to a specific organisation/team etc.

The first SELECT statement then returns a list of users who do not appear in the output of the second statement.

[AlexOnTheHill]

That's it! Brilliant! Exactly what was needed!

What you've done there makes sense too. Thank you for the explanation.

[yelyah.nodrog]

Thankyou so much, I just need to join h_sys_accounts and I can search by only active users aswell this is amazing!