Reading JSON into table format

PowerShell Help
1

Hi All,

I am trying to read the following JSON and put it into a table.

I find it very easy to get the top level values such as id, siteId etc but face issues when trying to expand out the system.object value types such as clientIPs, I also have difficulties when I hit a nested object such as actions which contains fields that I need to split out into a column such as query string, postdata, requestresult, url etc.

Can someone point me in the right direction please?

[
  {
       "id":  "4230001700439320676",
       "siteId":  41373146,
       "startTime":  1657605663831,
       "endTime":  0,
       "clientIPs":  [
                         "103.117.104.40"
                     ],
       "country":  [
                       "Australia"
                   ],
       "countryCode":  [
                           "AU"
                       ],
       "clientType":  "Browser",
       "clientApplication":  "Vivaldi",
       "clientApplicationId":  835,
       "httpVersion":  "2.0",
       "clientApplicationVersion":  "null",
       "userAgent":  "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.137 Safari/537.36 Vivaldi/2.7.1628.33",
       "os":  "",
       "osVersion":  "",
       "supportsCookies":  true,
       "supportsJavaScript":  false,
       "hits":  44,
       "pageViews":  2,
       "entryReferer":  "https://eastwest.abp.co.in/Online/",
       "entryPage":  "POST eastwest.abp.co.in/nysondurst-you-couldiers-With-their-Race-not-the",
       "servedVia":  [
                         "Sydney, Australia"
                     ],
       "securitySummary":  {
                               "api.threats.illegal_resource_access":  2
                           },
       "actions":  [
                       "@{queryString=?type=js3\u0026sn=v_4_srv_5_sn_B360A6223031FB0C8F99424B3224AD06_perc_100000_ol_0_mul_1_app-3Aef6b7649bf84cb11_1_app-3Acd4697ba1bc4a478_1\u0026svrid=5\u0026flavor=post\u0026vi=UNAQDRMGPNKEIJNUPWRCURPHBJHRBPFT-0\u0026modifiedSince=1657579747952\u0026rf=https%3a%2f%2feastwest.abp.co.in%2feastwest%2flogin%3fReturnUrl%3d%252feastwest%252f%23%21\u0026bp=3\u0026app=ef6b7649bf84cb11\u0026crc=1861402075\u0026en=o0bm09bh\u0026end=1; postData=JTI0YSUzZDElN2M1JTdjTG9naW4lN2NDJTdjeCU3YzE2NTc2MDYzOTQzODUlN2MwJTdjZG4lN2MtMSU3Y3h1JTdjJTJmTm9ydGhPbmxpbmUlMmZsb2dpbiUzZlJldHVyblVybCUzZCUyNTJmTm9ydGhPbmxpbmUlMjUyZiU3Y3N2dHJnJTdjMSU3Y3N2bSU3Y2kxJTVlc2swJTVlc2gwJTdjdHZ0cmclN2MxJTdjdHZtJTdjaTElNWVzazAlNWVzaDAlN2N4Y3MlN2MwMDAwJTdjeGNlJTdjMDAwMCUyYzIlN2M5JTdjX2wMDBLMUkxMk03MDIxNzgzNzVWMCU3Y2h0dHBzJTNhJTJmJTJmbm9ydGhvbmxpbmUuYW1wLmNvbS5hdSUyZk5vcnRoT25saW5lJTJmU2NyaXB0UmVzb3VyY2UuYXhkJTdjYjAwmUlMmY=; requestResult=api.request_result.req_blocked_security; isSecured=True; url=eastwest.abp.co.in/Online/rb_bf96747ztk; responseTime=28; thinkTime=28; incidentId=423000170043960676-104404813837960267; threats=System.Object[]}",
                       "@{queryString=?type=js3\u0026sn=v_4_srv_5_sn_B360A6223031FB0C8F99424B3224AD06_perc_100000_ol_0_mul_1_app-3Aef6b7649bf84cb11_1_app-3Acd4697ba1bc4a478_1\u0026svrid=5\u0026flavor=post\u0026vi=UNAQDRMGPNKEIJNUPWRCURPHBJHRBPFT-0\u0026modifiedSince=1657579747952\u0026rf=https%3a%2f%2feastwest.abp.co.in%2feastwest%2f%23%21\u0026bp=3\u0026app=ef6b7649bf84cb11\u0026crc=770089898\u0026en=o0bm09bh\u0026end=1; postData=JTI0dHZuJTNkJTJmTm9ydGhPbmxpbmUlMmYlMjR0dnQlM2QwMDAwMDAwMDA1NTc4JTI0dHZtJTNkaTElM2JrMCUzYmgwJTI0dHZ0cmclM2QxJTI0bmklM2Q0ZyU3YzEwJTI0cnQlM2QxLTE2NTc2MDYzOTU1NzglM2JodHRwcyUzYSUyZiUyZm5vcnRob25saW5lLmFtcC5jb20uYXUlMmZueXNvbmR1cnN0LXlvdS1jb3VsZGllcnMtV2l0aC10aGVpci1SYWNlLW5vdC10aGUlN2NiMDAwZTBmMGcwaDBpMGsxMWwxMm0xNHY0ODY4N3cxNTI0MDFJMTIlN2NodHRwcyUzYSUyZiUyZm5vcnRob25saW5lLmFtcC5jb20uYXUlMmZOb3J0aE9ubGluZSUyZkpTY3JpcHRfMDAwMEZpbGVzJTJmYnVuZGxlJTNmdiUzZDZ1TXI0VFdwTDh5UW5KbHRzRlZPQURlaG8tbmNXRkpTWHo2Z3VxLTltZ2sxJTdjYjAwMDBlMGYwZzBoMGkwazBsMG0wdjMwMDkwOXcxMTA5NTIySzFJMTJNLTEyMjE0MDM3VjAlN2NodHRwcyUzYSUyZiUyZm5vcnRob25saW5lLmFtcC5jb20uYXUlMmZOb3J0aE9ubGluZSUyZkpTY3JpcHRfMDAwMEZpbGVzJTJmU2lsdmVybGlnaHQlMmZidW5kbGUlM2Z2JTNkSHVnMk9Jci1ZU25pbmQ5Qm1RQVYzY181RlV6b3BOWSDA5Nkk5TTEzNTcwMTM1MDFWMFcxJTdjaHR0cHMlM2ElMmYlMmZmb250cy5nc3RhdGljLmNvbSUyZnMlMmZvcGVuc2FucyU=; requestResult=api.request_result.req_blocked_security; isSecured=True; url=eastwest.abp.co.in/Online/rb_bf96747ztk; responseTime=25; thinkTime=25; incidentId=423000170043960676-263481554849170510; threats=System.Object[]}"
                   ]
   },
   {
       "id":  "9197000480389184860",
       "siteId":  41373246,
       "startTime":  1657599202832,
       "endTime":  1657599803000,
       "clientIPs":  [
                         "158.255.7.209"
                     ],
       "country":  [
                       "China"
                   ],
       "countryCode":  [
                           "CN"
                       ],
       "clientType":  "Hacking Tool",
       "clientApplication":  "libwww-perl",
       "clientApplicationId":  41,
       "httpVersion":  "1.1",
       "clientApplicationVersion":  "null",
       "userAgent":  "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML",
       "os":  "",
       "osVersion":  "",
       "supportsCookies":  false,
       "supportsJavaScript":  false,
       "hits":  42,
       "pageViews":  0,
       "entryReferer":  "",
       "entryPage":  "GET eastwest.abp.co.in/",
       "servedVia":  [
                         "Frankfurt, Germany"
                     ],
       "securitySummary":  {
                               "api.threats.illegal_resource_access":  15,
                               "api.acl.blacklisted_ips":  1
                           },
       "actions":  [
                       "@{postData=; requestResult=api.request_result.req_blocked_acl; isSecured=True; url=eastwest.abp.co.in/; responseTime=0; thinkTime=0; incidentId=9197000480389184860-1134634664039615500}",
                       "@{postData=; requestResult=api.request_result.req_blocked_acl; isSecured=True; url=eastwest.abp.co.in/; responseTime=0; thinkTime=0; incidentId=9197000480389184860-1599387122315103246}",
                       "@{postData=; requestResult=api.request_result.req_blocked_acl; isSecured=True; url=eastwest.abp.co.in/; responseTime=0; thinkTime=0; incidentId=9197000480389184860-877820599268609035}"
                       ]
   }
  ]

here’s the code i’m using to retrieve and try to make sense of the data!

Clear-Host

$EpochStart = Get-Date 1970-01-01T00:00:00

$sub_url = "https://my.imperva.com/api/prov/v1/accounts/listSubAccounts?"

$subs = Invoke-RestMethod -Method POST -Uri $sub_url -TimeoutSec 0 -Headers @{ 'x-API-Key' = 'N0tyr32lk3y3'

'x-API-Id' = '11112' }

#$subaccounts = $subs.resultList.sub_account_id

$date = get-date -Format dd-MM-yyyy

$xlSourcefile = "$env:TEMP\Imperva-Report-$date.xlsx"

Remove-Item -Path $xlSourcefile -force

$site_id_str = "https://my.imperva.com/api/prov/v1/sites/list?"

$site_id_one = Invoke-RestMethod -Method POST -Uri $site_id_str -TimeoutSec 0 -Headers @{ 'x-API-Key' = 'N0tyr32lk3y3'

'x-API-Id' = '11112' }

#build site_ID -> Site DNS Name Mapping

$sitedict = @{}

foreach($site in $site_id_one.sites)

{

$sitedict[$site.site_id] = $site.dns.dns_record_name.Substring(0, [system.Math]::Min(28,$site.dns.dns_record_name.Length))

}

foreach($item in $site_id_one.sites.site_id)

{

$siteurl = "https://my.imperva.com/api/visits/v1?site_id=$item&time_range=last_30_days"

write-host "sitename is " $sitedict.$item.Substring(0, [system.Math]::Min(28,$sitedict.$item.Length)) "($item)"

write-host "======================================================"

$site_details = Invoke-RestMethod -Method POST -Uri $siteurl -TimeoutSec 0 -Headers @{ 'x-API-Key' = 'N0tyr32lk3y3'

'x-API-Id' = '11112' }


foreach($answer in $site_details.visits){

$answer.psobject.Properties

}

}
2

jwh,
Welcome to the forum. :wave:t4:

Before we proceed … could you please go back, edit your question and fix the formatting of your code or sample data? And please share the code you have so far.

BTW: When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

… and it would be nice if you reduced the sample data by deleting big data fields not necessary for the understanding of your issue. Thanks.

3

Thanks Olaf, I have updates my post

4

When you’re saying table you’re probably talking about a structured data format like CSV. JSON data on the other hand are hierarchical data by default. So in easy cases may it be possible to transform JSON input data to structured output while in other cases may it be quite hard or nearly impossible. :man_shrugging:t4:

It may help you understanding how the JSON actually works

Data enclosed in square brackets are arrays/lists. PowerShell usually outputs those to the console in curly braces. So when you hit an array in a JSON data field you will have to expand it with a loop. :wink:
(I shortened your JSON data a little bit for demonstration purposes)

$JSONInputData = @'
[
  {
       "id":  "4230001700439320676",
       "siteId":  41373146,
       "clientIPs":  [
                         "103.117.104.40"
                     ],
       "actions":  [
                       "Action1",
                       "Action2"
                   ]
   },
   {
       "id":  "9197000480389184860",
       "siteId":  41373246,
       "clientIPs":  [
                         "158.255.7.209"
                     ],
       "actions":  [
                    "Action1",
                    "Action2",
                    "Action3"
                    ]
   }
  ]
'@ |
ConvertFrom-Json

$Output = @{
    ID       = ''
    siteId   = ''
    ClientIp = ''
}

foreach ($TopElement in $JSONInputData) {
    $Output.ID = $TopElement.id
    $Output.siteId = $TopElement.siteId
    foreach ($ClientIp in $TopElement.clientIPs) {
        $Output.ClientIp = $ClientIp
        [PSCustomObject]$Output
    }
}

and the output would be this:

  siteId ClientIp       ID
  ------ --------       --
41373146 103.117.104.40 4230001700439320676
41373246 158.255.7.209  9197000480389184860

… or, when you have more than one element in an array it’ll look like this:

$Output = @{
    ID     = ''
    siteId = ''
    Action = ''
}

foreach ($TopElement in $JSONInputData) {
    $Output.ID = $TopElement.id
    $Output.siteId = $TopElement.siteId
    foreach ($Action in $TopElement.actions) {
        $Output.Action = $Action
        [PSCustomObject]$Output
    }
}

… and the output would look like this:

Action    siteId ID
------    ------ --
Action1 41373146 4230001700439320676
Action2 41373146 4230001700439320676
Action1 41373246 9197000480389184860
Action2 41373246 9197000480389184860
Action3 41373246 9197000480389184860

The problem with hierarchical data is - when you have more than one list next to each other - how to turn those data into structured data as the data in different branches are actually unrelated to each other.

1 Like