Send Mobile Device information to Web Help Desk

[csimmonskenra]

I'm in the process of setting up Web Help Desk and connecting it to Casper. I've configured the Assets section to pull computer inventory information from Casper using Web Help Desk's built-in Casper connection. The only problem is that it only pulls in Computers, not Mobile Devices.

I can set up Web Help Desk to pull Mobile Device information from a custom view in the jamfsoftware MySQL database. The problem I'm running into is figuring out how to join the iphones and iphone_details tables so I can pull fields from both.

Here's the SQL view definition that I'm using. Any help will be very appreciated!!

create view `vw_casper_mobile_devices` as select
p.iphone_id,
p.serial_number,
l.username,
l.realname,
d.device_name SyncID, d.guid, d.display_name,
d.device_name,
d.iccid,
d.imei,
d.phone_number,
d.last_backup_time,
d.model,
d.os_version,
d.os_build,
d.network,
d.carrier,
d.disk_size,
d.disk_used,
d.disk_available,

/* The following eaX.value_on_client columns represent mobile extended attributes. These are examples only; the actual column name to use depends on your own extended attributes configuration. See the Casper 'extension_attributes' table for the list of extension attribute names and IDs. You don't need to include columns for each row in the table--only the extension attributes you want to include in the view. - Remove the '-- ' comment indicator before each column to be included. Be sure the column identifier for each line has a unique identifier (ea1, ea2, etc.). - Use the value of the extension_attributes.display_name field in place of 'ExtrAttrX'. - IMPORTANT! For each custom field, uncomment a corresponding "left join" expression below, replacing ### with the value of the corresponding extension_attributes.extension_attribute_id field. NOTE: When using Web Help Desk versions 10.2.0.35 and earlier, thea * column display name ('ExtAttrX') must NOT include spaces. */
-- ea1.value_on_client `ExtAttr1` /* To uncomment this line, delete the '-- ' prefix. */
-- ea2.value_on_client `ExtAttr2`
-- ea3.value_on_client `ExtAttr3`

group_concat( distinct a.application_name, ' [color=gray]', a.application_version, '[/color]' order by a.application_name separator ' ' ) as InstalledSoftware,
max(d.report_id) ReportID

from iphone p
left join location_history lh on p.iphone_id = lh.iphone_id
left join location l on lh.location_id = l.location_id

left join iphone_details d on ???

-- left join extension_attribute_values ea1 on ea1.report_id = d.report_id and ea1.extension_attribute_id = ###
left join iphone_applications a on a.report_id = d.report_id
group by d.device_name order by upper(d.device_name);

[ewettach]

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

[jimdavis66]

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

[CGundersen]

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.