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'

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'))
    Note: To get recommended firewall rule for the vulnerable OS, see Recommended Firewall Rule to Secure Vulnerable OS.