JQ: Another Tool We Thought We Knew
So often you'll see folks (me included) use "jq" to take an unformatted JSON mess and turn it into a readable output. For instance, last thursday we used the Shodan API to dump about 650k of host info like this:
curl -s -k "https://api.shodan.io/shodan/host/%1?key=%shodan-api-key%" | jq
In other words, up to today, I've just used jq as a JSON "prettifier".
At some point (OK, TIL), I finally clued into the fact that that the "q" in "jq" stood for "query"
First, let's simplify things first by making a file that we can play with (see Thursday's diary - https://isc.sans.edu/diary/Shodans+API+For+The+Recon+Win/30050/ - for details on the API call below):
curl -s -k "https://api.shodan.io/shodan/host/45.60.31.34?key=%shodan-api-key%" > isc.txt
Let's use jq to query / extract the "ports" array in the file:
type isc.txt | jq ".ports"
[
1024,
8200,
25,
8112,
2082,
2083,
2087,
554,
14344,
53,
12345,
60001,
9800,
587,
80,
5201,
.. and so on (123 open ports)
printing these without the carriage returns gets it all on one page, sometimes that's important:
type isc.txt | jq ".ports" --compact-output
[1024,8200,25,8112,2082,2083,2087,554,14344,53,12345,60001,9800,587,80,5201,82,83,14265,9306,8800,7777,7779,31337,631,8834,16010,5269,1177,5800,2222,8880,8888,8889,3268,3269,10443,3790,9080,1234,10134,3299,4848,9001,8443,13579,5900,5901,9998,9999,10000,10001,7443,9000,2345,9002,6443,4911,9009,7474,1337,9530,3389,8001,8009,8010,50000,9443,7001,4443,4444,5985,5986,5007,5009,6000,6001,1400,8060,9600,9090,9091,389,9095,5000,5001,9100,5005,5006,1935,8081,5010,8083,4500,8085,8086,8089,8090,7071,4000,8098,25001,2480,4022,5560,3001,8123,444,8126,6080,4040,8139,465,4567,4064,9191,3050,9200,1521,8181,443]
Let's extract both the subdomains and hostnames? (often these are the same):
type isc.txt | jq ".domains,.hostnames"
[
"cio.org",
"ranges.io",
"cyberaces.org",
"sans.co",
"imperva.com",
"cyberfoundations.org",
"securingthehuman.org",
"sans.org",
"giac.net",
"sans.edu",
"giac.org",
"cybercenters.org"
]
[
"cio.org",
"ranges.io",
"cyberaces.org",
"sans.co",
"giac.net",
"imperva.com",
"cyberfoundations.org",
"qms.sans.org",
"content.sans.org",
"sans.org",
"sso.securingthehuman.org",
"isc.sans.edu",
"sans.edu",
"giac.org",
"cybercenters.org"
]
At some point, you'll find that the IP addresses returned by shodan are typically in decimal. No problem, convert decimal value to hex, then convert each octet back to digital and stuff the dots in! Or you can just ask for both the ip and ip_str values:
type isc.txt | jq ".ip,.ip_str"
758914850,
"45.60.31.34"
How about just dumping out the keys that you can mess with?
type isc.txt | jq "keys"
[
"area_code",
"asn",
"city",
"country_code",
"country_name",
"data",
"domains",
"hostnames",
"ip",
"ip_str",
"isp",
"last_update",
"latitude",
"longitude",
"org",
"os",
"ports",
"region_code",
"tags"
]
There's way more to jq - you can execute scripts, add and delete keys, sort output or do math on the various values. From the "query" perspective, you can treat your JSON input very much like a SQL database - you can use statements like select, index and join, which should all look very familiar.
You can also write scripts for jq to execute. The scripts have all the scripty things you'd expect: if/then/else, try/catch boolean operators, regex support, text manipulation operators and so on.
If you have jq installed, typing "man jq" will give you several pages of possibilties, even "jq --help" will get you started. Googling "man jq" will give you the same if you don't have it installed yet.
For me, basic queries do the job most days (which is what was discussed above) - if I need more I tend to use other scripting solutions, most days bash, python or powershell. But (just like most of us do with AWK), I'm just scratching the surface of what jq can do.
If you've done something cool with jq, please share in our comment form!
===============
Rob VandenBrink
rob@coherentsecurity.com
Comments
jq -r 'group_by(.jsonPayload.connection.dest_ip, .jsonPayload.connection.dest_port, .jsonPayload.connection.protocol)[] | {"dest_ip": .[0].jsonPayload.connection.dest_ip, "dest_port": .[0].jsonPayload.connection.dest_port, "protocol": .[0].jsonPayload.connection.protocol, "count": length}' | jq -s '. | sort_by(-.count)'
Brandon Evans
Jul 24th 2023
1 year ago
AndrewB
Jul 25th 2023
1 year ago