Examples for sample database
This page describes some basic PQL statements and the results they produce when run against the Sample Database.
You can run the examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:
INSERT statement
The following statement is an example of an INSERT statement. If you run
this statement in your Query Sandbox,
you can experiment with 5 devices: a server, a printer, a couple of desktops, a switch,
and a router:
INSERT INTO / values {
network => {
device => {
system => {
name => '5627',
computed_vendor => 'LANIER',
computed_model => '5627',
computed_class => 'printer',
computed_score => '115'
},
os => {
version => 'LANIER 5627 5.20'
},
interface => {
name => 'eth0',
mac_address => '01:01:02:03:04:05',
in_octets => '1995335536',
out_octets => '248342455',
oper_status => '1'
}
},
device => {
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'
}
},
device => {
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'
}
},
device => {
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'
}
},
device => {
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'
}
},
device => {
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'
}
}
}
}
Results of INSERT statement
The Sandbox now contains the following sample database, which includes 5 devices: a server, a printer, a couple of desktops, a switch, and a router:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address 01:01:02:03:04:05
in_octets 1995335536
out_octets 248342455
oper_status 1
device
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
nameeth1
mac_address 03:A1:A2:A3:A4:A5
in_octets 0
out_octets 0
oper_status 0
device
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
device
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
device
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
device
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
SELECT statement
You can run the following SELECT statement to select everything from
the sample database:
SELECT name, computed_model FROM /network/device/system WHERE name is not null
Note: To add a new PQL statement in the Query Sandbox field, simply select a portion of the previous statement, then press CTRL+A on your keyboard to select all. The previous statement is overwritte as soon as you enter your next statement.
Results of SELECT statement
If you run this statement against the sample database created above, you'll get the following results:
row
name5627
computed_model5627
row
nameESMITH
computed_modelnull
row
nameAJONES
computed_modelnull
row
nameKRYPTON
computed_modelWindows Workstation
row
nameNISSINGETTY
computed_modelNetscreen Router
MERGE statement
The following MERGE statement replaces XX, and adds XX:
MERGE@'2008-03-21T15:50:01.528935Z' into / values {
network[true] => {
device[interface/mac_address='02:A1:A2:A3:A4:A4'] => {
system[true] => {
meminfo[true] => {
SwapTotal[true] => '3997474816',
SwapFree[true] => '3548553216',
MemFree[true] => '40374272',
HighTotal[true] => '1198030848',
Committed_AS[true] => '1972723712',
SUnreclaim[true] => '19660800',
NFS_Unstable[true] => '0',
VmallocChunk[true] => '64999424',
Writeback[true] => '0',
MemTotal[true] => '2116120576'
}
}
}
}
}
Results of MERGE statement
If you run this statement against the sample database created above, you'll get the following results (truncated to show only relevant changes):
row
*
network
...
device
interface
mac_address 02:A1:A2:A3:A4:A4
name eth0
in_octets 1229592351
out_octets 1472928781
oper_status 1
interface
mac_address 03:A1:A2:A3:A4:A5
name eth1
in_octets 0
out_octets 0
oper_status 0
system
name ESMITH
computed_vendor Intel
computed_class server
computed_score 10
meminfo
memtotal 2116120576
writeback 0
vmallocchunk 64999424
nfs_unstable 0
sunreclaim 19660800
committed_as 1972723712
hightotal 1198030848
memfree4 0374272
swapfree 3548553216
swaptotal 3997474816
os
version Linux
...
UPDATE statement
The following UPDATE statement changes the name of each
eth0 interface to eth17:
UPDATE /network/device/interface[name='eth0'] SET name = 'eth17'
Results of UPDATE statement
If you run this statement against the sample database created above, you'll get the following results, the blue text indicating what is changed:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth17
mac_address 01:01:02:03:04:05
in_octets 1995335536
out_octets 248342455
oper_status 1
device
system
name ESMITH
computed_vendor Intel
computed_class server
computed_score 10
os
version Linux
interface
name eth17
mac_address 02:A1:A2:A3:A4:A4
in_octets 1229592351
out_octets 1472928781
oper_status 1
interface
nameeth1
mac_address 03:A1:A2:A3:A4:A5
in_octets 0
out_octets 0
oper_status 0
device
system
name AJONES
computed_vendor Intel
computed_class server
computed_score 10
os
version Linux
interface
name eth17
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
device
system
computed_model Ethernet Switch
computed_class switch
computed_score 25
os
version Ethernet Switch
interface
name eth17
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
device
system
name KRYPTON
computed_model Windows Workstation
computed_class workstation
computed_score 125
os
version Darwin Kernel Version 9.2.2
interface
name eth17
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
device
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 eth17
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
DELETE statement
The following DELETE statement removes an interface:
DELETE FROM /network/device/interface WHERE ../interface/name = 'eth0'
Results of DELETE statement
If you run this statement against the sample database created above, you'll get the following results, the red text indicating what is deleted:
row
*
network
device
system
name 5627
computed_vendor LANIER
computed_model 5627
computed_class printer
computed_score 115
os
version LANIER 5627 5.20
interface
name eth0
mac_address 01:01:02:03:04:05
in_octets 1995335536
out_octets 248342455
oper_status 1
device
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
device
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
device
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
device
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
device
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
