See is this one helps you at all.....
SELECT
System_Disc.*,
UserMachineRelation.*,
MDMDeviceProperty.*,
usr.*
FROM System_Disc
INNER JOIN UserMachineRelation on ItemKey = MachineResourceID
INNER JOIN MDMDeviceProperty on CAST(DeviceID as nvarchar(255)) =
SMS_Unique_Identifier0
INNER JOIN v_R_user usr on (usr.user_name0 =
substring(UniqueUserName,charindex('\',UniqueUserName)+1,len(UniqueUserName)))
AND PRopertyName = 'MdmStatus'
From: [email protected] [mailto:[email protected]] On
Behalf Of Erik Wold
Sent: Friday, August 26, 2016 3:42 PM
To: [email protected]
Subject: Re: [mssms] RE: Reporting on mobile devices in SCCM
Thanks. Yes, they are intune managed.
Mvh
Erik
_____________________________
From: Marcum, John <[email protected]<mailto:[email protected]>>
Sent: fredag, august 26, 2016 4:42 pm
Subject: RE: [mssms] RE: Reporting on mobile devices in SCCM
To: <[email protected]<mailto:[email protected]>>
Are they managed by Intune? If not how did they get into SCCM to begin with if
you don't have the exchange connector nor intune? If they are managed by Intune
I have some queries I can send you.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Thursday, August 25, 2016 6:37 AM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] RE: Reporting on mobile devices in SCCM
These mobile devices are on exchange online so these tables are empty, don't
have exchange connector in sccm.
Mvh
Erik
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Marcum, John
Sent: 24. august 2016 14:36
To: '[email protected]<mailto:[email protected]>'
<[email protected]<mailto:[email protected]>>
Subject: RE: [mssms] RE: Reporting on mobile devices in SCCM
Here's something I was using a while back and it may get you going in the right
direction....
SELECT
CASE WHEN IsCompliant = '1' THEN 'Device is enrolled'
ELSE 'Device is not enrolled'
END as 'Enrollment Status',
deviceid as 'Device ID',
FriendlyName 'Device Name',
--usr.streetaddress0 'Address',
usr.physicalDeliveryOfficeNam0 'Office',
usr.title0 'Title',
UserName 'User',
DeviceOS 'Operating System',
DeviceType 'Device Type',
DeviceModel 'Device Model',
ExchangeServer 'Exchange Server Connected',
max ((left (FirstSyncTimeUTC, 11))) as 'First Sync Time',
max ((left (LastSuccessSyncTimeUTC, 11))) as 'Last Sync Time'
FROM EAS_Property eas
INNER JOIN v_R_user usr on (usr.user_name0 =
substring(eas.UniqueUserName,charindex('\',eas.UniqueUserName)+1,len(eas.UniqueUserName)))
where DATEDIFF(d,LastSuccessSyncTimeUTC,GetDate()) < 7 and IsCompliant = '0'
and DeviceType != 'touchdown'
group by
IsCompliant,
deviceid,
FriendlyName,
UserName,
DeviceOS,
DeviceType,
DeviceModel,
ExchangeServer,
FirstSyncTimeUTC,
LastSuccessSyncTimeUTC,
UniqueUserName,
usr.streetaddress0,
usr.title0,
usr.physicalDeliveryOfficeNam0
order by User
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Wednesday, August 24, 2016 12:02 AM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] RE: Reporting on mobile devices in SCCM
Thanks.
Just for reporting mobile devices I use info from this view (which is populated
with info) : "v_GS_DEVICE_COMPUTERSYSTEM", but I also saw this table
"v_GS_DEVICE_EMAIL" that also contains[OwnerEmailAddress0] ,[SyncDomain0]
,[SyncServer0] and ,[SyncUser0] which I thought would be nice to collect and
maybe have in the report. That's where I thought these HW inventory classes
would play a part.
Mvh
Erik
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Marcum, John
Sent: 23. august 2016 16:44
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] RE: Reporting on mobile devices in SCCM
So again, tell me what you want to report on and I may be able to help you.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Tuesday, August 23, 2016 9:19 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: [mssms] RE: Reporting on mobile devices in SCCM
That can absolute be right, it's for reporting. Other info like IMEI, model,
serialnumber etc is collected (don't remember the table name now).
Mvh
Erik
On Tue, Aug 23, 2016 at 4:14 PM +0200, "Marcum, John"
<[email protected]<mailto:[email protected]>> wrote:
Let's go about this another way.... What are you trying to accomplish? I don't
think those classes do what you think they do. There's no "client" on your
devices so a client agent setting is useless.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Tuesday, August 23, 2016 8:31 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: [mssms] RE: Reporting on mobile devices in SCCM
The devices are in SCCM and are inventoried, I just can't get these HW classes
to be inventoried.
Mvh
Erik
On Tue, Aug 23, 2016 at 3:18 PM +0200, "David McSpadden"
<[email protected]<mailto:[email protected]>> wrote:
You have to get them to SCCM before you can inventory them.
Exchange connector will get you basic information.
(Have not done this myself but....intune connection will get you more
information)
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Tuesday, August 23, 2016 9:00 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Reporting on mobile devices in SCCM
Hm, I made a new custom client setting with these HW inventory classes enabled
and deployed it to the "All Mobile Device" collection and thought that should
do it, but then of course I could have misunderstood this :)
Erik
Mvh
Erik
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Marcum, John
Sent: 23. august 2016 14:40
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Reporting on mobile devices in SCCM
Are you pulling them from the exchange connector?
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Erik Wold
Sent: Tuesday, August 23, 2016 6:54 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] Reporting on mobile devices in SCCM
Hi,
Anyone know how to get data in this view: "v_GS_DEVICE_EMAIL" for mobile
devices? I've extended hardware inventory class "Mobile Device Email" to update
this but it doesn't seem to work. I also have an issue with Android devices not
reporting their phone number like IOS does.
Mvh
Erik
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
This e-mail and any files transmitted with it are property of Indiana Members
Credit Union, are confidential, and are intended solely for the use of the
individual or entity to whom this e-mail is addressed. If you are not one of
the named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender and delete this
message immediately from your computer. Any other use, retention,
dissemination, forwarding, printing, or copying of this email is strictly
prohibited.
Please consider the environment before printing this email.
