SOLVED

AIP Log Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-1278959%22%20slang%3D%22en-US%22%3EAIP%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278959%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20query%20for%20Azure%20Information%20Protection%20that%20will%20generate%20a%20report%20for%20the%20number%20of%20classification%20labels%20applied%20during%20the%20day%20that%20includes%20the%20application%20name%20and%20label%20type.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EClassification%20Label%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Label%20Type%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Application%20Type%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount%3C%2FP%3E%3CP%3EPublic%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20New%20Label%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Outlook%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2010%3C%2FP%3E%3CP%3EInternal%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Upgrade%20Label%20%26nbsp%3B%20Word%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2015%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20what%20I%20have%3A%3C%2FP%3E%3CP%3EInformationProtectionLogs_CL%3CBR%20%2F%3E%7C%20where%20Activity_s%20%3D%3D%20%22NewLabel%22%3CBR%20%2F%3E%7C%20where%20ApplicationName_s%20%3D%3D%20%22Word%22%20or%20ApplicationName_s%20%3D%3D%20%22Outlook%22%3CBR%20%2F%3E%7C%20where%20LabelName_s%20%3D%3D%20%22Highly%20Confidential%20QA%22%20or%20LabelName_s%20%3D%3D%20%22Confidential%20QA%22%20or%20LabelName_s%20%3D%3D%20%22Internal%20QA%22%20or%20LabelName_s%20%3D%3D%20%22Public%20QA%22%3CBR%20%2F%3E%7C%20project%20Label_Name%20%3D%20columnifexists(%22LabelName_s%22%2C%22%22)%2C%20Application_Name%20%3D%20columnifexists(%22ApplicationName_s%22%2C%22%22)%2C%20Label_Type%20%3D%20columnifexists(%22Activity_s%22%2C%20%22NewLabel%22)%3CBR%20%2F%3E%7C%20summarize%20New_Labels%20%3D%20count(Label_Type)%20by%20Label_Name%3CBR%20%2F%3E%7C%20sort%20by%20New_Labels%20desc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1278959%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAIP%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1289578%22%20slang%3D%22en-US%22%3ERe%3A%20AIP%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1289578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F608134%22%20target%3D%22_blank%22%3E%40Metzinger35%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eneeds%20some%20more%20work%2C%20but%20is%20this%20right%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EInformationProtectionLogs_CL%0A%2F%2F%7C%20where%20Activity_s%20%3D%3D%20%22NewLabel%22%0A%2F%2F%7C%20where%20ApplicationName_s%20%3D%3D%20%22Word%22%20or%20ApplicationName_s%20%3D%3D%20%22Outlook%22%0A%7C%20where%20LabelName_s%20in%20(%22Highly%20Confidential%20QA%22%2C%22Confidential%20QA%22%2C%22Internal%20QA%22%20%2C%22Public%20QA%22%2C%22Confidential%20%5C%5C%20All%20Employees%22)%20%20%2F%2F%20I%20added%20the%20last%20one%20for%20my%20data%20to%20get%20a%20match%20%0A%7C%20project%20Label_Name%20%3D%20columnifexists(%22LabelName_s%22%2C%22%22)%2C%20Application_Name%20%3D%20columnifexists(%22ApplicationName_s%22%2C%22%22)%2C%20Label_Type%20%3D%20columnifexists(%22Activity_s%22%2C%20%22NewLabel%22)%2C%20ApplicationName_s%0A%7C%20summarize%20Amount%20%3D%20count(Label_Type)%20by%20%5B%22Classification%20Label%22%5D%20%3D%20Label_Name%2C%20Label_Type%2C%20%5B%22Application%20Type%22%5D%20%3D%20ApplicationName_s%0A%7C%20sort%20by%20Amount%20desc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eresult%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EClassification%20Label%3C%2FTH%3E%0A%3CTH%3ELabel_Type%3C%2FTH%3E%0A%3CTH%3EApplication%20Type%3C%2FTH%3E%0A%3CTH%3EAmount%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EConfidential%20%5C%20All%20Employees%3C%2FTD%3E%0A%3CTD%3ENewLabel%3C%2FTD%3E%0A%3CTD%3EMicrosoft%20Cloud%20App%20Security%3C%2FTD%3E%0A%3CTD%3E2%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1291473%22%20slang%3D%22en-US%22%3ERe%3A%20AIP%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1291473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20all%20the%20help%2C%20that%20works.%20I%20even%20added%20time%20generated%20column%20combining%20all%20labels%20that%20was%20classified%20during%20specific%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EInformationProtectionLogs_CL%0A%7C%20where%20Activity_s%20%3D%3D%20%22NewLabel%22%20or%20Activity_s%20%3D%3D%20%22UpgradeLabel%22%20or%20Activity_s%20%3D%3D%20%22RemoveLabel%22%20or%20Activity_s%20%3D%3D%20%22DowngradeLabel%22%0A%7C%20where%20ApplicationName_s%20%3D%3D%20%22AIP%20scanner%22%20or%20ApplicationName_s%20%3D%3D%20%22Excel%22%20or%20ApplicationName_s%20%3D%3D%20%22Microsoft%20Cloud%20App%20Security%22%20or%20ApplicationName_s%20%3D%3D%20%22Outlook%22%20or%20ApplicationName_s%20%3D%3D%20%22PowerPoint%22%20or%20ApplicationName_s%20%3D%3D%20%22Word%22%0A%7C%20where%20LabelName_s%20in%20(%22Highly%20Confidential%20QA%22%2C%22Confidential%20QA%22%2C%22Internal%20QA%22%2C%22Public%20QA%22)%0A%7C%20project%20Label_Name%20%3D%20columnifexists(%22LabelName_s%22%2C%22%22)%2C%20Application_Name%20%3D%20columnifexists(%22ApplicationName_s%22%2C%22%22)%2C%20Activity%20%3D%20columnifexists(%22Activity_s%22%2C%20%22NewLabel%22)%2C%20format_datetime(TimeGenerated%2C%20'MM-dd-yyyy')%0A%7C%20summarize%20Count%20%3D%20toint(count(Label_Name))%20by%20Label_Name%2C%20Application_Name%2C%20Activity%2C%20TimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResults%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22622%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CSTRONG%3ELabel_Name%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22190%22%3E%3CSTRONG%3EApplication_Name%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%3CSTRONG%3EActivity%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22105%22%3E%3CSTRONG%3ETimeGenerated%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CSTRONG%3ECount%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHighly%20Confidential%20QA%3C%2FTD%3E%3CTD%3EMicrosoft%20Cloud%20App%20Security%3C%2FTD%3E%3CTD%3ENewLabel%3C%2FTD%3E%3CTD%3E3%2F23%2F2020%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHighly%20Confidential%20QA%3C%2FTD%3E%3CTD%3EOutlook%3C%2FTD%3E%3CTD%3ENewLabel%3C%2FTD%3E%3CTD%3E3%2F23%2F2020%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

2 Replies

@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
best response confirmed by Clive Watson (Microsoft)
Solution

@Clive Watson 

 

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