jq for Infrastructure APIs

Infrastructure APIs return JSON. Master these patterns to automate ISE, pfSense, Vault, and wireless controllers.

Cisco ISE via netapi

Session Analysis

# Active sessions table
netapi ise --format json mnt sessions | jq -r '
  ["MAC", "USER", "IP", "STATE"],
  (.[] | [.calling_station_id, .user_name, .framed_ip_address, .session_state]) |
  @tsv
' | column -t

# Sessions with timing
netapi ise --format json mnt sessions | jq '
  .[] | select(.session_state == "AUTHENTICATED") |
  {
    mac: .calling_station_id,
    user: .user_name,
    ip: .framed_ip_address,
    duration: (.acct_session_time | tonumber | . / 60 | floor | "\(.)m"),
    policy: .access_policy_matched_rule
  }
'

# Sessions by authorization profile
netapi ise --format json mnt sessions | jq '
  group_by(.authorization_profile) |
  map({profile: .[0].authorization_profile, count: length}) |
  sort_by(.count) | reverse
'

# Find sessions for specific MAC
netapi ise --format json mnt sessions | jq --arg mac "AA:BB:CC:DD:EE:FF" '
  .[] | select(.calling_station_id | ascii_upcase == ($mac | ascii_upcase))
'

Policy Sets

# List policy sets
netapi ise --format json get-policy-sets | jq '.[] | {
  name,
  state,
  rank,
  rules: (.rules | length)
}'

# Find disabled policies
netapi ise --format json get-policy-sets | jq '
  .[] | select(.state == "disabled") | .name
'

# Policy set rules detail
netapi ise --format json get-policy-sets | jq '
  .[] | select(.name == "Wired_802.1X_Closed") |
  .rules[] | {name, state, condition, authzProfile}
'

# Find which policy sets use specific authorization profile
PROFILE="Corp-Access"
netapi ise --format json get-policy-sets | jq --arg p "$PROFILE" '
  .[] | select(.rules[]?.authzProfile == $p) |
  {
    policySet: .name,
    matchingRules: [.rules[] | select(.authzProfile == $p) | .name]
  }
'

Authorization Profiles

# List profiles with dACL
netapi ise --format json get-authz-profiles | jq '
  .[] | {name, dacl: .daclName, vlan: .vlan}
'

# Profiles using specific dACL
netapi ise --format json get-authz-profiles | jq --arg dacl "PERMIT_ALL" '
  .[] | select(.daclName == $dacl) | .name
'

dACL Operations

# Extract dACL rules
netapi ise api-call openapi GET '/api/v1/policy/network-access/downloadable-acl' | jq -r '
  .response[] | select(.name | test("Linux")) |
  "=== \(.name) ===\n\(.dacl)"
' | sed 's/\\n/\n/g'

# Compare two dACLs
DACL1="Linux-Onboard"
DACL2="Linux-EAP-TLS"
diff -u \
  <(netapi ise api-call openapi GET '/api/v1/policy/network-access/downloadable-acl' | \
    jq -r --arg d "$DACL1" '.response[] | select(.name == $d) | .dacl' | tr '\\n' '\n') \
  <(netapi ise api-call openapi GET '/api/v1/policy/network-access/downloadable-acl' | \
    jq -r --arg d "$DACL2" '.response[] | select(.name == $d) | .dacl' | tr '\\n' '\n')

Endpoint Identity Groups

# List groups with descriptions
netapi ise api-call openapi GET '/api/v1/endpoint-identity-group?size=100' | jq -r '
  ["GROUP", "DESCRIPTION", "SYSTEM"],
  (.response[] | [.name, (.description // "N/A"), (if .systemDefined then "yes" else "no" end)]) |
  @tsv
' | column -t -s$'\t'

# Find endpoints in group
netapi ise api-call openapi GET '/api/v1/endpoint?filter=groupId.EQ.<group-id>' | jq '
  .response[] | {mac, profile: .profileId}
'

Certificate Trust Store

# Certificates expiring within 90 days
netapi ise api-call openapi GET '/api/v1/certs/trusted-certificate?size=100' | jq '
  (now + 7776000) as $cutoff |
  .response[] |
  (.expirationDate | split("T")[0] | strptime("%Y-%m-%d") | mktime) as $exp |
  select($exp < $cutoff) |
  {
    name: .friendlyName,
    expires: .expirationDate | split("T")[0],
    issuer: .issuedBy
  }
'

pfSense via netapi

DNS Overrides

# List all DNS overrides
netapi pfsense dns list --format json | jq '.[] | {host, domain, ip}'

# Find entries matching pattern
netapi pfsense dns list --format json | jq '
  .[] | select(.host | test("vault"))
'

# Count by domain
netapi pfsense dns list --format json | jq '
  group_by(.domain) |
  map({domain: .[0].domain, count: length}) |
  sort_by(.count) | reverse
'

# Find duplicates (same host, different IPs)
netapi pfsense dns list --format json | jq '
  group_by(.host) |
  map(select(length > 1)) |
  .[] | {host: .[0].host, ips: [.[] | .ip]}
'

Firewall Rules

# List rules summary
netapi pfsense rules list --format json | jq -r '
  ["IFACE", "TYPE", "SRC", "DST", "DESC"],
  (.[] | [.interface, .type, .source, .destination, .descr]) |
  @tsv
' | column -t

# Rules with logging disabled (potential gap)
netapi pfsense rules list --format json | jq '
  .[] | select(.log == false) |
  {interface, type, source, destination, descr}
'

# Count rules per interface
netapi pfsense rules list --format json | jq '
  group_by(.interface) |
  map({interface: .[0].interface, count: length}) |
  sort_by(.count) | reverse
'

DHCP Leases

# Active leases
netapi pfsense dhcp leases --format json | jq '.[] | {
  ip,
  mac,
  hostname,
  starts: .starts,
  ends: .ends
}'

# Leases expiring within 1 hour
netapi pfsense dhcp leases --format json | jq '
  (now + 3600) as $soon |
  .[] | select(.ends | strptime("%Y-%m-%d %H:%M:%S") | mktime < $soon) |
  {ip, mac, hostname, ends}
'

HashiCorp Vault

Secret Listing

# List keys in path
vault kv list -format=json secret/domus | jq -r '.[]'

# Recursive list (KV v2)
vault kv list -format=json secret/data | jq -r '.[]'

# Keys with metadata
vault kv metadata list -format=json secret/domus | jq '
  to_entries[] | {key: .key, versions: .value.versions}
'

PKI Operations

# Issue certificate and extract parts
vault write -format=json pki_int/issue/domus-client \
  common_name="host.inside.domusdigitalis.dev" | jq -r '
  {
    certificate: .data.certificate,
    private_key: .data.private_key,
    ca_chain: .data.ca_chain[0],
    expiration: (.data.expiration | strftime("%Y-%m-%d %H:%M:%S")),
    serial: .data.serial_number
  }
'

# Just the cert
vault write -format=json pki_int/issue/domus-client \
  common_name="host.example.com" | jq -r '.data.certificate'

# Certificate inventory
vault list -format=json pki_int/certs | jq -r '.[]' | while read serial; do
  vault read -format=json "pki_int/cert/$serial" | jq -r '
    [.data.serial_number, (.data.expiration | strftime("%Y-%m-%d")), .data.common_name] | @tsv
  '
done | column -t -N "SERIAL,EXPIRES,CN"

# Certificates expiring within 30 days
vault list -format=json pki_int/certs | jq -r '.[]' | while read serial; do
  vault read -format=json "pki_int/cert/$serial" 2>/dev/null
done | jq -s '
  (now + 2592000) as $cutoff |
  .[] | select(.data.expiration < $cutoff) |
  {cn: .data.common_name, expires: (.data.expiration | strftime("%Y-%m-%d")), serial: .data.serial_number}
'

Token and Auth Info

# Current token info
vault token lookup -format=json | jq '{
  display_name,
  policies,
  ttl,
  creation_time: (.creation_time | strftime("%Y-%m-%d %H:%M:%S"))
}'

# List auth methods
vault auth list -format=json | jq 'to_entries[] | {
  path: .key,
  type: .value.type,
  description: .value.description
}'

SSH Secrets Engine

# Sign SSH public key
vault write -format=json ssh-client-signer/sign/admin \
  public_key=@~/.ssh/id_ed25519.pub | jq -r '.data.signed_key'

# With custom principals
vault write -format=json ssh-client-signer/sign/admin \
  public_key=@~/.ssh/id_ed25519.pub \
  valid_principals="root,admin" | jq '{
    signed_key: .data.signed_key,
    serial: .data.serial_number,
    valid_until: (.data.valid_after | . + 86400 | strftime("%Y-%m-%d %H:%M:%S"))
  }'

Wireless Controllers (WLC)

Client Distribution

# Clients per WLAN
netapi wlc clients --format json | jq '
  group_by(.wlan_profile_name) |
  map({wlan: .[0].wlan_profile_name, clients: length}) |
  sort_by(.clients) | reverse
'

# AP load distribution
netapi wlc clients --format json | jq '
  group_by(.ap_name) |
  map({
    ap: .[0].ap_name,
    clients: length,
    channels: ([.[] | .channel] | unique)
  }) |
  sort_by(.clients) | reverse
'

# Clients with weak signal
netapi wlc clients --format json | jq '
  .[] | select((.rssi | tonumber) < -75) |
  {mac: .mac_address, ap: .ap_name, rssi, wlan: .wlan_profile_name}
'

Rogue Detection

# Nearby rogues (strong signal = close)
netapi wlc rogues --format json | jq '
  .[] | select((.rssi | tonumber) > -70) |
  {
    mac: .mac_address,
    rssi,
    ssid,
    detecting_ap: .detecting_aps[0],
    classification: .classification
  }
'

# Rogue count by classification
netapi wlc rogues --format json | jq '
  group_by(.classification) |
  map({classification: .[0].classification, count: length})
'

AP Status

# AP inventory
netapi wlc aps --format json | jq '.[] | {
  name: .ap_name,
  model,
  ip: .ip_address,
  status: .operational_status,
  clients: .client_count
}'

# APs with issues
netapi wlc aps --format json | jq '
  .[] | select(.operational_status != "associated") |
  {name: .ap_name, status: .operational_status, last_reboot: .reboot_reason}
'

Generic API Patterns

Explore Unknown API Response

# Step 1: What type is the root?
curl -s "$API_URL" | jq 'type'

# Step 2: What keys exist?
curl -s "$API_URL" | jq 'keys'

# Step 3: Keys with types
curl -s "$API_URL" | jq 'to_entries[] | "\(.key): \(.value | type)"'

# Step 4: Drill into nested objects
curl -s "$API_URL" | jq '.data | to_entries[] | "\(.key): \(.value | type)"'

Pagination Handling

# Collect all pages
PAGE=1
while true; do
  RESPONSE=$(curl -s "$API_URL?page=$PAGE&per_page=100")
  COUNT=$(echo "$RESPONSE" | jq '.items | length')

  echo "$RESPONSE" | jq '.items[]'

  [[ "$COUNT" -lt 100 ]] && break
  ((PAGE++))
done | jq -s '.'

Error Handling

# Check for error field
curl -s "$API_URL" | jq '
  if .error then
    "ERROR: \(.error.message)"
  else
    .data
  end
'

# Exit on error
RESPONSE=$(curl -s "$API_URL")
if echo "$RESPONSE" | jq -e '.error' >/dev/null 2>&1; then
  echo "API Error: $(echo "$RESPONSE" | jq -r '.error.message')" >&2
  exit 1
fi
echo "$RESPONSE" | jq '.data'

Wazuh SIEM

Alert Triage

# High severity alerts from last hour
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/security/events?limit=1000" | jq '
  .data.affected_items[] |
  select(.rule.level >= 12) |
  {
    timestamp,
    agent: .agent.name,
    rule_id: .rule.id,
    description: .rule.description,
    src_ip: .data.srcip,
    dst_ip: .data.dstip
  }
'

# Alerts grouped by rule
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/security/events?limit=5000" | jq '
  [.data.affected_items[] | {rule: .rule.id, description: .rule.description}] |
  group_by(.rule) |
  map({rule: .[0].rule, description: .[0].description, count: length}) |
  sort_by(.count) | reverse | .[0:20]
'

# MITRE ATT&CK tactic breakdown
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/security/events?limit=5000" | jq '
  [.data.affected_items[] | select(.rule.mitre) | .rule.mitre.tactic[]] |
  group_by(.) | map({tactic: .[0], count: length}) | sort_by(.count) | reverse
'

# Top source IPs generating alerts
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/security/events?limit=5000" | jq '
  [.data.affected_items[] | select(.data.srcip) | .data.srcip] |
  group_by(.) | map({ip: .[0], count: length}) |
  sort_by(.count) | reverse | .[0:15]
'

Agent Health

# Agents by status
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/agents" | jq '
  [.data.affected_items[] | .status] |
  group_by(.) | map({status: .[0], count: length})
'

# Disconnected agents (offline > 1 hour)
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/agents" | jq '
  .data.affected_items[] |
  select(.status == "disconnected") |
  {
    name,
    ip,
    last_keepalive: .lastKeepAlive,
    os: .os.platform,
    version
  }
'

# Agent version distribution
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/agents" | jq '
  [.data.affected_items[] | .version // "unknown"] |
  group_by(.) | map({version: .[0], count: length}) |
  sort_by(.count) | reverse
'

Vulnerability Assessment

# Critical CVEs across all agents
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/vulnerability?limit=5000" | jq '
  .data.affected_items[] |
  select(.severity == "Critical") |
  {
    agent: .agent_id,
    cve: .cve,
    package: .package.name,
    version: .package.version,
    cvss: .cvss3_score
  }
'

# Vulnerability count by severity
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/vulnerability?limit=5000" | jq '
  [.data.affected_items[] | .severity] |
  group_by(.) | map({severity: .[0], count: length})
'

# Most common vulnerable packages
curl -s -k -u "$WAZUH_USER:$WAZUH_PASS" \
  "https://${WAZUH_API}/vulnerability?limit=5000" | jq '
  [.data.affected_items[] | .package.name] |
  group_by(.) | map({package: .[0], count: length}) |
  sort_by(.count) | reverse | .[0:10]
'

gopass Secrets Inventory

Audit secrets structure without exposing values.

List Structure

# List all secret paths (tree structure to JSON)
gopass ls --flat | jq -R -s 'split("\n") | map(select(length > 0))'

# Group by first path component (category)
gopass ls --flat | jq -R -s '
  split("\n") | map(select(length > 0)) |
  map(split("/")[0]) |
  group_by(.) | map({category: .[0], count: length}) |
  sort_by(.count) | reverse
'

# Find secrets by pattern (without revealing content)
gopass ls --flat | jq -R -s '
  split("\n") | map(select(. | test("ise|radius|wifi"; "i")))
'

Audit Metadata

# Extract age recipients from .age-recipients files
find ~/.local/share/gopass/stores -name ".age-recipients" -exec cat {} \; | jq -R -s '
  split("\n") | map(select(length > 0)) | unique |
  {total_keys: length, keys: .}
'

# Secret age (modification times)
gopass ls --flat | while read path; do
  MTIME=$(gopass show -n "$path" 2>/dev/null | head -1 | stat -c %Y 2>/dev/null)
  echo "{\"path\":\"$path\",\"modified\":$MTIME}"
done | jq -s 'sort_by(.modified)'

# Find potentially stale secrets (not modified in 1 year)
CUTOFF=$(($(date +%s) - 31536000))
gopass ls --flat | while read path; do
  stat ~/.local/share/gopass/stores/*/$(dirname "$path")/.gpg-id 2>/dev/null
done

Rotation Tracking

# Extract rotation metadata (if you track it in YAML front matter)
gopass show "some/secret" | jq -R -s '
  if test("^---") then
    split("---")[1] | @yaml
  else
    {note: "no metadata"}
  end
'

ISE Data Connect (MSCHAPv2 Migration)

Query ISE Data Connect for migration analysis.

Find MSCHAPv2 Authentications

# Export from ISE Data Connect to JSON, then analyze
# MSCHAPv2 vs EAP-TLS breakdown
cat ise_radius_auth.json | jq '
  [.[] | .eap_authentication] |
  group_by(.) | map({method: .[0], count: length}) |
  sort_by(.count) | reverse
'

# Devices still using MSCHAPv2
cat ise_radius_auth.json | jq '
  .[] | select(.eap_authentication | test("MSCHAPv2|PEAP")) |
  {
    mac: .calling_station_id,
    user: .user_name,
    device_type: .device_type,
    os: .operating_system,
    policy: .authorization_policy_matched_rule
  }
' | jq -s 'unique_by(.mac)'

# Count by device type (prioritize migration order)
cat ise_radius_auth.json | jq '
  [.[] | select(.eap_authentication | test("MSCHAPv2|PEAP")) | .device_type // "Unknown"] |
  group_by(.) | map({type: .[0], count: length}) |
  sort_by(.count) | reverse
'

# Devices authenticating with MSCHAPv2 in last 30 days (active)
cat ise_radius_auth.json | jq --arg cutoff "$(date -d '30 days ago' +%Y-%m-%d)" '
  .[] |
  select(.eap_authentication | test("MSCHAPv2|PEAP")) |
  select(.timestamp[:10] >= $cutoff) |
  .calling_station_id
' | jq -s 'unique | length'

Migration Progress Tracking

# Compare two exports (before/after migration window)
jq -n --slurpfile before before.json --slurpfile after after.json '
  ($before[0] | [.[] | select(.eap_authentication | test("MSCHAPv2")) | .calling_station_id] | unique) as $before_macs |
  ($after[0] | [.[] | select(.eap_authentication | test("MSCHAPv2")) | .calling_station_id] | unique) as $after_macs |
  {
    before_count: ($before_macs | length),
    after_count: ($after_macs | length),
    migrated: (($before_macs - $after_macs) | length),
    new_mschapv2: (($after_macs - $before_macs) | length),
    still_mschapv2: $after_macs
  }
'

# Successful EAP-TLS authentications (migration targets confirmed)
cat ise_radius_auth.json | jq '
  .[] | select(.eap_authentication == "EAP-TLS") |
  select(.passed == true) |
  {mac: .calling_station_id, user: .user_name, cert_cn: .certificate_cn}
' | jq -s 'unique_by(.mac) | length'

Azure/Sentinel Integration

Process KQL query results exported as JSON.

Incident Analysis

# az sentinel incident list (via CLI)
az sentinel incident list --resource-group $RG --workspace-name $WORKSPACE -o json | jq '
  .[] | select(.properties.severity == "High") |
  {
    title: .properties.title,
    severity: .properties.severity,
    status: .properties.status,
    created: .properties.createdTimeUtc,
    tactics: .properties.additionalData.tactics
  }
'

# Group incidents by classification
az sentinel incident list --resource-group $RG --workspace-name $WORKSPACE -o json | jq '
  [.[] | .properties.classification // "Undetermined"] |
  group_by(.) | map({classification: .[0], count: length})
'

# Time to close (closed incidents)
az sentinel incident list --resource-group $RG --workspace-name $WORKSPACE -o json | jq '
  .[] | select(.properties.status == "Closed") |
  ((.properties.closedTimeUtc | fromdateiso8601) - (.properties.createdTimeUtc | fromdateiso8601)) as $duration |
  {
    title: .properties.title,
    hours_to_close: ($duration / 3600 | floor)
  }
'

KQL Export Processing

# Process exported KQL query results
# Example: SigninLogs query results
cat signinlogs_export.json | jq '
  .[] | select(.ResultType != 0) |  # Failed logins
  {
    user: .UserPrincipalName,
    app: .AppDisplayName,
    ip: .IPAddress,
    location: .Location,
    error: .ResultDescription,
    time: .TimeGenerated
  }
'

# Failed logins by user
cat signinlogs_export.json | jq '
  [.[] | select(.ResultType != 0) | .UserPrincipalName] |
  group_by(.) | map({user: .[0], failures: length}) |
  sort_by(.failures) | reverse | .[0:10]
'

# Geographic anomalies (logins from new locations)
cat signinlogs_export.json | jq '
  [.[] | {user: .UserPrincipalName, country: .Location.countryOrRegion}] |
  group_by(.user) | map({
    user: .[0].user,
    countries: [.[].country] | unique,
    country_count: ([.[].country] | unique | length)
  }) | map(select(.country_count > 2))
'

Defender Alerts

# Microsoft Defender alerts via Graph API
az rest --method get \
  --uri "https://graph.microsoft.com/v1.0/security/alerts_v2" | jq '
  .value[] | select(.severity == "high") |
  {
    title,
    severity,
    status,
    category,
    devices: [.evidence[] | select(.type == "device") | .deviceDnsName],
    created: .createdDateTime
  }
'

# Alert timeline (for incident correlation)
az rest --method get \
  --uri "https://graph.microsoft.com/v1.0/security/alerts_v2" | jq '
  [.value[] | {
    time: .createdDateTime,
    title,
    severity,
    category
  }] | sort_by(.time)
'

Certificate Expiry Monitoring

Comprehensive PKI monitoring across infrastructure.

ISE Trust Store

# All certificates expiring in 90 days
netapi ise api-call openapi GET '/api/v1/certs/trusted-certificate?size=200' | jq '
  (now + 7776000) as $cutoff |
  .response[] |
  (.expirationDate | fromdateiso8601) as $exp |
  select($exp < $cutoff) |
  {
    name: .friendlyName,
    subject: .subject,
    issuer: .issuedBy,
    expires: (.expirationDate | split("T")[0]),
    days_left: ((($exp - now) / 86400) | floor),
    usage: [.usedBy[]?] | join(", ")
  }
' | jq -s 'sort_by(.days_left)'

# System certificates (identity certs)
netapi ise api-call openapi GET '/api/v1/certs/system-certificate' | jq '
  .response[] |
  {
    host: .hostName,
    cn: .subject | capture("CN=(?<cn>[^,]+)").cn,
    expires: .expirationDate | split("T")[0],
    usedFor: .usedFor
  }
'

Vault PKI Comprehensive

# All certs with expiry timeline
vault list -format=json pki_int/certs 2>/dev/null | jq -r '.[]' | while read serial; do
  vault read -format=json "pki_int/cert/$serial" 2>/dev/null
done | jq -s '
  . as $certs |
  now as $now |
  $certs | map(
    (.data.expiration - $now) / 86400 | floor
  ) as $days |
  {
    total: ($certs | length),
    expired: ([$certs[] | select(.data.expiration < now)] | length),
    expiring_30d: ([$certs[] | select(.data.expiration < (now + 2592000) and .data.expiration > now)] | length),
    expiring_90d: ([$certs[] | select(.data.expiration < (now + 7776000) and .data.expiration > now)] | length)
  }
'

Host Certificate Check

# Check multiple hosts via openssl, output as JSON
HOSTS="ise-01.domain:443 vault.domain:8200 ldaps.domain:636"
for hostport in $HOSTS; do
  EXPIRY=$(echo | openssl s_client -connect "$hostport" -servername "${hostport%%:*}" 2>/dev/null | \
    openssl x509 -noout -enddate 2>/dev/null | cut -d= -f2)
  echo "{\"host\":\"$hostport\",\"expires\":\"$EXPIRY\"}"
done | jq -s '.'