Systems Management

After this line:

MACHINE.OS_BUILD,

Add:

MACHINE.IP,

MACHINE.USER,

Note that the user column in the machine table contains the user logged into the computer at the time the last inventory ran.

You can include the OU via a custom inventory rule:

RegistryValueReturn(HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Group Policy\State\Machine, Distinguished-Name, TEXT)

Including that in the report will require adding a join statement to the query. Once you have created your custom inventory rule get the software title's ID (easiest way is to login to your appliance using /adminui and then opening the software title, the ID will be in the URL). In my case, the ID is 34958, so my join statement looks like this:

JOIN MACHINE_CUSTOM_INVENTORY ADOU on ADOU.ID = MACHINE.ID and ADOU.SOFTWARE_ID = 34958

That line needs to be added after this:

FROM MACHINE MACHINE

Once the join is in place, you can select the OU by adding this after MACHINE.IP,:

ADOU.STR_FIELD_VALUE,

The entire query should now look like this:

SELECT MACHINE.NAME,
       MACHINE.OS_NAME,
       MACHINE.OS_BUILD,
       MACHINE.IP,
       MACHINE.USER,
       ADOU.STR_FIELD_VALUE,
       CASE MACHINE.OS_BUILD
          WHEN '10240' THEN '1507 (RTM)'
          WHEN '10586' THEN '1511'
          WHEN '14393' THEN '1607'
          WHEN '15063' THEN '1703'
          WHEN '16299' THEN '1709'
          ELSE 'Unknown OS Build'
       END
          AS OS_VERSION,
      CASE MACHINE.OS_BUILD 
          WHEN '10240' THEN Date_Add(Date('2015-07-29'),INTERVAL 18 MONTH)
          WHEN '10586' THEN Date_Add(Date('2015-11-10'),INTERVAL 18 MONTH)
          WHEN '14393' THEN Date_Add(Date('2016-08-02'),INTERVAL 18 MONTH)
          WHEN '15063' THEN Date_Add(Date('2017-04-05'),INTERVAL 18 MONTH)
          WHEN '16299' THEN Date_Add(Date('2017-10-17'),INTERVAL 18 MONTH)
          ELSE 'NO EOL DATE' 
      END 
          AS CALCULATED_EOL_DATE,
      CASE MACHINE.OS_BUILD 
          WHEN '10240' THEN Date('2017-05-09')
          WHEN '10586' THEN Date('2017-10-10')
          WHEN '14393' THEN 'Tentatively March 2018'
          WHEN '15063' THEN 'Tentatively September 2018'
          WHEN '16299' THEN 'Tentatively March 2019'
          ELSE 'NO MS EOL DATE' 
      END 
          AS MS_EOL_DATE,
      CASE MACHINE.OS_BUILD
          WHEN '10240' THEN DATEDIFF(DATE('2017-05-09'), NOW())
          WHEN '10586' THEN DATEDIFF(DATE('2017-10-10'), NOW())
          ELSE 'NO FIX EOL DATE'
       END
          AS DAYS_OVER_EOL
  FROM MACHINE MACHINE
  JOIN MACHINE_CUSTOM_INVENTORY ADOU on ADOU.ID = MACHINE.ID and ADOU.SOFTWARE_ID = 34958
 WHERE MACHINE.OS_NAME LIKE 'Microsoft Windows 10%'
 ORDER BY MACHINE.OS_BUILD DESC

In the SELECT portion of your script add:

MACHINE.IP,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USERNAME,

I like my computer names to be consistent, so I capitalize them:

UPPER(MACHINE.NAME) as NAME,

To get the OU of the computer, you will have to do a custom inventory rule (CIR), explained here, by using a powershell script, example here.

Here is a list of all the MACHINE FIELDS that are available in that table:

#ID, MODIFIED, CREATED, USER, USER_FULLNAME, NAME, MAC, IP, OS_NAME, OS_NUMBER, OS_MAJOR, OS_MINOR, OS_MINOR2, SERVICE_PACK, OS_ID, NOTES, LAST_MESSAGE, LAST_INVENTORY, LAST_SYNC, LAST_CLIENT_UPDATE, DOMAIN, OS_VERSION, OS_BUILD, OS_INSTALLED_DATE, LAST_REBOOT, UPTIME, SYSTEM_DIRECTORY, SYSTEM_DESCRIPTION, RAM_TOTAL, RAM_USED, RAM_MAX, CS_MANUFACTURER, CS_MODEL, CS_DOMAIN, LAST_USER, USER_LOGGED, USER_NAME, USER_DOMAIN, BIOS_NAME, BIOS_VERSION, BIOS_MANUFACTURER, BIOS_DESCRIPTION, BIOS_IDENTIFICATION_CODE, BIOS_SERIAL_NUMBER, BIOS_RELEASE_DATE, CSP_ID_NUMBER, ASSET_TAG, VIRTUAL, DOT_NET_VERSIONS, IE_VERSION, OS_FAMILY, MOTHERBOARD_PRIMARY_BUS, MOTHERBOARD_SECONDARY_BUS, PROCESSORS, SOUND_DEVICES, CDROM_DEVICES, VIDEO_CONTROLLERS, MONITOR, REGISTRY_SIZE, REGISTRY_MAX_SIZE, PAGEFILE_SIZE, PAGEFILE_MAX_SIZE, PRINTERS, KUID, MANUAL_ENTRY, SYS_ARCH, OS_ARCH, FORCE_INVENTORY, CUSTOM_FIELD_VALUE0, CUSTOM_FIELD_VALUE1, CUSTOM_FIELD_VALUE2, CUSTOM_FIELD_VALUE3, CUSTOM_FIELD_VALUE4, CUSTOM_FIELD_VALUE5, INVENTORY_STARTED, CLIENT_VERSION, CONNECT_TIME, DISCONNECT_TIME, SMMP_VERSION, PATCHLINK_LANGUAGE_ID, LAST_SHUTDOWN, CHASSIS_TYPE, TZ_AGENT, WMI_STATUS