Apr 03 2020
07:45 AM
- last edited on
Apr 08 2022
10:22 AM
by
TechCommunityAP
Apr 03 2020
07:45 AM
- last edited on
Apr 08 2022
10:22 AM
by
TechCommunityAP
Hi,
I'm trying to create a query for Azure Information Protection that will generate a report for the number of classification labels applied during the day that includes the application name and label type.
Example:
Classification Label Label Type Application Type Amount
Public New Label Outlook 10
Internal Upgrade Label Word 15
Currently what I have:
InformationProtectionLogs_CL
| where Activity_s == "NewLabel"
| where ApplicationName_s == "Word" or ApplicationName_s == "Outlook"
| where LabelName_s == "Highly Confidential QA" or LabelName_s == "Confidential QA" or LabelName_s == "Internal QA" or LabelName_s == "Public QA"
| project Label_Name = columnifexists("LabelName_s",""), Application_Name = columnifexists("ApplicationName_s",""), Label_Type = columnifexists("Activity_s", "NewLabel")
| summarize New_Labels = count(Label_Type) by Label_Name
| sort by New_Labels desc
Any help is greatly appreciated.
Apr 07 2020 12:22 PM
needs some more work, but is this right?
InformationProtectionLogs_CL
//| where Activity_s == "NewLabel"
//| where ApplicationName_s == "Word" or ApplicationName_s == "Outlook"
| where LabelName_s in ("Highly Confidential QA","Confidential QA","Internal QA" ,"Public QA","Confidential \\ All Employees") // I added the last one for my data to get a match
| project Label_Name = columnifexists("LabelName_s",""), Application_Name = columnifexists("ApplicationName_s",""), Label_Type = columnifexists("Activity_s", "NewLabel"), ApplicationName_s
| summarize Amount = count(Label_Type) by ["Classification Label"] = Label_Name, Label_Type, ["Application Type"] = ApplicationName_s
| sort by Amount desc
result:
Classification Label | Label_Type | Application Type | Amount |
---|---|---|---|
Confidential \ All Employees | NewLabel | Microsoft Cloud App Security | 2 |
Apr 08 2020 04:34 AM
Solution
Thanks for all the help, that works. I even added time generated column combining all labels that was classified during specific days.
InformationProtectionLogs_CL
| where Activity_s == "NewLabel" or Activity_s == "UpgradeLabel" or Activity_s == "RemoveLabel" or Activity_s == "DowngradeLabel"
| where ApplicationName_s == "AIP scanner" or ApplicationName_s == "Excel" or ApplicationName_s == "Microsoft Cloud App Security" or ApplicationName_s == "Outlook" or ApplicationName_s == "PowerPoint" or ApplicationName_s == "Word"
| where LabelName_s in ("Highly Confidential QA","Confidential QA","Internal QA","Public QA")
| project Label_Name = columnifexists("LabelName_s",""), Application_Name = columnifexists("ApplicationName_s",""), Activity = columnifexists("Activity_s", "NewLabel"), format_datetime(TimeGenerated, 'MM-dd-yyyy')
| summarize Count = toint(count(Label_Name)) by Label_Name, Application_Name, Activity, TimeGenerated
Results:
Label_Name | Application_Name | Activity | TimeGenerated | Count |
Highly Confidential QA | Microsoft Cloud App Security | NewLabel | 3/23/2020 | 2 |
Highly Confidential QA | Outlook | NewLabel | 3/23/2020 | 1 |
Apr 08 2020 04:34 AM
Solution
Thanks for all the help, that works. I even added time generated column combining all labels that was classified during specific days.
InformationProtectionLogs_CL
| where Activity_s == "NewLabel" or Activity_s == "UpgradeLabel" or Activity_s == "RemoveLabel" or Activity_s == "DowngradeLabel"
| where ApplicationName_s == "AIP scanner" or ApplicationName_s == "Excel" or ApplicationName_s == "Microsoft Cloud App Security" or ApplicationName_s == "Outlook" or ApplicationName_s == "PowerPoint" or ApplicationName_s == "Word"
| where LabelName_s in ("Highly Confidential QA","Confidential QA","Internal QA","Public QA")
| project Label_Name = columnifexists("LabelName_s",""), Application_Name = columnifexists("ApplicationName_s",""), Activity = columnifexists("Activity_s", "NewLabel"), format_datetime(TimeGenerated, 'MM-dd-yyyy')
| summarize Count = toint(count(Label_Name)) by Label_Name, Application_Name, Activity, TimeGenerated
Results:
Label_Name | Application_Name | Activity | TimeGenerated | Count |
Highly Confidential QA | Microsoft Cloud App Security | NewLabel | 3/23/2020 | 2 |
Highly Confidential QA | Outlook | NewLabel | 3/23/2020 | 1 |