Extract info from column

[Anonymous]

Hello, i am using a JSON file for my data in PowerBI. There are columns (description) which looks like the following:

I am trying to look for all the groups inside of that field. So if the description column contains "attack.mitre.org/groups" i want to extract the square brackets before it E.G (Putter Panda, BRONZE Bulter, Threat Group 3390, Lazzarus Group) Is this possible in a measure? The original JSON is from here :JSON 

[PhilipTreacy]

Hi @Anonymous 

Please download this file and see if it does what you need.

I'm a little unsure of exactly what you want.  You asked for the results to be generated via a measure which is what @Greg_Deckler  gave you.  But you then said you want it in a column?  You can display a measure in a table visual. But maybe you want it in a column in Power Query? That's the way I've approached it.

Searching for text between [] is straight foward however none of the extracted text matched (or was even close to) the example you gave Putter Panda, BRONZE Bulter, Threat Group 3390, Lazzarus Group 

All of the text extracted looked like this and I used the JSON file you linked to from GitHub

Please check the file I supplied and let me know if that's what you need or if you need some changes.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy  yes that is on the right track. However i only wanted the contents of the [] that was next to the URL "attack.mitre.org/groups" that is the information i am looking for. as the attached one also includes the contents of "attack.mitre.org/software" which im not intrested in at the moment

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Try the Power Query code in this file

Which gives these results

Regards

Phil

[Greg_Deckler]

@Anonymous - Perhaps try something like this:

Measure 3 = 
    VAR __Description = MAX([Column1.description])
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,LEN(__Description),1),
            "__Char",MID(__Description,[Value],1)
        )
    VAR __IsGroup = FIND("/group",__Description,1,0)
    VAR __SquareBracketStart = MAXX(FILTER(__Table,[Value]<__IsGroup && [__Char]="["),[Value])
    VAR __SquareBracketEnd = MINX(FILTER(__Table,[Value]>__SquareBracketStart && [__Char]="]"),[Value])
RETURN
    IF(__IsGroup = 0,BLANK(),MID(__Description,__SquareBracketStart+1,__SquareBracketEnd - __SquareBracketStart - 1))

PBIX is attached.

[Anonymous]

thanks @Greg_Deckler. Is there a way to put this into a new column instead of a measure? So create a new column with the group names in there? Some of the columns wont have groups so dont know if that would make an inpact? Its so i am able to create graphs based on the data output etc

[PhilipTreacy]

Hi @Anonymous 

You'd be better off doing this in Power Query,it'sbuilt for exactly this.

Can you please supply some data,not a picture.

Thanks

Phil

[Anonymous]

Hi @PhilipTreacy 

[BISCUIT](https://attack.mitre.org/software/S0017) is a backdoor that has been used by [APT1](https://attack.mitre.org/groups/G0006) since as early as 2007. (Citation: Mandiant APT1)

[BONDUPDATER](https://attack.mitre.org/software/S0360) is a PowerShell backdoor used by [OilRig](https://attack.mitre.org/groups/G0049). It was first observed in November 2017 during targeting of a Middle Eastern government organization, and an updated version was observed in August 2018 being used to target a government organization with spearphishing emails.(Citation: FireEye APT34 Dec 2017)(Citation: Palo Alto OilRig Sep 2018)

[BOOSTWRITE](https://attack.mitre.org/software/S0415) is a loader crafted to be launched via abuse of the DLL search order of applications used by [FIN7](https://attack.mitre.org/groups/G0046).(Citation: FireEye FIN7 Oct 2019)

[BS2005](https://attack.mitre.org/software/S0014) is malware that was used by [Ke3chang](https://attack.mitre.org/groups/G0004) in spearphishing campaigns since at least 2011. (Citation: Villeneuve et al 2014)

[PhilipTreacy]

Thx @Anonymous 

I meant can you copy/paste the data in the image, or supply the file that contains that data?

Regards

Phil

[Anonymous]

@PhilipTreacy sorry. The original data is in the question. but can be found here