Counting JSON fields inside array of logs

Follow

Mark Drummond

• April 11, 2018 14:38

The sendgrid email service sends its logs in a JSON array:

[
  {
    // Log 1
    event: "event1"
  },
  {
    // log 2
    event: "event2"
  },
  ...
]

I want to extract and count the event types, but my initial attempts result in something like this:

I don't want the event types separated by log index. I want to "collapse" them into a single metric.

0

• 

  Piotr Woch

  • April 13, 2018 13:52

  Hi Mark! Did you try parsing your data not as JSON, but as plain text and just parse out like this:
  
  parse regex (<event_type>event: \".*\") multi

  Please let me know if that helped get you the results you were expecting. 

  0

  Comment actions Permalink
• 

  Mark Drummond

  • April 14, 2018 14:23

  This seems to have put me on the right path. I am able to extract just the event type text with the following:

  _sourceCategory=sendgrid/notify
  | parse regex "\"event\":\"(?<event_type>.*?)\"" multi

  But multi is not working for me. It only grabs the event field from one log where there are "multiple logs in a log", and, kinda odd, which one it grabs seems to be random. Sample log message below. Using this as an example, in some cases the above regex grabs "processed" and in some cases "delivered".

  [
      {
          "email": "a@foo.com",
          "timestamp": 1523709521,
          "smtp-id": "<A@B.sendgrid.net>",
          "sg_event_id": "STRING",
          "sg_message_id": "STRING",
          "event": "processed"
      },
      {
          "ip": "1.2.3.4",
          "response": "250 2.6.0 <A@B.sendgrid.net> [InternalId=INT, Hostname=foo.com] 14998 bytes in 0.180, 81.337 KB/sec Queued mail for delivery",
          "sg_event_id": "STRING",
          "sg_message_id": "STRING",
          "tls": 1,
          "event": "delivered",
          "email": "b@foo.com",
          "timestamp": 1523709523,
          "smtp-id": "<A@B.sendgrid.net>"
      }
  ]

   

  0

  Comment actions Permalink
• 

  Mark Drummond

  • April 14, 2018 16:27

  Small change:

  _sourceCategory=sendgrid/notify
  | parse regex "\"event\":\"(?<event_type>\w*)\"" multi

  Using '\w*' allows me to drop the trailing '?' and the regex still works as before, but multi still not working for me. First two results below have single sendgrid log messages, the second two have two each, but only one 'event_type' value is being pulled:

  

  0

  Comment actions Permalink
• 

  Mark Drummond

  • April 14, 2018 16:32

  Aside: I've been testing my regex's on regex101.com and they are working as expected there.

  0

  Comment actions Permalink
• 

  Latimer Luis

  • April 16, 2018 20:35

  Hi Mark - 
  
  I tested this myself on Regex101 and I had to explicitly define the whitespace (\s) after the colon following "event".   Perhaps what you're seeing is actually something that was extracted in a field extraction rule and not by this parse statement? 

  | parse regex "\"event\":\s\"(?<event_type>\w*)\"" multi

   

  0

  Comment actions Permalink

Please sign in to leave a comment.