VMware Workspace ONE Community
BDBos
Enthusiast
Enthusiast

Sql query device / user details.

I would like from the WS database (we run on-prem). Want to run a query with all active devices supplemented with user data. E.g. username, first, last name, email.

I see a lot of data in dbo.Device; Mobilemanagement.EnrollmentUser; but not getting the data out properly. Can someone help me with the right sql query? Or are there other tables where I can extract the data better?

Reply
0 Kudos
1 Reply
OlegKiselev
Enthusiast
Enthusiast

Hi,

Maybe this will help you

 

select
dses.LastSeen
,enuser.DisplayName
,enuser.EmailAddress
,enuser.FirstName
,enuser.LastName
,enuser.UserName
from
dbo.device dev
,dbo.DeviceSession dses
,dbo.DeviceUserContext ducont
, mobileManagement.EnrollmentUser enuser
,dbo.DeviceUser duser
where
dev.CurrentSessionID = dses.DeviceSessionID
and dev.UnenrollmentDate is NULL
and ducont.DeviceID = dev.DeviceID
and dses.LastSeen > '2023-03-01'
and dev.DeviceID = ducont.DeviceID
and ducont.DeviceUserID = duser.DeviceUserID
and enuser.EnrollmentUserID = duser.EnrollmentUserID

Reply
0 Kudos