cancel
Showing results for 
Search instead for 
Did you mean: 

DQL expression to select blank and non blank rows

Phil_Rosier
Level 4

Is there a DQL expression to select blank or non blank rows from a column, for example to select rows from the permission table where group_trustee.name is not blank,  I've tried the != expression to no avail. Thanks.

 

 

 

 

5 REPLIES 5

Rishi_Thaper
Level 4
Employee

Hi Phil,


!="" (Not equal to empty string) works for most of the cases in a DQL query. Here, Permission table provide permissions info for both user and group. Wherever group_trustee.name is empty, it means that that row is populated for user_trustee.name. There is another column name trustee_type that can help here. If you just want to get group permissions for a group, you can add condition for trustee_type = "group". Let me know if this helps.

Example:

FROM permission

GET permission.object_type, isinherited, path.absname, path.msu.type, group_trustee.name, readable_permission

IF trustee_type = "group"

and path.msu.name = "winnas_share"

 

Thanks,

Rishi

Phil_Rosier
Level 4

Second case

 

Hi Rishi,

I tried using ="" and !="" expressions both in DQL and in the SQLite explorer and whilst it worked as expected in SQLite it didn't return any rows when added to my DQL statement.

The scenario is that my customer wants to enforce their standards for granting permissions to shares, such that each share has 3 trustee groups for read only, modify and full control. Users are then assigned to a member group of the associated trustee group.

I've been asked to create a custom report to identify cases where the standard hasn't been followed, for example where users have been assigned directly to a trustee group, or where the trustee group is a member of the user group rather than the other way around. Something like:

From permission

Get path.absname, path.msu.name, group_trustee.name, group_trustee.memberusers.name, readable_permission

If group_trustee.memberusers.name !="" and trustee_type="group

Overall there are likely to be around 8-10 non compliant scenarios that I'll need to create a report for, most of which will need to look for blank or non blank fields.

Kind regards

Phil.

 

 

 

 

 

Rishi_Thaper
Level 4
Employee

Hi Phil,

There seems to be some issue with != or = working with multivalued columns in DQL. While that is being looked at, can you utilize following query and perform minor post-processing to report on (Or elimnate) rows with users. Note that isinherited=0 will limit the rows to unique permission set.

FROM permission
Get path.absname, path.msu.name, group_trustee.name, readable_permission, isinherited, group_trustee.memberusers.name
If group_trustee.memberusers.name !="" and trustee_type="group"
and path.msu.name = "Share1"
and isinherited = 0
Format group_trustee.memberusers.name as CSV

One point to note is that above query will provide information for all the users under a group (whether direct or indirect) which may not fulfill your use-case. However, today this should be achievable using some customization outside of DQL. For instance get all groups where direct members are listed using a DI API and then feed the list to DQL query for permissions and path information.

Thanks,

Rishi

Phil_Rosier
Level 4

Thanks for your reply Rishi, I'll give that a try next week when back onsite. I was wondering whether another work around might be to use the count operation, such as:

If count(group_trustee.memberusers.sid)>0

Or does this have the same limitations with multivalued columns?

Kind regards.

Phil

 

 

 

 

Rishi_Thaper
Level 4
Employee

Hi Phil,

Unfortunaltely this too won't work for multivalued columns today.

Thanks,

Rishi