Read JSON Content From Standard Input (stdin)
Problem
You need to read and work with JSON data that’s streaming from stdout.
Solution
Treat JSON content piped to stdin as database tables.
Discussion
This works in the same way reading CSV files from stdin works, so we won’t spend a great deal of time on this subject.
I will assert that, while we could toss the venerable jq (and similar tools) to the wind with DuckDB around, do not feel bad about using it to make your DuckDB query-life easier. Here’s an example of that which I mentioned in the previous chapter:
curl --silent 'https://viz.greynoise.io/api/greynoise/v2/meta/metadata' |
jq .metadata |
duckdb -markdown -c "
FROM read_json_auto('/dev/stdin', format='array', records=true, sample_size=-1)
SELECT
created_at,
name,
category,
intention
ORDER BY 1 DESC
LIMIT 20
"| created_at | name | category | intention |
|---|---|---|---|
| 2024-03-22 | Joomla facileforms Arbitrary File Upload Attempt | activity | malicious |
| 2024-03-22 | Joomla JBcatalog Arbitrary File Upload Attempt | activity | malicious |
| 2024-03-22 | Joomla JBcatalog Arbitrary File Upload Vuln Check | activity | unknown |
| 2024-03-21 | Well-Known Scanner | activity | unknown |
| 2024-03-21 | Dell SonicWALL Scrutinizer methodDetail SQL Injection Attempt | activity | malicious |
| 2024-03-21 | Fortra FileCatalyst Workflow Web Portal Scanner | activity | unknown |
| 2024-03-21 | Schuhfried Testing Platform CVE-2023-38995 Info Leak Attempt | activity | malicious |
| 2024-03-21 | Web.xml Access Attempt | activity | unknown |
| 2024-03-21 | Joomla Com_Fabrik Arbitrary File Upload Attempt | activity | malicious |
| 2024-03-19 | osCommerce 2.2 RCE Attempt | activity | malicious |
| 2024-03-15 | WP-22.php Malware Scanner | activity | malicious |
| 2024-03-15 | Hikvision IP Cameras CVE-2013-4975 Privilege Escalation Check | activity | unknown |
| 2024-03-15 | Terramaster TOS RCE Attempt | activity | malicious |
| 2024-03-14 | Hikvision Intercom Broadcasting System CVE-2023-6895 Command Injection Attempt | activity | malicious |
| 2024-03-14 | ECOA BAS Controller CVE-2021-41293 Path Traversal Attempt | activity | malicious |
| 2024-03-14 | Weaver E-Cology E-mobile SQL Injection Attempt | activity | malicious |
| 2024-03-13 | FortiNet FortiClientEMS CVE-2023-48788 SQL Injection Attempt | activity | malicious |
| 2024-03-13 | Oracle BI Publisher CVE-2019-2588 Directory Traversal Attempt | activity | malicious |
| 2024-03-12 | Cisco ASA CVE-2020-3259 Information Disclosure Attempt | activity | malicious |
| 2024-03-12 | Ecology OA validate.jsp SQL Injection Attempt | activity | malicious |