cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Patrick1 Frequent Visitor
Frequent Visitor

Filter within a field

Hi,

 

I'm an IT admin and we're using Power BI to visualize the event logs from our servers. I have a query that downloads the logs with a specific event ID (4771). The problem I have with this one is that the information I'm interested in is cluttered within a single field called EventData. See the screenshot below :

 

 pbi4771.JPG

 

 

The real fields I'm interested in are the yellow ones. I don't find any way to filter only this information. On top of that I'd like to be able to sum or group all the events that occured either for the same TargetUserName or the same IpAddress.

 

Is there any way this can be done?

 

Thanks a lot!

 

Patrick

1 ACCEPTED SOLUTION

Accepted Solutions
erik_tarnvik Established Member
Established Member

Re: Filter within a field

Great. We will start by extracting your first data point, the one that follow TargetUserName. First, make a copy of the Event Data column. You do this by first selecting the query in the Query Pane on the left hand side of your screen. Then click on the Add Column menu selection at the top of the Power Query window. Select the Event Data column in your table by clicking on the header. Then click Duplicate Column. You should now get a new column called "Event Data - Copy".

 

Select this new column. Then click Extract in the menu and click Text Between Delimiters in the resulting drop-down menu. In the resulting dialog box, enter the text 

TargetUserName">

as Start delimiter and

</Data> 

as the End delimiter. Click OK. The column should now only contain the text you wanted for the first data point as per the pic you attached to your original question. If not, please carefully examine the delimiters to make sure that they match the start and end point of the text you want extracted. Once satisfied, rename this new column to something that makes sense to you (perhaps TargetUserName in this example) by editing the name in the column header.

 

To complete the remaining data points you just have to repeat these steps for each one, only changing the delimiters accordingly. Hopefully this works, and if not please let me know what problems you ran into. Good luck!

10 REPLIES 10
MarcelBeug Super Contributor
Super Contributor

Re: Filter within a field

It looks like you have some data cleaning to do with Power Query (i.e. in the Query Editor).

If you need help with that, then please provide exact specifications how to extract the required data (possibly the indicated labels should be searched for and the corresponding values are between the next ">" and the next "<" (e.g. "0x25" for field Status)?)

Specializing in Power Query Formula Language (M)
erik_tarnvik Established Member
Established Member

Re: Filter within a field

@MarcelBeug is right, this is best (and easily) done in Power Query. Here is how:

 

Enter the query editor and select your data source. Using your example, start by creating two extra custom columns that are just a copy of your EventData column. Then select your first EventData column and use the Extract - Text Between Deliminators menu choice under the Transform menu. Specify 

TargetUserName">

as the starting deliminator and

</Data> 

as the ending deliminator. You now have the first column done. Repeat for the other columns. Rename columns as appropriate.

erik_tarnvik Established Member
Established Member

Re: Filter within a field

For completeness I should mention that instead of duplicating the columns, you could start by splitting column EventData by deliminator </Data>, remove the resulting columns that contain unwanted data and perform the Extract step on the remaining columns. Not sure which version I prefer frankly but if the data file is huge, this method may intuitively have better performance . I am never quite sure about that though, I've been surprised before.

Patrick1 Frequent Visitor
Frequent Visitor

Re: Filter within a field

Thanks to both of you and sorry for the delay.

 

I'm not sure I understand what I need to do and I'm not sure if it can be done. Our logs are pulled from the Azure cloud and our analytics system out there generated the query for Power BI. Here's what it looks like. I only removed our subscription ID in the source URL for privacy reasons.

 

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/...",
[Query=[#"query"="SecurityEvent | where EventID == 4771",#"x-ms-app"="OmsAnalyticsPBI",#"timespan"="PT24H10M",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string",   Text.Type },
{ "int",      Int32.Type },
{ "long",     Int64.Type },
{ "real",     Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool",     Logical.Type },
{ "guid",     Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery

So is there anything that could be added or modified in there?

 

Thanks a lot.

erik_tarnvik Established Member
Established Member

Re: Filter within a field

If I am not missing something here you should be able to proceed with the steps I proposed, through the GUI of Power Query. This will modify the script you copied and add additional steps to that script.

 

I haven't used Azure though, how is the script you copied in your post provided to you, ist that something you manually put in Power BI or is there automation involved here?

Patrick1 Frequent Visitor
Frequent Visitor

Re: Filter within a field

Thanks again for the reply. Yes as I said the Power BI query is generated within our Azure Analytics system. In fact it's only a way of exporting the data. I define my search criterias in Azure and then choose to export the results to Power BI and then the query I posted is automatically generated.

 

My knowledge of Power Query is basically zero so the more details you can give me the more it would help Smiley Happy

 

Thanks!

erik_tarnvik Established Member
Established Member

Re: Filter within a field

Just to make sure we don't go down a rabbit hole due to my inexperience with Azure, are you able to change the table (resulting from the "M" script) in Power Query? Try something simple like removing or renaming a column. Does that work?

 

Also, since there is some automation involved here with which I am unfamilar: We may be able to change the script so that it produces what your are after. But, if there is an external process that defines the script perhaps your modifications would be overwritten when the automation executes the next time? There are ways around these conserns though, we just need to be aware if they are valid or not.

Patrick1 Frequent Visitor
Frequent Visitor

Re: Filter within a field

Yes I can modify the query as I wish. Azure won't replace it by itself. The data can be refreshed but the structure of the query won't be automatically overwritten. 

 

So this shouldn't be a problem.

 

Thanks a lot!

erik_tarnvik Established Member
Established Member

Re: Filter within a field

Great. We will start by extracting your first data point, the one that follow TargetUserName. First, make a copy of the Event Data column. You do this by first selecting the query in the Query Pane on the left hand side of your screen. Then click on the Add Column menu selection at the top of the Power Query window. Select the Event Data column in your table by clicking on the header. Then click Duplicate Column. You should now get a new column called "Event Data - Copy".

 

Select this new column. Then click Extract in the menu and click Text Between Delimiters in the resulting drop-down menu. In the resulting dialog box, enter the text 

TargetUserName">

as Start delimiter and

</Data> 

as the End delimiter. Click OK. The column should now only contain the text you wanted for the first data point as per the pic you attached to your original question. If not, please carefully examine the delimiters to make sure that they match the start and end point of the text you want extracted. Once satisfied, rename this new column to something that makes sense to you (perhaps TargetUserName in this example) by editing the name in the column header.

 

To complete the remaining data points you just have to repeat these steps for each one, only changing the delimiters accordingly. Hopefully this works, and if not please let me know what problems you ran into. Good luck!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 248 members 2,831 guests
Please welcome our newest community members: