Creating Arrays from JSON properties and arrays

InsightConnect
1

I have been wracking my brain trying to figure out how to include a could of JSON property that is outside of a nested JSON array that contains information that I need to include in a MS Teams response.

What I am doing:
I am using the Cisco Umbrella plugin to search Umbrella activity for users who may have visited a site/domain. Cisco returns a JSON object that contains an array of objects (see sample JSON below). I want to output a table that includes the properties datetime, domain, externalIp, internalIP, verdict, USER, system name. Both User and system name are in an array called identities.

Currently, I am looping through the identity array and outputting an array that contains only the two elements of username, computer name.

Problem: I am not finding a way to create a custom array that would include the two elements from the identities array and the properties outside of the identity array.

The end goal is to have a message presented like this (the fields to not align up yet):
image

Structure returned by Umbrella: JSON array of objects that contain arrays.

Sample of returned data:

“visited_domains”{
“$success”: true,
“domain_visits”: [
{
“datetime”: “2024-11-21T16:09:03”,
“domain”: “<URL/DOMAIN VISITED>”,
“externalIp”: “1.2.3.4”,
“identities”: [
{
“deleted”: false,
“id”: 1235400214,
“label”: “USER”
},
{
“deleted”: false,
“id”: 1235413385,
“label”: “system.name.org”
},
{
“deleted”: false,
“id”: 604265489,
“label”: “User LOCATION”
},
],
“internalIp”: “5.6.7.8”,
“queryType”: “A”,
“timestamp”: 1732205343000,
“verdict”: “allowed”
}
]
}

2

Screenshot 2024-11-21 at 5.28.33 PM

I was able to get it working, but just wanted to warn you about the data size constraints. If you have a lot of results returned then it is going to fail on the Teams Message with this response: “code”:“1”,“message”:“MessageSizeExceeded-Message total size exceeded. Actual message total size: 285081 bytes, whereas max allowed size is 102400 bytes”

I attached a Snippet with the steps that I took to accomplish your goal. I used the Umbrella step to grab the visits for google.com.

I then used JQ to grab each of the items seen in the Teams Message Table.

That is output as a string which is not convenient, so I used Python to convert it to a more friendly format. You can try other methods, this was just the fastest for me.

Last is the Send HTML Teams message, using HTML to create the table, and handlebars to pull each item out of the python object output.

You can download the snippet and import into your environment for testing.

Umbrella Results to Microsoft Teams.snpt (7.4 KB)

1 Like
3

Holy cow, that is a lot more than I was expecting! I will check out the snippet shortly. I started to mess around with JQ last night, but I was not able to get it working. I was thinking of sending the JSON to a python script as well, but I am still learning Python (primarily write in PowerShell).

Thanks for the heads up on the Teams message limit.

4

Our internal Moose @Eric-Wilson shared an awesome site with me where you can test out your JQ.

https://www.devtoolsdaily.com/jq_playground/

It makes it much better for testing.

1 Like
5

Just want to add that if you wrap the JQ satement in a JSON object then you can use the Type Converter plugin to go from String to Object without having to use python. Example below.

{"output":
.domain_visits | map({
  domain,
  verdict,
  datetime,
  externalIp,
  internalIp,
  USER: (.identities[]? | .label // "N/A")
})
}
6

Thanks! I was just about to ask you about JQ. Looks like you just dot source the array and pipe it into a map command. Looks simple, wondering how I was struggling with it. lol. For the user part, where you have

USER: (.identities? | .label // “N/A”)

is that regex or some function of JQ. Sorry for the basic questions. I am only on month 5 of using this platform and trying to learn everything I can. It is great that we can share these bits and use them, but I also like to make sure I understand what is happening, sort of learning how to fish.