NOT operator

The NOT operator adds restrictions to the clause in which it is used.

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example #1
• Example #2
• Example #3
• Example #4

Example #1

For example, a query that searches for devices that have any interfaces with a name other than 'eth0' can be written like this:

SELECT *
  FROM /network/device
  WHERE NOT interface/name = 'eth0'

You can express the same thing by using the not equal to != operator. The following example, like the one above, searches for devices with interfaces with a name other than 'eth0':

SELECT *
  FROM /network/device
  WHERE interface/name != 'eth0'

If run against the sample database, this query includes devices with 'eth0' interfaces, if those devices also had interfaces with another name as well. It excludes devices with 'eth0' interfaces, if those devices had no interfaces with another name:

row
  *
    system
      name ESMITH
      computed_vendor Intel
      computed_class server
      computed_score 10
    os
      version Linux
    interface
      name eth0
      mac_address 02:A1:A2:A3:A4:A4
      in_octets 1229592351
      out_octets 1472928781
      oper_status 1
    interface
      name eth1
      mac_address 03:A1:A2:A3:A4:A5
      in_octets 0
      out_octets 0
      oper_status 0
row
  *
    system
      name AJONES
      computed_vendor Intel
      computed_class server
      computed_score 10
    os
      version Linux
    interface
      name eth0
      mac_address 04:A1:A2:A3:A4:A8
      in_octets 1223098455
      out_octets 1523093749
      oper_status 1
    interface
      name eth1
      mac_address 05:A1:A2:A3:A4:A9
      in_octets 0
      out_octets 0
      oper_status 0
row
  *
    system
      computed_model Ethernet Switch
      computed_class switch
      computed_score 25
    os
      version Ethernet Switch
    interface
      name eth0
      mac_address 06:13:72:F3:0A:F5
      in_octets 0
      out_octets 0
      oper_status 2
    interface
      name eth1
      mac_address 07:13:72:F3:0A:F6
      in_octets 0
      out_octets 0
      oper_status 2
row
  *
    system
      name KRYPTON
      computed_model Windows Workstation
      computed_class workstation
      computed_score 125
    os
      version Darwin Kernel Version 9.2.2
    interface
      name eth0
      mac_address 08:16:CB:FF:FE:66
      in_octets 0
      out_octets 346
      oper_status 1
    interface
      name eth1
      mac_address 09:16:CB:FF:FE:67
      in_octets 0
      out_octets 0
      oper_status 2
    interface
      name eth2
      mac_address 10:16:CB:FF:FE:68
      in_octets 0
      out_octets 0
      oper_status 2
    interface
      name eth3
      mac_address 11:16:CB:FF:FE:69
      in_octets 1598233842
      out_octets 2360815490
      oper_status 1
row
  *
    system
      name NISSINGETTY
      computed_vendor Juniper Networks
      computed_model Netscreen Router
      computed_class router
      computed_score 60
    os
      version NetScreen-5GT 5.1.043a
    interface
      name eth0
      mac_address 12:10:D8:99:2B:C2
      in_octets 1385354750
      out_octets 1451321493
      oper_status 1
    interface
      name eth1
      mac_address 13:10:D8:99:2B:C1
      in_octets 3312952833
      out_octets 3239791359
      oper_status 1 

Example #2

This query collects information about the local drives on your network for which GoToAssist has WMI information, and displays the information in ascending order by machine name. It includes identifying information such as drive name, type, network path, and so on. Without the limits of the WHERE clause, this query would find all drives. The ! narrows the search by eliminating drives described as network connection.

SELECT systemname as "Machine Name",
  name as "Drive",
  description as "Label",
  drivetype as "Type",
  filesystem as "File System",
  volumeserialnumber as "Serial Number",
  format(size, 'human_bytes') as "Total Space",
  format(freespace, 'human_bytes') as "Free Space",
  format(size - freespace, 'human_bytes') as "Used Space",
  format((100 * freespace) / size, 'human_bytes')||'%' as "% Free" 
FROM /network/device/wmi/win32_logicaldisk 
WHERE description != 'Network Connection' order by 1,2

Depending on your database, the results would look something like this:

row
   Machine Name MADRID
   Drive D:
   Label CD-ROM Disc
   Type 5 Compact Disc
   File System
   Serial Number
  Total Space
     size
  Free Space
     freespace
   Used Space null
   % Free null
row
   Machine Name PARIS
   Drive C:
   Label Local Fixed Disk
   Type 3 Local Disk
   File System NTFS
   Serial Number 482AF396
  Total Space
     size 37.24 GB
  Free Space
     freespace 13.03 GB
   Used Space 24.21 GB
   % Free 34% 
   ...

Example #3

This query Lists the services that are currently running on each Windows computer on your network for which GoToAssist has WMI information, in ascending order by system and service name. It lists identifying information such as name, type, and description of service, path to file, and process ID. It also includes service configuration data, such as desktop interactivity, and start mode, as well as the current state of each service.

SELECT systemname as "System",
 displayname as "Display Name",
 name as "Service Name",
 servicetype as "Type",
 desktopinteract as "Interactive with Desktop",
 startmode as "Start Mode",
 startname as "Run As",
 pathname as "Path to File",
 processid as "Process ID",
 description as "Full Description" 
FROM /network/device/wmi/win32_service 
WHERE state != 'Stopped' order by 1,3

Depending on your database, the results would look something like this:

row
   System TOKYO
   Display Name Automatic Updates
   Service Name wuauserv
   Type Share Process
   Interactive with Desktop
   Start Mode Auto
   Run As LocalSystem
   Path to File C:\WINDOWS\system32\svchost.exe -k netsvcs
   Process ID 1234
   Full Description Enables the download and installation 
     of Windows updates. If this service is disabled, this 
     computer will not be able to use the Automatic Updates 
     feature or the Windows Update Web site.
row
   System LONDON
   Display Name Application Layer Gateway Service
   Service Name ALG
   Type Own Process
   Interactive with Desktop
   Start Mode Manual
   Run As NT AUTHORITY\LocalService
   Path to File C:\WINDOWS\System32\alg.exe
   Process ID 5678
   Full Description Provides support for 3rd party protocol 
     plug-ins for Internet Connection Sharing and the 
     Windows Firewall. 
   ...

Example #4

This query provides a routing table for discovered clients. Without the limits in the WHERE NOT defaultipgateway = '' clause, the results are much broader.

SELECT dnshostname as "Host Name", 
  ipaddress as "IP Address", 
  defaultipgateway as "Gateway", 
  gatewaycostmetric as "Cost Metric", 
  macaddress as "MAC Address", 
  description as "Description" 
FROM /network/device/wmi/win32_networkadapterconfiguration 
WHERE NOT defaultipgateway = ''

Depending on your database, the results would look something like this:

row
  Host Name PARIS
  IP Address 10.10.10.10
  Gateway 10.10.10.10
  Cost Metric 10
  MAC Address A2:03:04:05:06:07
  Description Broadcom NetXtreme Gigabit Ethernet
row
  Host Name ROME
  IP Address 10.20.20.20
  Gateway 10.10.10.10
  Cost Metric 20
  MAC Address B3:04:05:06:07:08
  Description Intel(R) PRO/100 VE Network 
    Connection - Packet Scheduler Miniport
row
  Host Name DUBLIN
  IP Address 10.30.30.30
  Gateway 10.10.10.10
  Cost Metric 10
  MAC Address C4:05:06:07:08:09
  Description Intel(R) PRO/1000 MT Network 
    Connection - Packet Scheduler Miniport 
   ...

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 1125 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?