Iterate through JSON files and obtain specific data

Question

I'm trying to filter through some sign in log data in JSON format within files.

I've got a directory containing the files here:

Inside these files, is data looking like this:

What I need to do is go through each file and get specific data, for example.. Return me all data where location is not AU.

Now what I've got technically works, which is:

Get-ChildItem -Path 'c:\temp\sign_in_logs' -Filter *.json | ForEach-Object {
    Get-Content $_.FullName | ConvertFrom-Json | Where-Object {$_.Location -ne 'AU'}}

But my problem is it prints the entire content of the JSON file where the data I'm looking for is.

My question is, how can I get just the single entry/entries back, which in the case of my example would be just this (get me data where location is not AU):

    {
    "id":  "bedf8559-403f-4aa7-b3da-4d5091da8400",
    "createdDateTime":  "2022-03-23T00:44:27Z",
    "userPrincipalName":  "user@domainone",
    "Location":  "CA",
    "ipAddress":  "2.2.2.2",
    "appDisplayName":  "Azure Portal",
    "conditionalAccessStatus":  "success",
    "Status":  "Other."
}

Do I need to do something with the data first before I can individually grab the items?

Answer 1

I'm guessing you're looking to either know how to export the filtered data to different JSON files, or to have the filtered objects in memory but in a way you can identify the source of data.

• If you want to export the filtered JSONs to separated files:

Get-ChildItem -Path 'c:\temp\sign_in_logs' -Filter *.json | ForEach-Object {
    $fileName = 'filtered_{0}.json' -f $_.BaseName
    Get-Content $_.FullName -Raw | ConvertFrom-Json |
        Where-Object { $_.Location -ne 'AU' } |
        ConvertTo-Json | Set-Content $fileName
}

• If you want to have the filtered data in memory:

$result = Get-ChildItem -Path 'c:\temp\sign_in_logs' -Filter *.json | ForEach-Object {
    $filtered = Get-Content $_.FullName -Raw | ConvertFrom-Json |
        Where-Object { $_.Location -ne 'AU' }

    [pscustomobject]@{
        SourceFile      = $_.Name
        FilteredObjects = $filtered
    }
}

Following above code, you can inspect the $result object by filtering techniques and dot notation, i.e.: if you want to see the filtered contents of one.json:

$result.where{ $_.SourceFile -eq 'one.json' }.FilteredObjects