Here are some examples for advanced queries:
Flow Queries for Communication Patterns
-
Total Traffic across data centers or sites (DCI link use)
sum(bytes) of flows where ( Dst Manager = 'abc' AND src manager = 'cba') OR ( Dst Manager = 'cba' AND src manager = 'abc')
- Total VTEP traffic
sum(bytes) of flows where Flow Type = 'Src is VTEP' or flow type = 'Dst is VTEP' VTEP traffic grouped by VMKNIC
-
sum(bytes) of flows where Flow Type = 'Src is VTEP' or Flow Type = 'Dst is VTEP' group by ip
- Other Management Traffic
flows where Flow Type = 'Source is VMKNIC' or Flow Type = 'Destination is VMKNIC'
- Flows for extended L2 network
flows where flow type = 'Extended L2 Network' and Destination IP Address = 10.172.13.14
Flow Queries for Aggregation and Grouping
- Total Internet traffic by Source VM
sum(bytes) of flows where Flow Type = 'Internet' group by src vm
- Top ports by total bytes
sum(bytes) of flow group by port order by sum(bytes)
- Top subnet pairs by routed traffic volume
sum(bytes) of flow where Flow Type = 'Routed' group by Source Subnet Network, destination subnet network order by sum(bytes)
- Total VM by total pair bytes
sum(bytes) of flows group by src vm , dest vm order by sum(bytes)
- Top Server VM/Port by total bytes
sum(bytes) of flows group by dest vm , port order by sum(bytes)
Flow Queries for Capacity Estimation and Sizing
- Total bytes of all
vm-internet
/internet-vm
traffic grouped by ESX ( Palo Alto Service VM sizing)sum(bytes) of flows where flow type = 'internet' and (flow type = ' src is vm ' OR flow type = 'destination is vm ') group by host order by sum(bytes)
- Aggregated traffic series for matching flows ( Palo Alto Service VM sizing)
series( sum(byte rate)) of flows where host = 'ddc1-pod2esx012.dm.democompany.net' and (Flow Type = 'Source is VM' OR flow type = 'Destination is VM')
Useful Queries for Application
- VMs in a given application
VM where application = 'CRM'
- Routed Flows from a given application
Flows where source application = CRM and Flow Type = 'Routed'
- Flows between two tiers (one-way)
Flows where src tier = 'App' and Destination Tier = 'DB'
- Flows between two tiers (one-way)
Flows where ( src tier = 'App' and destination Tier = 'DB') OR (destination tier = 'App' and source tier = 'DB')
Useful Queries for VM and ESX
- Properties of Prod -Midtier-1 VM (MAC, IP, host, and so on)
CPU Usage Rate, Network Rate, Memory Usage Rate, mac address, ip , vxlan , host of vm 'Quality control-VM26'
- Network segments having the highest VM count
vm group by l2 network
- Datastores have highest VM count
vm group by datastore
- Hosts by vSphere version
host group by version
- Hosts by vSphere Builds
host group by OS
- All VMs on all host/blade slotted in a particular UCS chassis (Nested Query)
vm where host in (host where Blade like 'sys/chassis-1')
Useful Queries: General Capacity
- Number of Datacenters:
count of datacenter
- Number of clusters
count of cluster
- Number of Hosts
count of host
- Number of VMs
count of vm
- Number of Networks
count of vlan
Useful Queries: Routes
- VNIs by Primary controller
vxlan group by Primary Controller
- Routes for Provider edge 3
routes where vrf = 'Provider Edge 3'
- Routes of DMZ DLR
NextHop Router of routes where VRF = 'LDR-DMZ'
- Routes having the given router as next hop
routes where NextHop Router = 'California-Edge'
Useful Queries: Firewall Rules
-
Firewall rules between two VMs
firewall rules from 'Prod-Midtier-1' to 'Prod-Db-1'
- Rules with have
ANY
sourcefirewall rules where Service Any = true
- VMs for a given rule
vm where Firewall Rule = 'Prod MidTier to Prod DB - DBService '
- Firewall rules where any port is allowed
firewall rule where action = allow and service any = true
- Flows hitting a particular firewall rule
flows where firewall rule = 'Admin to Prod and Lab - SSH'
- Denied flows in the system
flows where firewall action = deny
- View gateway firewall
Firewall Rule where firewall type = 'GatewayFirewall'
- View distributed firewall
Firewall Rule where firewall type = 'Distributed Firewall'
Useful Queries: General Traffic Patterns
- East-West and North-South traffic count, switched traffic count, routed traffic count, and VM to VM traffic count
plan security in last 7 days
Useful Queries: Traffic from a security lens
- Top talkers VMs details
top 7 vm group by name, Vlan order by sum(Total Network Traffic) in last 7 days
- Networks that carry the most traffic
top 7 vlan group by Vlan id, vm count order by sum(Total Network Traffic) in last 7 days
- Networks where most of the communication is within the VLAN (not crossing a physical firewall or L3 boundary)
top 7 flow where Flow Type = 'Switched' group by Subnet Network order by sum(Bytes) in last 7 days
- Networks where most of the communication is across VLAN (may be causing bottleneck problems at physical firewall)
top 7 flow where Flow Type = 'Routed' group by Source Subnet Network, Destination Subnet Network order by sum(Bytes) in last 7 days
- VMs that talks outside the country
top 7 flow where Destination Country != 'United States' group by Source VM, Destination Country order by sum(Bytes) in last 7 days
- Data stores experiencing the most storage latencies
avg(Read Latency), avg(Write Latency) of top 7 vm group by Datastore, vlan order by avg(Write Latency) in last 7 days
Useful Queries: Compliance/Vulnerabilities
- Vulnerable OSs details
vm where Operating System like 'Microsoft Windows Server 2003' or Operating System like 'Microsoft Windows Server 2008' or Operating System like 'Red Hat Enterprise Linux 6' or Operating System like 'Red Hat Enterprise Linux 5' or Operating System like 'SUSE Linux Enterprise 10' group by vlan, Operating System
- Vulnerable OS Count
count of vm where Operating System like 'Microsoft Windows Server 2003' or Operating System like 'Microsoft Windows Server 2008' or Operating System like 'Red Hat Enterprise Linux 6' or Operating System like 'Red Hat Enterprise Linux 5' or Operating System like 'SUSE Linux Enterprise 10'
- Total attack surface due to Old OSs
vm where vlan in (vlan of vm where os in ('Microsoft Windows Server 2003', 'Microsoft Windows Server 2008', 'Red Hat Enterprise Linux 6', 'Red Hat Enterprise Linux 5', 'SUSE Linux Enterprise 10')) group by Vlan
count of vm where vlan in (vlan of vm where os in ('Microsoft Windows Server 2003', 'Microsoft Windows Server 2008', 'Red Hat Enterprise Linux 6', 'Red Hat Enterprise Linux 5', 'SUSE Linux Enterprise 10'))
Note: To get recommended firewall rule for the vulnerable OS, see Recommended Firewall Rule to Secure Vulnerable OS.