Vantage 8.03 report to list security group membership

Yahoo Archive
1

Thanks to all…That’s exactly what I needed…Worked like a champ!

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Tony Hughes
Sent: Tuesday, December 10, 2013 9:47 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Vantage 8.03 report to list security group membership

 

 

 

 

Mike, this here is very basic if you just want to see security groups and who is in them:

select userfile.name, secgroup.secgroupdesc, secgroup.secgroupcode,
userfile.dcduserid
from UserFile cross join SecGroup
where
( (userfile.grouplist like '%' + secgroup.secgroupcode + '~%') or
(userfile.grouplist like '%' + secgroup.secgroupcode)
)
and userfile.userdisabled = 0

 

 

On Monday, December 9, 2013 9:47 AM, Mike Abell <mabell@...> wrote:

 

I need to create a report that will display users that belong to each security group.  Can anyone help with the DB tables that house this information?

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

 

 

2

I need to create a report that will display users that belong to each security group.  Can anyone help with the DB tables that house this information?

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

3
Hello,
The tables you should look at are:
secgroup
security

However, you will find that they are quite cumbersome to work with.

Sebastien.-

--- In vantage@yahoogroups.com, Mike Abell <mabell@...> wrote:
>
> I need to create a report that will display users that belong to each security group. Can anyone help with the DB tables that house this information?
>
> Mike Abell
> Information Technology Manager
> Flexial - BOA Group - Cookeville, Tennessee
> Office: 931.432.1853 ext 302
> Mobile: 615.418.3055
> email: MAbell@...
>
4

Thanks Sebastian, Yes, I’ve been using the “security” table but it doesn’t appear to have any information on actually “who” belongs to “groups”.  Unless I’m missing something. 

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Sebastien
Sent: Tuesday, December 10, 2013 8:17 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Vantage 8.03 report to list security group membership

 

 

Hello,
The tables you should look at are:
secgroup
security

However, you will find that they are quite cumbersome to work with.

Sebastien.-

--- In vantage@yahoogroups.com, Mike Abell <mabell@...> wrote:
>
> I need to create a report that will display users that belong to each security group. Can anyone help with the DB tables that house this information?
>
> Mike Abell
> Information Technology Manager
> Flexial - BOA Group - Cookeville, Tennessee
> Office: 931.432.1853 ext 302
> Mobile: 615.418.3055
> email: MAbell@...
>

5

That should be in the UserFile (group list - List of security groups the user belongs to.)

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mike Abell
Sent: Tuesday, December 10, 2013 8:51 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Vantage 8.03 report to list security group membership

 

 

Thanks Sebastian, Yes, I’ve been using the “security” table but it doesn’t appear to have any information on actually “who” belongs to “groups”.  Unless I’m missing something. 

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Sebastien
Sent: Tuesday, December 10, 2013 8:17 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Vantage 8.03 report to list security group membership

 

 

Hello,
The tables you should look at are:
secgroup
security

However, you will find that they are quite cumbersome to work with.

Sebastien.-

--- In vantage@yahoogroups.com, Mike Abell <mabell@...> wrote:
>
> I need to create a report that will display users that belong to each security group. Can anyone help with the DB tables that house this information?
>
> Mike Abell
> Information Technology Manager
> Flexial - BOA Group - Cookeville, Tennessee
> Office: 931.432.1853 ext 302
> Mobile: 615.418.3055
> email: MAbell@...
>

6

And I believe it’s one of those fun ~ separated fields…

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Karen Schoenung
Sent: Tuesday, December 10, 2013 9:55 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Vantage 8.03 report to list security group membership

 




That should be in the UserFile (group list - List of security groups the user belongs to.)

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mike Abell
Sent: Tuesday, December 10, 2013 8:51 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Vantage 8.03 report to list security group membership

 

 

Thanks Sebastian, Yes, I’ve been using the “security” table but it doesn’t appear to have any information on actually “who” belongs to “groups”.  Unless I’m missing something. 

 

Mike Abell

Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...

 

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Sebastien
Sent: Tuesday, December 10, 2013 8:17 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Vantage 8.03 report to list security group membership

 

 

Hello,
The tables you should look at are:
secgroup
security

However, you will find that they are quite cumbersome to work with.

Sebastien.-

--- In vantage@yahoogroups.com, Mike Abell <mabell@...> wrote:
>
> I need to create a report that will display users that belong to each security group. Can anyone help with the DB tables that house this information?
>
> Mike Abell
> Information Technology Manager
> Flexial - BOA Group - Cookeville, Tennessee
> Office: 931.432.1853 ext 302
> Mobile: 615.418.3055
> email: MAbell@...
>

 




7


Mike, this here is very basic if you just want to see security groups and who is in them:

select userfile.name, secgroup.secgroupdesc, secgroup.secgroupcode,
userfile.dcduserid
from UserFile cross join SecGroup
where
( (userfile.grouplist like '%' + secgroup.secgroupcode + '~%') or
(userfile.grouplist like '%' + secgroup.secgroupcode)
)
and userfile.userdisabled = 0



On Monday, December 9, 2013 9:47 AM, Mike Abell <mabell@...> wrote:
  
<div id="ygrps-yiv-183931991yiv3332117757ygrp-text">
  
  
  <div>
I need to create a report that will display users that belong to each security group.  Can anyone help with the DB tables that house this information?
 
Mike Abell
Information Technology Manager
Flexial - BOA Group - Cookeville, Tennessee
Office:   931.432.1853 ext 302
Mobile:  615.418.3055 
email:  MAbell@...
 
 
</div>
 


<div style="color:#fff;height:0;"></div></div>
#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 -- #ygrps-yiv-183931991yiv3332117757ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp hr {
border:1px solid #d8d8d8;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp #ygrps-yiv-183931991yiv3332117757hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp #ygrps-yiv-183931991yiv3332117757ads {
margin-bottom:10px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp .ygrps-yiv-183931991yiv3332117757ad {
padding:0 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp .ygrps-yiv-183931991yiv3332117757ad p {
margin:0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mkp .ygrps-yiv-183931991yiv3332117757ad a {
color:#0000ff;text-decoration:none;}
#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ygrp-lc {
font-family:Arial;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ygrp-lc #ygrps-yiv-183931991yiv3332117757hd {
margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ygrp-lc .ygrps-yiv-183931991yiv3332117757ad {
margin-bottom:10px;padding:0 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity span {
font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity span:first-child {
text-transform:uppercase;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity span a {
color:#5085b6;text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity span span {
color:#ff7900;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757activity span .ygrps-yiv-183931991yiv3332117757underline {
text-decoration:underline;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757attach div a {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757attach img {
border:none;padding-right:5px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757attach label {
display:block;margin-bottom:5px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757attach label a {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 blockquote {
margin:0 0 0 4px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757bold {
font-family:Arial;font-size:13px;font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757bold a {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 dd.ygrps-yiv-183931991yiv3332117757last p a {
font-family:Verdana;font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 dd.ygrps-yiv-183931991yiv3332117757last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 dd.ygrps-yiv-183931991yiv3332117757last p span.ygrps-yiv-183931991yiv3332117757yshortcuts {
margin-right:0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757attach-table div div a {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757attach-table {
width:400px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757file-title a, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757file-title a:active, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757file-title a:hover, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757file-title a:visited {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757photo-title a, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757photo-title a:active, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757photo-title a:hover, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div.ygrps-yiv-183931991yiv3332117757photo-title a:visited {
text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 div#ygrps-yiv-183931991yiv3332117757ygrp-mlmsg #ygrps-yiv-183931991yiv3332117757ygrp-msg p a span.ygrps-yiv-183931991yiv3332117757yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757green {
color:#628c2a;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757MsoNormal {
margin:0 0 0 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 o {
font-size:0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757photos div {
float:left;width:72px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757photos div div {
border:1px solid #666666;height:62px;overflow:hidden;width:62px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757reco-category {
font-size:77%;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757reco-desc {
font-size:77%;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 .ygrps-yiv-183931991yiv3332117757replbq {
margin:4px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg table {
font-size:inherit;font:100%;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg select, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 input, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg pre, #ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 code {
font:115% monospace;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg * {
line-height:1.22em;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-mlmsg #ygrps-yiv-183931991yiv3332117757logo {
padding-bottom:10px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-msg p a {
font-family:Verdana;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-msg p#ygrps-yiv-183931991yiv3332117757attach-count span {
color:#1E66AE;font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-reco #ygrps-yiv-183931991yiv3332117757reco-head {
color:#ff7900;font-weight:700;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-reco {
margin-bottom:20px;padding:0px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ov li a {
font-size:130%;text-decoration:none;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ov li {
font-size:77%;list-style-type:square;padding:6px 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-sponsor #ygrps-yiv-183931991yiv3332117757ov ul {
margin:0;padding:0 0 0 8px;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-text {
font-family:Georgia;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-text p {
margin:0 0 1em 0;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-text tt {
font-size:120%;}

#ygrps-yiv-183931991 #ygrps-yiv-183931991yiv3332117757 #ygrps-yiv-183931991yiv3332117757ygrp-vital ul li:last-child {
border-right:none !important;}
#ygrps-yiv-183931991