topic How to create table from large JSON data? in Getting Data In

How to create table from large JSON data?

mmoermans — Mon, 27 Jan 2020 10:23:16 GMT

I've got a large JSON result from an API of the results from multiple domains

{"success": true, "message": "OK", "data": {"submission-date": "2020-01-25", "finished-date": "2020-01-125", "name": "{'source': 'api.com Dashboard', 'type': 'web', 'account': 'test, 'list': 'domain'} 54354543543", "identifier": "6546423432432", "api-version": "1.0", "domains": [{"domain": "domain1", "status": "ok", "score": 55, "link": "https://api.com/site/domain1/55/", "categories": [{"category": "ipv6", "passed": true}, {"category": "dnssec", "passed": true}, {"category": "tls", "passed": false}, {"category": "appsecpriv", "passed": false}], "views": [{"name": "web_appsecpriv_csp", "result": true}, {"name": "web_appsecpriv_referrer_policy", "result": true}, {"name": "web_appsecpriv_x_content_type_options", "result": false}, {"name": "web_appsecpriv_x_frame_options", "result": true}, {"name": "web_appsecpriv_x_xss_protection", "result": false}, {"name": "web_dnssec_exist", "result": false}, {"name": "web_dnssec_valid", "result": false}, {"name": "web_https_cert_chain", "result": true}, {"name": "web_https_cert_domain", "result": true}, {"name": "web_https_cert_pubkey", "result": true}, {"name": "web_https_cert_sig", "result": true}, {"name": "web_https_dane_exist", "result": false}, {"name": "web_https_dane_valid", "result": false}, {"name": "web_https_http_available", "result": true}, {"name": "web_https_http_compress", "result": true}, {"name": "web_https_http_hsts", "result": false}, {"name": "web_https_http_redirect", "result": true}, {"name": "web_https_tls_ciphers", "result": true}, {"name": "web_https_tls_clientreneg", "result": true}, {"name": "web_https_tls_compress", "result": true}, {"name": "web_https_tls_keyexchange", "result": true}, {"name": "web_https_tls_secreneg", "result": false}, {"name": "web_https_tls_version", "result": true}, {"name": "web_ipv6_ns_address", "result": true}, {"name": "web_ipv6_ns_reach", "result": true}, {"name": "web_ipv6_ws_address", "result": false}, {"name": "web_ipv6_ws_reach", "result": false}, {"name": "web_ipv6_ws_similar", "result": false}]}, {"domain": "domain2", "status": "ok", "score": 99, "link": "https://api.com/site/domain2/35/", "categories": [{"category": "ipv6", "passed": true}, {"category": "dnssec", "passed": true}, {"category": "tls", "passed": true}, {"category": "appsecpriv", "passed": false}], "views": [{"name": "web_appsecpriv_csp", "result": true}, {"name": "web_appsecpriv_referrer_policy", "result": false}, {"name": "web_appsecpriv_x_content_type_options", "result": true}, {"name": "web_appsecpriv_x_frame_options", "result": true}, {"name": "web_appsecpriv_x_xss_protection", "result": false}, {"name": "web_dnssec_exist", "result": true}, {"name": "web_dnssec_valid", "result": true}, {"name": "web_https_cert_chain", "result": true}, {"name": "web_https_cert_domain", "result": true}, {"name": "web_https_cert_pubkey", "result": true}, {"name": "web_https_cert_sig", "result": true}, {"name": "web_https_dane_exist", "result": false}, {"name": "web_https_dane_valid", "result": true}, {"name": "web_https_http_available", "result": true}, {"name": "web_https_http_compress", "result": true}, {"name": "web_https_http_hsts", "result": true}, {"name": "web_https_http_redirect", "result": true}, {"name": "web_https_tls_ciphers", "result": true}, {"name": "web_https_tls_clientreneg", "result": true}, {"name": "web_https_tls_compress", "result": true}, {"name": "web_https_tls_keyexchange", "result": true}, {"name": "web_https_tls_secreneg", "result": true}, {"name": "web_https_tls_version", "result": true}, {"name": "web_ipv6_ns_address", "result": true}, {"name": "web_ipv6_ns_reach", "result": true}, {"name": "web_ipv6_ws_address", "result": true}, {"name": "web_ipv6_ws_reach", "result": true}, {"name": "web_ipv6_ws_similar", "result": true}]}

I'm trying to get an overview of results per domain but I'm having difficulty getting the categories/views/results sorted per domain (domain1, domain2).

So far I've tried spath and mvzip but I haven't found a way to combine the domains properly:

index=json | spath | rename data.domains{}.domain as domain | rename data.domains{}.categories{}.category AS category, data.domains{}.categories{}.passed AS passed, data.domains{}.score AS score, data.domains{}.views{}.name AS viewname, data.domains{}.views{}.result AS viewresult | mvexpand domain | table domain, score, category, passed, viewname, viewresult

Does anyone have a good method for this?

Re: How to create table from large JSON data?

to4kawa — Mon, 27 Jan 2020 11:28:11 GMT

| makeresults 
| eval _raw="{\"success\": true, \"message\": \"OK\", \"data\": {\"submission-date\": \"2020-01-25\", \"finished-date\": \"2020-01-125\", \"name\": \"{'source': 'api.com Dashboard', 'type': 'web', 'account': 'test, 'list': 'domain'} 54354543543\", \"identifier\": \"6546423432432\", \"api-version\": \"1.0\", \"domains\": [{\"domain\": \"domain1\", \"status\": \"ok\", \"score\": 55, \"link\": \"https://api.com/site/domain1/55/\", \"categories\": [{\"category\": \"ipv6\", \"passed\": true}, {\"category\": \"dnssec\", \"passed\": true}, {\"category\": \"tls\", \"passed\": false}, {\"category\": \"appsecpriv\", \"passed\": false}], \"views\": [{\"name\": \"web_appsecpriv_csp\", \"result\": true}, {\"name\": \"web_appsecpriv_referrer_policy\", \"result\": true}, {\"name\": \"web_appsecpriv_x_content_type_options\", \"result\": false}, {\"name\": \"web_appsecpriv_x_frame_options\", \"result\": true}, {\"name\": \"web_appsecpriv_x_xss_protection\", \"result\": false}, {\"name\": \"web_dnssec_exist\", \"result\": false}, {\"name\": \"web_dnssec_valid\", \"result\": false}, {\"name\": \"web_https_cert_chain\", \"result\": true}, {\"name\": \"web_https_cert_domain\", \"result\": true}, {\"name\": \"web_https_cert_pubkey\", \"result\": true}, {\"name\": \"web_https_cert_sig\", \"result\": true}, {\"name\": \"web_https_dane_exist\", \"result\": false}, {\"name\": \"web_https_dane_valid\", \"result\": false}, {\"name\": \"web_https_http_available\", \"result\": true}, {\"name\": \"web_https_http_compress\", \"result\": true}, {\"name\": \"web_https_http_hsts\", \"result\": false}, {\"name\": \"web_https_http_redirect\", \"result\": true}, {\"name\": \"web_https_tls_ciphers\", \"result\": true}, {\"name\": \"web_https_tls_clientreneg\", \"result\": true}, {\"name\": \"web_https_tls_compress\", \"result\": true}, {\"name\": \"web_https_tls_keyexchange\", \"result\": true}, {\"name\": \"web_https_tls_secreneg\", \"result\": false}, {\"name\": \"web_https_tls_version\", \"result\": true}, {\"name\": \"web_ipv6_ns_address\", \"result\": true}, {\"name\": \"web_ipv6_ns_reach\", \"result\": true}, {\"name\": \"web_ipv6_ws_address\", \"result\": false}, {\"name\": \"web_ipv6_ws_reach\", \"result\": false}, {\"name\": \"web_ipv6_ws_similar\", \"result\": false}]}, {\"domain\": \"domain2\", \"status\": \"ok\", \"score\": 99, \"link\": \"https://api.com/site/domain2/35/\", \"categories\": [{\"category\": \"ipv6\", \"passed\": true}, {\"category\": \"dnssec\", \"passed\": true}, {\"category\": \"tls\", \"passed\": true}, {\"category\": \"appsecpriv\", \"passed\": false}], \"views\": [{\"name\": \"web_appsecpriv_csp\", \"result\": true}, {\"name\": \"web_appsecpriv_referrer_policy\", \"result\": false}, {\"name\": \"web_appsecpriv_x_content_type_options\", \"result\": true}, {\"name\": \"web_appsecpriv_x_frame_options\", \"result\": true}, {\"name\": \"web_appsecpriv_x_xss_protection\", \"result\": false}, {\"name\": \"web_dnssec_exist\", \"result\": true}, {\"name\": \"web_dnssec_valid\", \"result\": true}, {\"name\": \"web_https_cert_chain\", \"result\": true}, {\"name\": \"web_https_cert_domain\", \"result\": true}, {\"name\": \"web_https_cert_pubkey\", \"result\": true}, {\"name\": \"web_https_cert_sig\", \"result\": true}, {\"name\": \"web_https_dane_exist\", \"result\": false}, {\"name\": \"web_https_dane_valid\", \"result\": true}, {\"name\": \"web_https_http_available\", \"result\": true}, {\"name\": \"web_https_http_compress\", \"result\": true}, {\"name\": \"web_https_http_hsts\", \"result\": true}, {\"name\": \"web_https_http_redirect\", \"result\": true}, {\"name\": \"web_https_tls_ciphers\", \"result\": true}, {\"name\": \"web_https_tls_clientreneg\", \"result\": true}, {\"name\": \"web_https_tls_compress\", \"result\": true}, {\"name\": \"web_https_tls_keyexchange\", \"result\": true}, {\"name\": \"web_https_tls_secreneg\", \"result\": true}, {\"name\": \"web_https_tls_version\", \"result\": true}, {\"name\": \"web_ipv6_ns_address\", \"result\": true}, {\"name\": \"web_ipv6_ns_reach\", \"result\": true}, {\"name\": \"web_ipv6_ws_address\", \"result\": true}, {\"name\": \"web_ipv6_ws_reach\", \"result\": true}, {\"name\": \"web_ipv6_ws_similar\", \"result\": true}]}"
| spath path=data.domains{} output=domains
| stats count by domains
| spath input=domains path=views{} output=views
| spath input=domains path=categories{} output=categories
| spath input=domains path=domain
| spath input=domains path=score
| fields domain score categories views
| mvexpand categories
| stats count by views domain score categories
| spath input=categories
| eval viewname=spath(views,"name") , viewresult=spath(views,"result") 
| table domain, score, category, passed, viewname, viewresult

Hi, I've done.

Re: How to create table from large JSON data?

kamlesh_vaghela — Mon, 27 Jan 2020 12:06:02 GMT

@mmoermans

I'm not sure how you are relating categories{} and views{} with single domain, as they are in same level with different number of values, but I'm able to get categories{} and views{} differently. Can you please try below searches?

index=json
| spath path=data.domains{} output=data_domains | mvexpand data_domains
| fields _time data_domains 
| eval _raw=data_domains | extract | rename categories{}.* AS * 
| eval tmp = mvzip(category,passed)
| mvexpand tmp
| eval category = mvindex(split(tmp,","),0),passed = mvindex(split(tmp,","),1)
| table _time domain score category passed

AND

index=json
| spath path=data.domains{} output=data_domains | mvexpand data_domains
| fields _time data_domains 
| eval _raw=data_domains | extract | rename views{}.* as * 
| eval tmp = mvzip(name,result)
| mvexpand tmp
| eval name = mvindex(split(tmp,","),0),result = mvindex(split(tmp,","),1)
| table _time domain score name result

Sharing Sample Search for View & result:

| makeresults 
| eval _raw="{\"success\": true,\"message\": \"OK\",\"data\": {\"submission-date\": \"2020-01-25\",\"finished-date\": \"2020-01-125\",\"name\": \"{'source': 'api.com Dashboard', 'type': 'web', 'account': 'test, 'list': 'domain'} 54354543543\",\"identifier\": \"6546423432432\",\"api-version\": \"1.0\",\"domains\": [{\"domain\": \"domain1\",\"status\": \"ok\",\"score\": 55,\"link\": \"https://api.com/site/domain1/55/\",\"categories\": [{\"category\": \"ipv6\",\"passed\": true}, {\"category\": \"dnssec\",\"passed\": true}, {\"category\": \"tls\",\"passed\": false}, {\"category\": \"appsecpriv\",\"passed\": false}],\"views\": [{\"name\": \"web_appsecpriv_csp\",\"result\": true}, {\"name\": \"web_appsecpriv_referrer_policy\",\"result\": true}, {\"name\": \"web_appsecpriv_x_content_type_options\",\"result\": false}, {\"name\": \"web_appsecpriv_x_frame_options\",\"result\": true}, {\"name\": \"web_appsecpriv_x_xss_protection\",\"result\": false}, {\"name\": \"web_dnssec_exist\",\"result\": false}, {\"name\": \"web_dnssec_valid\",\"result\": false}, {\"name\": \"web_https_cert_chain\",\"result\": true}, {\"name\": \"web_https_cert_domain\",\"result\": true}, {\"name\": \"web_https_cert_pubkey\",\"result\": true}, {\"name\": \"web_https_cert_sig\",\"result\": true}, {\"name\": \"web_https_dane_exist\",\"result\": false}, {\"name\": \"web_https_dane_valid\",\"result\": false}, {\"name\": \"web_https_http_available\",\"result\": true}, {\"name\": \"web_https_http_compress\",\"result\": true}, {\"name\": \"web_https_http_hsts\",\"result\": false}, {\"name\": \"web_https_http_redirect\",\"result\": true}, {\"name\": \"web_https_tls_ciphers\",\"result\": true}, {\"name\": \"web_https_tls_clientreneg\",\"result\": true}, {\"name\": \"web_https_tls_compress\",\"result\": true}, {\"name\": \"web_https_tls_keyexchange\",\"result\": true}, {\"name\": \"web_https_tls_secreneg\",\"result\": false}, {\"name\": \"web_https_tls_version\",\"result\": true}, {\"name\": \"web_ipv6_ns_address\",\"result\": true}, {\"name\": \"web_ipv6_ns_reach\",\"result\": true}, {\"name\": \"web_ipv6_ws_address\",\"result\": false}, {\"name\": \"web_ipv6_ws_reach\",\"result\": false}, {\"name\": \"web_ipv6_ws_similar\",\"result\": false}]}, {\"domain\": \"domain2\",\"status\": \"ok\",\"score\": 99,\"link\": \"https://api.com/site/domain2/35/\",\"categories\": 
    [ {\"category\": \"ipv6\",\"passed\": true}, {\"category\": \"dnssec\",\"passed\": true}, {\"category\": \"tls\",\"passed\": true}, {\"category\": \"appsecpriv\",\"passed\": false}],\"views\": 
    [ {\"name\": \"web_appsecpriv_csp\",\"result\": true}, {\"name\": \"web_appsecpriv_referrer_policy\",\"result\": false}, {\"name\": \"web_appsecpriv_x_content_type_options\",\"result\": true}, {\"name\": \"web_appsecpriv_x_frame_options\",\"result\": true}, {\"name\": \"web_appsecpriv_x_xss_protection\",\"result\": false}, {\"name\": \"web_dnssec_exist\",\"result\": true}, {\"name\": \"web_dnssec_valid\",\"result\": true}, {\"name\": \"web_https_cert_chain\",\"result\": true}, {\"name\": \"web_https_cert_domain\",\"result\": true}, {\"name\": \"web_https_cert_pubkey\",\"result\": true}, {\"name\": \"web_https_cert_sig\",\"result\": true}, {\"name\": \"web_https_dane_exist\",\"result\": false}, {\"name\": \"web_https_dane_valid\",\"result\": true}, {\"name\": \"web_https_http_available\",\"result\": true}, {\"name\": \"web_https_http_compress\",\"result\": true}, {\"name\": \"web_https_http_hsts\",\"result\": true}, {\"name\": \"web_https_http_redirect\",\"result\": true}, {\"name\": \"web_https_tls_ciphers\",\"result\": true}, {\"name\": \"web_https_tls_clientreneg\",\"result\": true}, {\"name\": \"web_https_tls_compress\",\"result\": true}, {\"name\": \"web_https_tls_keyexchange\",\"result\": true}, {\"name\": \"web_https_tls_secreneg\",\"result\": true}, {\"name\": \"web_https_tls_version\",\"result\": true}, {\"name\": \"web_ipv6_ns_address\",\"result\": true}, {\"name\": \"web_ipv6_ns_reach\",\"result\": true}, {\"name\": \"web_ipv6_ws_address\",\"result\": true}, {\"name\": \"web_ipv6_ws_reach\",\"result\": true}, {\"name\": \"web_ipv6_ws_similar\",\"result\": true}]}]}}" 
| extract 
| spath path=data.domains{} output=data_domains | mvexpand data_domains
| fields _time data_domains 
| eval _raw=data_domains | extract | rename categories{}.* AS *, views{}.* as * 
| eval tmp = mvzip(name,result)
| mvexpand tmp
| eval name = mvindex(split(tmp,","),0),result = mvindex(split(tmp,","),1)
| table _time domain score name result