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?
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
Hi (:
Thanks for sharing this query, very helpful!
Do you maybe have any idea how can I have the device IP and MAC also ?
Hi Lior81,
ip address in hex
select
dev.DeviceID
,dses.LastSeen
,enuser.DisplayName
,enuser.EmailAddress
,enuser.FirstName
,enuser.LastName
,enuser.UserName
,di.MAC
,di.IPAddress
from
dbo.device dev
,dbo.DeviceSession dses
,dbo.DeviceUserContext ducont
, mobileManagement.EnrollmentUser enuser
,dbo.DeviceUser duser
,DeviceInterface di
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
and di.DeviceID = dev.DeviceID