Extracting items (over x days) where an AIP label has changed from one value to another...

[JMSHW0420]

Hello to all,

So what I am trying to do is, is to extract extract any items (over x days) where an AIP label has changed from Confidential to another value.

The KEY part though is, that I am also trying to find out IF any of these item have been emailed by ANYONE after the the AIP label change has been made...

I STILL cannot find a way to make a 'complete' match with the 'Item' associated to an email...

I have the following query, which 'maps' against the InformationProtectionEvents, EmailEvents and EmailAttachmentInfo logs:

let LabelChange = InformationProtectionEvents
| where Time > ago(4d)
| where Activity == "DowngradeLabel"
| where LabelNameBefore contains "Confidential" and LabelName !contains "Confidential"
| where ItemPath contains "http"
| extend SenderMailFromAddress = User;
let EmailItem = EmailEvents
| join kind=inner LabelChange on SenderMailFromAddress
| summarize arg_max(TimeGenerated, *) by SenderMailFromAddress;
let EmailAttachment = EmailAttachmentInfo
| join kind=inner EmailItem on SenderObjectId
| summarize arg_max(TimeGenerated, *) by SenderObjectId;
EmailAttachment
| project ItemName, ItemPath, LabelName, LabelNameBefore, User,
SenderMailFromAddress, NetworkMessageId,
FileName, FileType, SenderObjectId

It does NOT perform the 'matching' I require and I would be really open to some suggestions.

Reach out to: @Rod_Trent, @Gary Bushey, @Ofer_Shezaf 

[#2]

If you have the time stamp, then you can probably test if an email has been sent between that time and now

...
| where TimeGenerated between (TimeofLastChange_ .. now())

[#3]

Thank you for the response @CliveWatson. Very much appreciated.

I have realised my query is not structurally correct even in its current format.

The following element is correct:

let endtime = 4d
let LabelChange = InformationProtectionEvents
| where Time > ago(endtime)
| where Activity == "DowngradeLabel"
| where LabelNameBefore contains "Confidential" and LabelName !contains "Confidential"
| where ItemPath contains "http";

Out of the above, I can extract the ItemName attribute which represents the file name where the AIP label has changed.

...the part of trying to find out IF any of these items have been emailed by ANYONE after the AIP label has changed, CANNOT be matched against the 'User' attribute ONLY.

Clive you mention the time stamp; do you mean matching within the EmailEvents log?

So you mean something like:

let EmailItems = EmailEvents
| where TimeGenerated between (endtime .. now())
| summarize arg_max(TimeGenerated, *) by SenderMailFromAddress;
let EmailAttachments = EmailAttachmentInfo
| join kind=inner EmailItems on SenderObjectId
| summarize arg_max(TimeGenerated, *) by SenderObjectId;

The above is assuming the file changed SHOULD be attachment. So I am still struggling how to work out a way to 'combine' the 'let' statements of 'LabelChange' and 'EmailAttachments'.

Again, any suggestions much appreciated.

[#4]

@JMSHW0420 

Sorry I don't have an example data so this is fake code

EmailEvents
| where AttachmentCount > 0
// this detects the file and its last timestamp
| summarize arg_max(TimeGenerated, *) by SenderMailFromAddress
// add one ms to make the new time *after* the record was found 
| extend endtime = TimeGenerated + 1ms  
//
// now see if the item is in EmailItems within the new time period 
// join to EmailItems ... by ....
// is the email item seen in this new time window?
| where TimeGenerated between (endtime .. now())

We could also use a datatable to fake the tables you are using, this is what I think EmailEvents may look like (or the key columns at least) 

let EmailEvents = datatable(AttachmentCount:int, TimeGenerated:datetime, SenderMailFromAddress:string)
[
"0", datetime(2021-11-04T11:36:42.6616095Z),"clive@fake.com",
"1", datetime(2021-11-03T12:30:53.4764186Z),"clive@morefake.com",
"2", datetime(2021-11-02T12:30:53.4764186Z),"clive@morefake.com"
];
EmailEvents
| where AttachmentCount > 0
| summarize arg_max(TimeGenerated, *) by SenderMailFromAddress
| extend endtime = TimeGenerated + 1ms  

[#5]

Thank you for the quick response @CliveWatson. Once again, very much appreciated.

I am going to be humble and say, not quite sure what you mean with TimeGenerated 'expansion'.

I tried to run:

let endtime = 1d;
EmailEvents
| where AttachmentCount > 0
| summarize arg_max(TimeGenerated, *) by SenderMailFromAddress
| extend endtime = TimeGenerated + 1ms
| where TimeGenerated between (ago(endtime) .. now())

...and failed with 'endtime' in the TimeGenerated of the LAST line.

IF you have the time, maybe you could just expand a little on this please?

EmailEvents does not store Attachments either which is why I am trying to link up with EmailAttachmentInfo log.

I was hoping I could use the FileName from EmailAttachmentInfo and see IF there was a match (or contains) with the ItemName from the InformationProtectionEvents log.