Forum Discussion

JMSHW0420's avatar
JMSHW0420
Iron Contributor
Nov 04, 2021

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

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 ...
JMSHW0420's avatar
JMSHW0420
Iron Contributor
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.
CliveWatson's avatar
CliveWatson
Icon for Microsoft rankMicrosoft
Nov 05, 2021

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


 

  • JMSHW0420's avatar
    JMSHW0420
    Iron Contributor
    Nov 05, 2021
    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.

Resources