Forum Discussion

Metzinger35's avatar
Metzinger35
Copper Contributor
Apr 03, 2020

AIP Log Analytics

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.

  • CliveWatson 

     

    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_NameApplication_NameActivityTimeGeneratedCount
    Highly Confidential QAMicrosoft Cloud App SecurityNewLabel3/23/20202
    Highly Confidential QAOutlookNewLabel3/23/20201

     

  • Metzinger35 

     

    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
    • Metzinger35's avatar
      Metzinger35
      Copper Contributor

      CliveWatson 

       

      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_NameApplication_NameActivityTimeGeneratedCount
      Highly Confidential QAMicrosoft Cloud App SecurityNewLabel3/23/20202
      Highly Confidential QAOutlookNewLabel3/23/20201

       

Resources