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

source

firewall 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'))

To get recommended firewall rule for the vulnerable OS, see Recommended Firewall Rule to Secure Vulnerable OS.

VMware Aria Operations for Networks 6.14