We have two custom fields that I would like to include in a query. I have been able to get one of the fields to appear by using WHERE field.fields_id= but how would I add "field.value AS `SIM` FROM field LEFT JOIN system ON (field.system_id=system.id) WHERE field.fields_id=3" to the query below
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, system.status AS 'system.status', system.last_seen_by AS `system.last_seen_by`, field.value AS `IMIE` FROM field LEFT JOIN system ON (field.system_id=system.id) WHERE field.fields_id=1 GROUP BY system.id
SQL incoming... We're using MAX to combine multiple rows and CASE to populate the two columns.
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, system.status AS 'system.status', system.last_seen_by AS `system.last_seen_by`, MAX(CASE WHEN field.fields_id = 1 THEN field.value END) AS `IMIE`, MAX(CASE WHEN field.fields_id = 2 THEN field.value END) AS `field 2` FROM field LEFT JOIN system ON (field.system_id = system.id) WHERE field.fields_id IN (1,2) GROUP BY field.system_id;
Also, don't forget about the existing columns in the system table. You can see them by going to menu -> Admin -> Database -> List Tables and selecting the system table. There is already a field called 'serial_imei'.
Thank you, that is exactly what I was needing.
Regarding the serial_sim and serial_imei fields, where do they appear when viewing a device? How can this information be collected using a script? We have audited over 500 HP tablets with cell modems, using the audit_windows.vbs script and neither of these fields are populated base on a custom report I created.
This information cannot be collected using a script at the current time. I have nothing to test developing a script with, either. Are the tablets running Windows or something else? We could likely develop something here. I'm unsure the fields would be shown in the GUI as the device isn't a "phone" of some description. I'll see if I can enable them.
Our current HP Tablets are running Windows 8.1, we will be rolling out new tablet, 250 of them running Windows 10 in the next quarter.
All of our tablets are cell enabled
The section for "Cellular Details" has now been exposed to all devices for the next release. If you want to send me the output from this command, I'll see if we can get it to automatically retrieve the relevant details.
netsh mbn sh interface
Feel free to remove any sensitive info (or just change it). The main thing I need is the exact formatting of the returned information.
Powered by a free Atlassian Confluence Open Source Project License granted to Opmantek. Evaluate Confluence today.