How to extract information from the database when we have unique event id splitted across rows having different evidence values like email subject, email recipient, attachments etc? Also when we have multiple email recipients?

asked 01 Aug '16, 09:45

Jaydeep's gravatar image

Jaydeep
152
accept rate: 0%


First we need to consolidate email recipients in one field. This can be done using stuff function in SQL. Then we have to convert the rows to columns. This can be done using Pivot function in SQL. Following is the query:

select * from ( select a.[EventRowID] AS EventID, b.[Evidencetype] AS EvidenceType, STUFF((select distinct ',' + +[DLP_EvidenceTypeAndValue].[EvidenceValue] from [DLP_EventView] left join [DLP_EvidenceTypeAndValue] on [DLP_EventView].[EventRowID] = [DLP_EvidenceTypeAndValue].[EventRowID] where [DLP_EvidenceTypeAndValue].[EvidenceType]=b.[EvidenceType] and [DLP_EventView].[EventRowID] = a.[EventRowID] FOR XML PATH('')) , 1, 1, '') as EvidenceValue, a.[UTCTime], a.[Policy_DateModified], a.[ComputerName], c.[UserName], a.[EventTypeDisplayName], a.[ReactionSet_DisplayName] AS AgentAction, a.[Score] AS Severity, a.[TotalContentSize], a.[TotalNumberOfHits], a.[FocusDisplay] AS Destination, a.[TagSet_DisplayName] AS TagAndCatg, a.[Evidence], a.[Online], a.[TotalNumberOfCategoriesAndTags], a.[Policy_Name], a.[ProcessInfo_FileName] from [DLP_EventView] AS a left join [DLP_EvidenceTypeAndValue] AS b on a.[EventRowID] = b.[EventRowID] left join [EPOComputerProperties] as c on a.[ComputerName] = c.[ComputerName] where a.[InsertionTime] >= '2016-07-01T13:28:06.683' and EventTypeDisplayName LIKE 'DLP: Email Protection' group by a.[EventRowID],b.[Evidencetype], a.[UTCTime], a.[Policy_DateModified], a.[ComputerName], c.[UserName], a.[EventTypeDisplayName], a.[ReactionSet_DisplayName], a.[Score], a.[TotalContentSize], a.[TotalNumberOfHits], a.[FocusDisplay], a.[TagSet_DisplayName], a.[Evidence], a.[Online], a.[TotalNumberOfCategoriesAndTags], a.[Policy_Name], a.[ProcessInfo_FileName] ) d pivot ( max(EvidenceValue) for EvidenceType in (EMAIL_RECIPIENT, EMAIL_SUBJECT, FILE_NAME) ) piv;

After ingesting we just need to group by Event id and split across email recipient.

Thanks Prabhu, Yukesh and Kayzad for your help on this...

Thanks, JD

link

answered 01 Aug '16, 09:52

Jaydeep's gravatar image

Jaydeep
152
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×3
×2
×2
×1

Asked: 01 Aug '16, 09:45

Seen: 403 times

Last updated: 01 Aug '16, 09:52