How do I add multiple custom fields to a query?

 
1
0
-1

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

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      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'.

      Mark.

      1. Rick Wegner

        Thank you, that is exactly what I was needing.

      2. Rick Wegner

        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.

      3. Mark Unwin

        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.

      4. Rick Wegner

        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.

      5. Rick Wegner

        All of our tablets are cell enabled

      CommentAdd your comment...
    2.  
      1
      0
      -1

      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.

        CommentAdd your comment...