Getting missing details for OGs and Smart Groups from UEM DB

Getting missing details for OGs and Smart Groups from UEM DB:

In this blog we will go through how we could get the details of Organization Group s and smart groups using API. For example, when it got created, how many admin, users and devices are in that OG/smart Group. It’s a great way to audit if you have so many of OGs/smart groups and you like to get more info before deleting them.

We will also go through which data is missing from API call and how we could get all details from SQL DB table.

First, we will see what details we get using API. If you like to find out more about how to run API call, then have a look at my below blog:

Rest API in Workspace ONE UEM

Organization Groups:

Graphical user interface, text, application

Description automatically generated

https://{host}/API/system/groups/{id}/children

By running above API, you will be able to get the current OG and all child OGs detail. Below is the sample of result in my lab.

Graphical user interface, text

Description automatically generated

Smart Groups:

https://{host}/API/mdm/smartgroups/search

By running above API, you will be able to get the list of all smart groups detail. Below is the sample of result in my lab.

A picture containing timeline

Description automatically generated

From SQL using script:

Above is great way to get most of info for OGs/Smart Groups but there is one relative info missing about who (UEM Admin) created this specific OG or smart group. That is handy and useful information but not showing in API call.

If you are on-prem customer then you could easily find this info from SQL using below scripts.

Organization Groups:

Below is the sample result from my lab for OGs. Just make sure you have correct ParentLocationGroupID before running this query to get correct data. You could see UserName of the admin who created OG and when it got created.

select lg.Locationgroupid, lg.Name, cu.UserName, lg.Createdon from dbo.locationgroup lg (nolock)
Left join dbo.CoreUser cu (nolock) on lg.createdby =  cu.CoreUserID
Left join locationgroupflat lgf (nolock) on lgf.ChildLocationGroupID = lg.locationgroupid
where lgf.ParentLocationGroupID = 570

 

Graphical user interface, text, application

Description automatically generated

Smart Groups:

Below is the sample result from my lab for smart groups. Just make sure you have correct ParentLocationGroupID before running this query to get correct data. You could see UserName of the admin who created smart group and when it got created.

select sg.smartgroupid, sg.RootLocationGroupID, sg.Name, cu.UserName, sg.Createdon from smartGroup.SmartGroup sg (nolock)
Left join dbo.CoreUser cu (nolock) on sg.createdby =  cu.CoreUserID
Left join locationgroupflat lgf (nolock) on lgf.ChildLocationGroupID = sg.rootlocationgroupid
where lgf.ParentLocationGroupID = 570

Graphical user interface, text, application

Description automatically generated

Written by

+ posts

Joined VMware in July 2015 as consultant and working in different BU over 6 years. Having experience in IT industry over 10 years with Masters degree in IT.

2 thoughts on “Getting missing details for OGs and Smart Groups from UEM DB

  • Amine
    2022-04-12 at 15:42

    Hello,
    Is there a way to change the OG type from the DB?

    • Patrick Zoeller
      2022-07-21 at 09:57

      Even if this is possible , it will not be supported, please use the GUI to change the Type of a OG

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*
You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.

BCF Shop Theme By aThemeArt.
BACK TO TOP