Advanced Queries

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
    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.