Querying and filtering MSFT_NetTCPConnection through WMI with extensions v2

[AntonioSousa]

Running

Get-WmiObject -Query "SELECT * FROM MSFT_NetTCPConnection where localport=443" -namespace "root\StandardCIMV2"

locally gives a list of data that is very big. I have been able to reduce it by grouping:

Get-WmiObject -Query "SELECT * FROM MSFT_NetTCPConnection where localport=443" -namespace "root\StandardCIMV2" | Group-Object LocalPort

Wondering though how can I do it efficiently with the WMI v2 extensions ?

BTW, the idea is to have metrics for the number of established web connections (I'll probably filter additionally by state...).

[ben_davidson · Accepted solution]

You could make the query more manageable by trimming down the query down to just the required properties with something like: 

Get-WmiObject -Query "SELECT Name, OwningProcess, LocalPort FROM MSFT_NetTCPConnection where localport=443" -namespace "root\StandardCIMV2"

 But unfortunately piping to Group-Object to get a total is not supported by the data source. 

Have you looked at the metrics provided by:

Get-WmiObject -query "SELECT ConnectionsActive, ConnectionsEstablished, ConnectionFailures, ConnectionsPassive, ConnectionsReset FROM Win32_PerfFormattedData_Tcpip_TCPv4"

They may not be fine grain enough for what you need though. If they work, they are counter metrics so you need to handle them as such:

metrics:
    - key: com.dynatrace.extension.host-observability.network.tcp.connections.active
      value: column:ConnectionsActive
      type: count