Exporting my Array Properly to CSV

Mike 246 Reputation points
2022-03-25T09:48:43.21+00:00

Hi,

I have a data that looks like this
$data

timestamp : 1645423991999
entities : {@{type=policyRule; label=[GIT] Administrative activity from a non-corporate IP address;
id=5e9d64458a22bee70209f290; policyType=AUDIT}, @{type=account; label=Jon Doe (Admin);
id=73890295-8ad0-445a-8033-93d86ce3e699; em=john.doe@tenant .com;
pa=john.doe@tenant .com; entityType=2; saas=11161; inst=0}, @{type=user;
label=john.doe@tenant .com; id=john.doe@tenant .com}, @{type=service;
label=Okta PSA Int; id=10999}...}
audits : {993106399_10980_1e48ae8d-92dd-11ec-9d82-818dd0912bc8}
title : [GIT] Administrative activity from a non-corporate IP address
description : Activity policy 'Administrative activity from a non-corporate IP address' was triggered
by 'Jon Doe (Admin) (john.doe@tenant .com)'
contextId : bc1b92b9-5dc9-49be-995b-c97eb515a1d3
threatScore : 35
threatScoreReasoning : {@{template=UEBA_ALERTS_SEVERITY_LEVEL_EVIDENCE; parameters=}}
intent : {0}
statusValue : 0
severityValue : 1
resolutionStatusValue : 0
idValue : 15728641
isSystemAlert : False
URL : https://tenant.com.portal.cloudappsecurity.com/#/alerts/62132d79668e595c124eb021

And I wanted to rewrite this array and export it as a new CSV that should have additional information:

timestamp : 1645423991999 (converted to simple format like mm-dd-yy hh:mm if possible.
entities : {@{type=policyRule; label=[GIT] Administrative activity from a non-corporate IP address;
id=5e9d64458a22bee70209f290; policyType=AUDIT}, @{type=account; label=Jon Doe (Admin);
id=73890295-8ad0-445a-8033-93d86ce3e699; em=john.doe@tenant .com;
pa=john.doe@tenant .com; entityType=2; saas=11161; inst=0}, @{type=user;
label=john.doe@tenant .com; id=john.doe@tenant .com}, @{type=service;
label=Okta PSA Int; id=10999}...}
audits : {993106399_10980_1e48ae8d-92dd-11ec-9d82-818dd0912bc8}
title : [GIT] Administrative activity from a non-corporate IP address
description : Activity policy 'Administrative activity from a non-corporate IP address' was triggered
by 'Jon Doe (Admin) (john.doe@tenant .com)'
contextId : bc1b92b9-5dc9-49be-995b-c97eb515a1d3
threatScore : 35
threatScoreReasoning : {@{template=UEBA_ALERTS_SEVERITY_LEVEL_EVIDENCE; parameters=}}
intent : {0}
statusValue : 0
severityValue : 1
resolutionStatusValue : 0
idValue : 15728641
isSystemAlert : False
URL : https://tenant.com.portal.cloudappsecurity.com/#/alerts/62132d79668e595c124eb021
Domain: EMEA

I was thinking of doing this

$data | export-csv c:\temp\data.csv ->>>>>>>>>>>>>>>>>>>>>>>>>how can I get rid of system.object under the column entities, etc...I exporting this as a raw data reference.

hash.csv is a file containing key and value id=73890295 = EMEA

$hash = @{}
import-csv c:\temp\hash.csv | foreach-object{
$hash[$.ID] = $.domain}

importing my data

import-csv c:\temp\data.csv | foreach-object {
$_ | add-member noteproperty 'Domain' ""
if ($hash.containskey($.entities?)){ ->>>>>>>>>>>>>>>>>>>>>>>>>>>>>I don't know how to pull this value id=73890295
$
.domain = $hash[$.entities?]
}
$
} |export-csv c:\temp\newdata.csv

"Domain" = EMEA ->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>This is from my Hash.

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,435 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 45,431 Reputation points
    2022-03-31T21:44:25.51+00:00

    If I've understood the data structure:

    get-mcasalert -identity 62434cc3230a2b72004c723a |
        ForEach-Object{
            $pa = ""
            foreach ($e in $_.entities){
                if ($e.type -eq 'account'){
                    $pa = $e.pa
                    break
                }
            }
            [PSCustomObject]@{
                Title = $_.title
                Entities = $pa
                URL = $_.URL
            }
    

    Because the title contains many Cr/Lf the display may look incorrect, but it's accurate.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Rich Matheisen 45,431 Reputation points
    2022-03-25T19:10:53.683+00:00

    From where did you get the elements in the array "$data"? It looks like a textual representation of the data in the elements rather than the data actually in the elements.

    0 comments No comments

  2. Mike 246 Reputation points
    2022-03-30T07:35:04.89+00:00

    get-mcasalert -identity 62434cc3230a2b72004c723a

    _id : 62434cc3230a2b72004c723a
    timestamp : 1648538333000
    entities : {@{type=account; label=Jon Doe; id=445b152d-9252-49a6-8a55-9c786ad4947f; pa=jonhdoe@mathieu.company .com; entityType=2; saas=11161; inst=0},
    @{type=service; label=Office 365; id=11161}, @{type=app_permission; label=API-Costdashboard-SPN; id=6242b94ed0870f0d9ecbaab5;
    clientId=83e94853-2b65-4780-a79b-fd394a7cff7f; service=11161}, @{type=policyRule; label=Unusual addition of credentials to an OAuth app;
    id=5fe12a973006332443b9bb11; policyType=ANOMALY_DETECTION}...}
    title : Unusual addition of credentials to an OAuth app
    description : The user Jon Doe (jonhdoe@mathieu.company .com) performed an unusual addition of credentials to the application API-Costdashboard-SPN. This
    usage pattern may indicate that an attacker has compromised the app, and is using it to spread phishing, exfiltrate data, or to gain access to
    other accounts and devices. The user added a credential of type Password. A credential of type Password is added when an application is using
    a password to authenticate.
    stories : {0}
    contextId : bc1b92b9-5dc9-49be-995b-c97eb515a1d3
    threatScore : 35
    threatScoreReasoning : {@{template=UEBA_ALERTS_SEVERITY_LEVEL_EVIDENCE; parameters=}}
    evidence : {@{title=}, @{title=}, @{title=}, @{title=}...}
    isPreview : False
    intent : {3}
    resolutionStatusValue : 0
    statusValue : 0
    severityValue : 1
    idValue : 15859739
    isSystemAlert : False
    URL : https://xxxx.portal.cloudappsecurity.com/#/alerts/62434cc3230a2b72004c723a
    Identity : 62434cc3230a2b72004c723a

    What I would want is to only select a portion of value in entities Combined with the title and URL

    So it will be like this

    Title :Unusual addition of credentials to an OAuth app
    Entities :pa=jonhdoe@mathieu.company .com
    URL :https://xxxx.portal.cloudappsecurity.com/#/alerts/62434cc3230a2b72004c723a

    0 comments No comments

  3. Rich Matheisen 45,431 Reputation points
    2022-03-31T19:58:40.83+00:00

    The module you're using is a 3rd-party one: Welcome to the Unofficial Microsoft Cloud App Security PowerShell module!

    The description of the cmdlet says:

    Get-MCASAlert returns a single custom PS Object or multiple PS Objects with all of the alert properties. Methods available are only those available to custom objects by default.

    Getting the Title and URL should be easy enough, but the Entities appear to be an array of hashes and you're only interested the element of the array in which there is a "type' key whose value is 'account. From that element of the array you want to find another element of that array that has a key of "pa" and extract its value.

    Is that correct?

    0 comments No comments