I am not an expert at SQL, so there is no doubt a more efficient way to do this, but here is what I came up with today. I didn't know that the built-in Web Help Desk sync with Casper didn't include the mobile devices, so needed to do something as well. Please feel free to make changes to make it better.

Here are the steps for creating a view that will allow Web Help Desk to pull Mobile Asset data from JSS.

Below are the views that create the "sub" views that are needed to create the ew4MobileDeviceMaster view

Steps 1-3 are needed to join the iphones table and the locations table.
1. This pulls general information for all mobile devices, however pulls duplicates if multiple location_id exist.

create or replace view ew4iphones
as select
p.iphone_id,
p.serial_number,
p.wifi_mac_address,
p.bluetooth_mac_address,
p.udid,
l.username,
l.realname,
l.location_id,
l.room
from iphones p
left join location_history lh on p.iphone_id = lh.iphone_id
left join locations l on lh.location_id = l.location_id

1. This limits it to the most recent location_id. This prevents duplicate data from being pulled in the view in step 2.

create view ew4_t2 as
select serial_number, max(location_id) as mostrecent
from ew4iphones group by serial_number

1. This creates the final view for mobile devices with locations.

create view helpdeskmobiledevices as
select t1.*, t2.mostrecent
from ew4iphones as t1
join ew4_t2 as t2
on t2.serial_number = t1.serial_number and t2.mostrecent=t1.location_id
order by t1.serial_number
Steps 4-5 are needed to join the iphones table and iphone_details table.
4. This creates a view with the mobile devices and reports (needed to have something in common between the iphones table and the iphone_details table)

create or replace view ew4iphones_reports
as select
p.iphone_id,
p.serial_number,
r.report_id
from iphones p
left join reports r on p.iphone_id = r.iphone_id

1. This step is much like step 3. It prevents duplicate data from being pulled in the view in step 4.

create or replace view ew4iphones_max as
select iphone_id, max(report_id) as mostrecent
from ew4iphones_reports group by iphone_id

1. The following creates the final view called ew4MobileDeviceMaster that we will select within Web Help Desk Settings - Assets - Discovery Connections

create or replace view ew4MobileDeviceMaster as
select t1.iphone_id, t1.serial_number, t3.report_id, t3.display_name,t3.device_name, t3.model, t3.os_version,t3.os_build,t3.model_version_number, t3.ip_address, t3.battery_level, t4.wifi_mac_address, t4.bluetooth_mac_address, t4.udid, t4.username, t4.realname, t4.room, t4.location_id
from ew4iphones_reports as t1
join ew4iphones_max as t2
join iphone_details as t3
join helpdeskmobiledevices as t4
on t2.iphone_id = t1.iphone_id and t2.mostrecent=t1.report_id and t1.report_id=t3.report_id and t2.iphone_id = t4.iphone_id
order by t1.iphone_id

does anyone know if version 9 will natively support syncing of iOS devices?

I did exchange email with Peter Kruty (Solarwinds product manager). Evidently there is a tracking request (#184135), but there is no ETA for iOS support. Has anyone setup views with v9 and have anything to share? Any other competing products that have worked out v9 integration? Thanks.