Group Bro JSON notices, sort by count, output first_heard, last_heard

notice_counts.sh

zcat */notice.*.gz \
  | cat - current/notice.log \
  | jq -s '.[].ts |= (split(".")[0] | . + "Z" | fromdateiso8601 ) 
    | group_by(.note) | [ .[] | {note: unique_by(.note)[0].note, first_heard: min_by(.ts).ts 
    | todate, last_heard: max_by(.ts).ts | todate, count: length } ] | sort_by(.count) | reverse '

notice_example_output.json

[
  {
    "note": "SSL::Invalid_Server_Cert",
    "first_heard": "2015-07-28T17:44:20Z",
    "last_heard": "2015-07-30T14:17:32Z",
    "count": 749
  },
  {
    "note": "PacketFilter::Dropped_Packets",
    "first_heard": "2015-07-29T15:05:32Z",
    "last_heard": "2015-07-29T16:20:07Z",
    "count": 13
  },
  {
    "note": "Traceroute::Detected",
    "first_heard": "2015-07-29T17:22:13Z",
    "last_heard": "2015-07-29T17:22:13Z",
    "count": 1
  },
  {
    "note": "DNS::TOO_MANY_SUBDOMAINS",
    "first_heard": "2015-07-29T18:52:43Z",
    "last_heard": "2015-07-29T18:52:43Z",
    "count": 1
  },
  {
    "note": "DNS::DNS_reverse_scan_PTR",
    "first_heard": "2015-07-29T19:12:00Z",
    "last_heard": "2015-07-29T19:12:00Z",
    "count": 1
  }
]

raw_notice_log_sample.json

{"ts":"2015-07-30T00:00:28.110167Z","uid":"CVyCLr2380Nk61NKE6","id.orig_h":"72.179.140.146","id.orig_p":46102,"id.resp_h":"207.171.163.134","id.resp_p":443,"proto":"tcp","note":"SSL::Invalid_Server_Cert","msg":"SSL certificate validation failed with (unable to get local issuer certificate)","sub":"CN=todo-ta-g7g.amazon.com,O=Amazon.com\u005c, Inc.,L=Seattle,ST=Washington,C=US","src":"72.179.140.146","dst":"207.171.163.134","p":443,"peer_descr":"worker-1","actions":["Notice::ACTION_LOG"],"suppress_for":3600.0,"dropped":false}
{"ts":"2015-07-30T00:04:41.496833Z","uid":"Chvobu1A8n3kCJ5M8d","id.orig_h":"72.179.140.146","id.orig_p":52099,"id.resp_h":"64.86.135.143","id.resp_p":443,"proto":"tcp","note":"SSL::Invalid_Server_Cert","msg":"SSL certificate validation failed with (unable to get local issuer certificate)","sub":"CN=a248.e.akamai.net,O=Akamai Technologies Inc.,L=Cambridge,ST=MA,C=US","src":"72.179.140.146","dst":"64.86.135.143","p":443,"peer_descr":"worker-1","actions":["Notice::ACTION_LOG"],"suppress_for":3600.0,"dropped":false}