Exporting my Array Properly to CSV

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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?