Forum Discussion
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 :
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
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
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)?)
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.
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.
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 AnalyticsQuerySo is there anything that could be added or modified in there?
Thanks a lot.
