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:
Organization Groups:
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.
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.
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
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
Joined VMware in July 2015 as a consultant and worked in different BU over 7 years. Having experience in the IT industry of over 10 years with a Master's degree in IT.
Amine
Hello,
Is there a way to change the OG type from the DB?
Patrick Zoeller
Even if this is possible , it will not be supported, please use the GUI to change the Type of a OG