/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Adding IP address and username to SQL report (Working SQL query below)

02/01/2018 1296 views
Hello,

I have this query showing the Windows 10 Builds we have and whether they are supported or not, but I want to add the IP and Username, how can I do this?  I've love to get the Active Directory OU they are in too, but not even sure that is possible.

SELECT MACHINE.NAME,
       MACHINE.OS_NAME,
       MACHINE.OS_BUILD,
       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
 WHERE MACHINE.OS_NAME LIKE 'Microsoft Windows 10%'
 ORDER BY MACHINE.OS_BUILD DESC
0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

4

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




Answered 02/06/2018 by: chucksteel
Red Belt

  • Upvote for taking time to include the "how-to" on adding CIR to the report, the necessary join and such.

All Answers

0

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 
Answered 02/06/2018 by: five.
Second Degree Green Belt

This content is currently hidden from public view.
Reason: Removed by member request For more information, visit our FAQ's.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ