Check out the
headlines
headlines
Examples for sample database
This page describes some basic PQL statements and the results they produce when run against the Sample Database:
INSERT statement
The following statement is an example of an INSERT statement.
If you run this statement in your 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:
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
The following SELECT statement selects everything from
the sample database:
SELECT select name, computed_model FROM /network/device/system WHERE name is not null
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
