This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
I'm trying to see who has what and if they really need it due to license's issue.
My Info:
Maximo 6.2.1
Weblogic Application
SQL Server 2005
thx,
mrggutz
Here is the query that I use to find all active users who are consuming a
license and what type of license it is.
SELECT maxgroup.groupname, groupuser.userid, person.displayname,
maxuser.status, person.statusdate, person.department,
person.locationsite
FROM groupuser groupuser,
maxgroup maxgroup,
maxuser maxuser,
person person
WHERE groupuser.groupname = maxgroup.groupname
AND groupuser.userid = person.personid
AND maxuser.userid = groupuser.userid
AND (maxuser.status='ACTIVE')
We are Maximo 6.2.6
SQL Server 2005
Websphere
Thanks
Scott A. Buettner
Honda of America Mfg., Inc.
Business Analyst - NA E IT Business Systems Services
NA Manufacturing Systems - Equipment Reliability Group
Phone: 937-642-5000 ext. 57001
E-mail: scott_buettner@ham.honda.com
Internal Pager: 6739
"George" <ggutierrez@pasadenarefining.com>
Sent by: MAXIMO@yahoogroups.com
11/02/2011 10:01 AM
Please respond to
MAXIMO@yahoogroups.com
To
MAXIMO@yahoogroups.com
cc
Subject
[MAXIMO List] Maximo users and access
Does anyone have an SQL Statement for returning what users have access to
in Maximo? I thought I had a report in the Security Groups that would do
this but I don't.
I'm trying to see who has what and if they really need it due to license's
issue.
My Info:
Maximo 6.2.1
Weblogic Application
SQL Server 2005
thx,
mrggutz
Confidentiality Notice:
This transmission (including any attachments) may contain confidential information belonging to the sender and is intended only for the use of the party or entity to which it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, retention or the taking of action in reliance on the contents of this transmission is strictly prohibited. If you have received this transmission in error, please immediately notify the sender and erase all information and attachments.
Thank you very much that help me out alot.
Thank You,
George Gutierrez Jr.
Maximo Administration
Pasadena Refining Systems
713.920.3908 Direct
832.473.5330 Cell
U1O2 - Chave
scott_buettner@ham.h
onda.com
Sent by: To
MAXIMO@yahoogroups.c MAXIMO@yahoogroups.com
om cc
Subject
11/02/2011 09:20 AM Re: [MAXIMO List] Maximo users
and access
Please respond to
MAXIMO@yahoogroups.c
om
Here is the query that I use to find all active users who are consuming a
license and what type of license it is.
SELECT maxgroup.groupname, groupuser.userid, person.displayname,
maxuser.status, person.statusdate, person.department,
person.locationsite
FROM groupuser groupuser,
maxgroup maxgroup,
maxuser maxuser,
person person
WHERE groupuser.groupname = maxgroup.groupname
AND groupuser.userid = person.personid
AND maxuser.userid = groupuser.userid
AND (maxuser.status='ACTIVE')
We are Maximo 6.2.6
SQL Server 2005
Websphere
Thanks
Scott A. Buettner
Honda of America Mfg., Inc.
Business Analyst - NA E IT Business Systems Services
NA Manufacturing Systems - Equipment Reliability Group
Phone: 937-642-5000 ext. 57001
E-mail: scott_buettner@ham.honda.com
Internal Pager: 6739
"George" <ggutierrez@pasadenarefining.com>
Sent by: MAXIMO@yahoogroups.com
11/02/2011 10:01 AM
Please respond to
MAXIMO@yahoogroups.com
To
MAXIMO@yahoogroups.com
cc
Subject
[MAXIMO List] Maximo users and access
Does anyone have an SQL Statement for returning what users have access to
in Maximo? I thought I had a report in the Security Groups that would do
this but I don't.
I'm trying to see who has what and if they really need it due to license's
issue.
My Info:
Maximo 6.2.1
Weblogic Application
SQL Server 2005
thx,
mrggutz
Confidentiality Notice:
This transmission (including any attachments) may contain confidential
information belonging to the sender and is intended only for the use of the
party or entity to which it is addressed. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, retention or the taking of action in reliance on the contents
of this transmission is strictly prohibited. If you have received this
transmission in error, please immediately notify the sender and erase all
information and attachments.
Here is something that may help, it is my first stab at it
SELECTGuser.GROUPNAME,Guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description FROMMAXIMO.maxapps,MAXIMO.sigoption,(SELECTDISTINCTGROUPUSER.GROUPNAME,groupuser.userid FROMGROUPUSER,MAXGROUPWHEREGROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME )Guser,(selectdisplayname,userid maxuserid fromperson,maxuserwhereperson.personid =maxuser.personid)maxpersonWHEREExists(Select1fromMAXIMO.ApplicationAuthwhereApplicationAuth.App =maxapps.App ANDApplicationAuth.OptionName =SigOption.OptionName AndApplicationAuth.GroupName =guser.groupnameandGuser.userid =maxperson.maxuserid)Andmaxapps.app =sigoption.app ANDsigoption.visible=1OrderByGuser.userid,MaxApps.Description,SigOption.Description
It will return a row for every app the user has access to as well as their options for the application
I am sure it can be improved.
Wes Williams
http://www.weswilliams.net
http://www.williamsconnell.com
From: George <ggutierrez@pasadenarefining.com>
To: MAXIMO@yahoogroups.com
Sent: Wednesday, November 2, 2011 9:01 AM
Subject: [MAXIMO List] Maximo users and access
Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
I'm trying to see who has what and if they really need it due to license's issue.
My Info:
Maximo 6.2.1
Weblogic Application
SQL Server 2005
thx,
mrggutz
Great query Wes! I've recopied it below to hopefully clear up the formatting issues from your original post.
SELECT guser.GROUPNAME, guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description
FROM maxapps,sigoption,(SELECT DISTINCT GROUPUSER.GROUPNAME,groupuser.userid FROM GROUPUSER,MAXGROUP WHERE GROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME)Guser,(select displayname,userid maxuserid
from person,maxuser where person.personid=maxuser.personid)maxperson WHERE Exists(Select 1 from ApplicationAuth where ApplicationAuth.App =maxapps.App AND
ApplicationAuth.OptionName=SigOption.OptionName And ApplicationAuth.GroupName=guser.groupname and Guser.userid =maxperson.maxuserid)And maxapps.app=sigoption.app
AND sigoption.visible=1 Order By Guser.userid,MaxApps.Description,SigOption.Description
--- In MAXIMO@yahoogroups.com, wwilliams@... wrote:
>
> Here is something that may help, it is my first stab at it
> Â SELECTGuser.GROUPNAME,Guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description FROMMAXIMO.maxapps,MAXIMO.sigoption,(SELECTDISTINCTGROUPUSER.GROUPNAME,groupuser.userid FROMGROUPUSER,MAXGROUPWHEREGROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME )Guser,(selectdisplayname,userid maxuserid fromperson,maxuserwhereperson.personid =maxuser.personid)maxpersonWHEREExists(Select1fromMAXIMO.ApplicationAuthwhereApplicationAuth.App =maxapps.App ANDApplicationAuth.OptionName =SigOption.OptionName AndApplicationAuth.GroupName =guser.groupnameandGuser.userid =maxperson.maxuserid)Andmaxapps.app =sigoption.app ANDsigoption.visible=1OrderByGuser.userid,MaxApps.Description,SigOption.Description
>
>
> It will return a row for every app the user has access to as well as their options for the application
>
> I am sure it can be improved.
>
> Wes Williams
>
>
>
>
> http://www.weswilliams.net
> http://www.williamsconnell.com
>
> From: George <ggutierrez@...>
> To: MAXIMO@yahoogroups.com
> Sent: Wednesday, November 2, 2011 9:01 AM
> Subject: [MAXIMO List] Maximo users and access
>
>
> Â
> Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
> I'm trying to see who has what and if they really need it due to license's issue.
>
> My Info:
> Maximo 6.2.1
> Weblogic Application
> SQL Server 2005
>
> thx,
> mrggutz
>
>
>
>
>
>
FYI - the 2nd line appears to have wrapped, so delete the '\' so the parameter reads
sigoption.optionname
and not
sigoption.opt\ ionname
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
> Great query Wes! I've recopied it below to hopefully clear up the formatting issues from your original post.
>
> SELECT guser.GROUPNAME, guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description
> FROM maxapps,sigoption,(SELECT DISTINCT GROUPUSER.GROUPNAME,groupuser.userid FROM GROUPUSER,MAXGROUP WHERE GROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME)Guser,(select displayname,userid maxuserid
> from person,maxuser where person.personid=maxuser.personid)maxperson WHERE Exists(Select 1 from ApplicationAuth where ApplicationAuth.App =maxapps.App AND
> ApplicationAuth.OptionName=SigOption.OptionName And ApplicationAuth.GroupName=guser.groupname and Guser.userid =maxperson.maxuserid)And maxapps.app=sigoption.app
> AND sigoption.visible=1 Order By Guser.userid,MaxApps.Description,SigOption.Description
>
>
> --- In MAXIMO@yahoogroups.com, wwilliams@ wrote:
> >
> > Here is something that may help, it is my first stab at it
> > Â SELECTGuser.GROUPNAME,Guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description FROMMAXIMO.maxapps,MAXIMO.sigoption,(SELECTDISTINCTGROUPUSER.GROUPNAME,groupuser.userid FROMGROUPUSER,MAXGROUPWHEREGROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME )Guser,(selectdisplayname,userid maxuserid fromperson,maxuserwhereperson.personid =maxuser.personid)maxpersonWHEREExists(Select1fromMAXIMO.ApplicationAuthwhereApplicationAuth.App =maxapps.App ANDApplicationAuth.OptionName =SigOption.OptionName AndApplicationAuth.GroupName =guser.groupnameandGuser.userid =maxperson.maxuserid)Andmaxapps.app =sigoption.app ANDsigoption.visible=1OrderByGuser.userid,MaxApps.Description,SigOption.Description
> >
> >
> > It will return a row for every app the user has access to as well as their options for the application
> >
> > I am sure it can be improved.
> >
> > Wes Williams
> >
> >
> >
> >
> > http://www.weswilliams.net
> > http://www.williamsconnell.com
> >
> > From: George <ggutierrez@>
> > To: MAXIMO@yahoogroups.com
> > Sent: Wednesday, November 2, 2011 9:01 AM
> > Subject: [MAXIMO List] Maximo users and access
> >
> >
> > Â
> > Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
> > I'm trying to see who has what and if they really need it due to license's issue.
> >
> > My Info:
> > Maximo 6.2.1
> > Weblogic Application
> > SQL Server 2005
> >
> > thx,
> > mrggutz
> >
> >
> >
> >
> >
> >
>
That works really well. I had to remove the ">" within it but after that it worked great. thanks
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
>
> FYI - the 2nd line appears to have wrapped, so delete the '\' so the parameter reads
>
> sigoption.optionname
>
> and not
>
> sigoption.opt\ ionname
>
>
>
> --- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@> wrote:
> >
> > Great query Wes! I've recopied it below to hopefully clear up the formatting issues from your original post.
> >
> > SELECT guser.GROUPNAME, guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description
> > FROM maxapps,sigoption,(SELECT DISTINCT GROUPUSER.GROUPNAME,groupuser.userid FROM GROUPUSER,MAXGROUP WHERE GROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME)Guser,(select displayname,userid maxuserid
> > from person,maxuser where person.personid=maxuser.personid)maxperson WHERE Exists(Select 1 from ApplicationAuth where ApplicationAuth.App =maxapps.App AND
> > ApplicationAuth.OptionName=SigOption.OptionName And ApplicationAuth.GroupName=guser.groupname and Guser.userid =maxperson.maxuserid)And maxapps.app=sigoption.app
> > AND sigoption.visible=1 Order By Guser.userid,MaxApps.Description,SigOption.Description
> >
> >
> > --- In MAXIMO@yahoogroups.com, wwilliams@ wrote:
> > >
> > > Here is something that may help, it is my first stab at it
> > > Â SELECTGuser.GROUPNAME,Guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description FROMMAXIMO.maxapps,MAXIMO.sigoption,(SELECTDISTINCTGROUPUSER.GROUPNAME,groupuser.userid FROMGROUPUSER,MAXGROUPWHEREGROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME )Guser,(selectdisplayname,userid maxuserid fromperson,maxuserwhereperson.personid =maxuser.personid)maxpersonWHEREExists(Select1fromMAXIMO.ApplicationAuthwhereApplicationAuth.App =maxapps.App ANDApplicationAuth.OptionName =SigOption.OptionName AndApplicationAuth.GroupName =guser.groupnameandGuser.userid =maxperson.maxuserid)Andmaxapps.app =sigoption.app ANDsigoption.visible=1OrderByGuser.userid,MaxApps.Description,SigOption.Description
> > >
> > >
> > > It will return a row for every app the user has access to as well as their options for the application
> > >
> > > I am sure it can be improved.
> > >
> > > Wes Williams
> > >
> > >
> > >
> > >
> > > http://www.weswilliams.net
> > > http://www.williamsconnell.com
> > >
> > > From: George <ggutierrez@>
> > > To: MAXIMO@yahoogroups.com
> > > Sent: Wednesday, November 2, 2011 9:01 AM
> > > Subject: [MAXIMO List] Maximo users and access
> > >
> > >
> > > Â
> > > Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
> > > I'm trying to see who has what and if they really need it due to license's issue.
> > >
> > > My Info:
> > > Maximo 6.2.1
> > > Weblogic Application
> > > SQL Server 2005
> > >
> > > thx,
> > > mrggutz
> > >
> > >
> > >
> > >
> > >
> > >
> >
>
Hi,
This one will run faster in SQL Server and also limits it to users that are in ACTIVE status.
It also includes the po and invoice limits and any conditions placed on the group.
select g.userid, u.defsite, p.displayname, g.groupname, m.description, a.app, a.optionname,
t.polimit, t.invoicelimit, c.conditionnum, c.description, c.expression
from groupuser g
join maxuser u on u.userid = g.userid
left join person p on p.personid = g.userid
left join maxgroup m on m.groupname = g.groupname
left join applicationauth a on a.groupname = g.groupname
left join limittolerance t on t.groupname = g.groupname
left join securityrestrict s on s.groupname = a.groupname and s.app = a.app
left join condition c on c.conditionnum = s.conditionnum
where u.status = 'ACTIVE'
order by g.userid, g.groupname, a.optionname
Dave Bone
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
> Great query Wes! I've recopied it below to hopefully clear up the formatting issues from your original post.
>
> SELECT guser.GROUPNAME, guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description
> FROM maxapps,sigoption,(SELECT DISTINCT GROUPUSER.GROUPNAME,groupuser.userid FROM GROUPUSER,MAXGROUP WHERE GROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME)Guser,(select displayname,userid maxuserid
> from person,maxuser where person.personid=maxuser.personid)maxperson WHERE Exists(Select 1 from ApplicationAuth where ApplicationAuth.App =maxapps.App AND
> ApplicationAuth.OptionName=SigOption.OptionName And ApplicationAuth.GroupName=guser.groupname and Guser.userid =maxperson.maxuserid)And maxapps.app=sigoption.app
> AND sigoption.visible=1 Order By Guser.userid,MaxApps.Description,SigOption.Description
>
>
> --- In MAXIMO@yahoogroups.com, wwilliams@ wrote:
> >
> > Here is something that may help, it is my first stab at it
> > Â SELECTGuser.GROUPNAME,Guser.userid,maxperson.displayname,maxapps.description,maxapps.app,sigoption.optionname,sigoption.description FROMMAXIMO.maxapps,MAXIMO.sigoption,(SELECTDISTINCTGROUPUSER.GROUPNAME,groupuser.userid FROMGROUPUSER,MAXGROUPWHEREGROUPUSER.GROUPNAME =MAXGROUP.GROUPNAME )Guser,(selectdisplayname,userid maxuserid fromperson,maxuserwhereperson.personid =maxuser.personid)maxpersonWHEREExists(Select1fromMAXIMO.ApplicationAuthwhereApplicationAuth.App =maxapps.App ANDApplicationAuth.OptionName =SigOption.OptionName AndApplicationAuth.GroupName =guser.groupnameandGuser.userid =maxperson.maxuserid)Andmaxapps.app =sigoption.app ANDsigoption.visible=1OrderByGuser.userid,MaxApps.Description,SigOption.Description
> >
> >
> > It will return a row for every app the user has access to as well as their options for the application
> >
> > I am sure it can be improved.
> >
> > Wes Williams
> >
> >
> >
> >
> > http://www.weswilliams.net
> > http://www.williamsconnell.com
> >
> > From: George <ggutierrez@>
> > To: MAXIMO@yahoogroups.com
> > Sent: Wednesday, November 2, 2011 9:01 AM
> > Subject: [MAXIMO List] Maximo users and access
> >
> >
> > Â
> > Does anyone have an SQL Statement for returning what users have access to in Maximo? I thought I had a report in the Security Groups that would do this but I don't.
> > I'm trying to see who has what and if they really need it due to license's issue.
> >
> > My Info:
> > Maximo 6.2.1
> > Weblogic Application
> > SQL Server 2005
> >
> > thx,
> > mrggutz
> >
> >
> >
> >
> >
> >
>