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 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, GaryBushey, Ofer_Shezaf 

4 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Nov 04, 2021
    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())
    • JMSHW0420's avatar
      JMSHW0420
      Iron Contributor
      Nov 05, 2021
      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
        Former Employee
        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


         

Resources