KQL Search

Query Details

HomeAI ToolsDevice Query

TI Tweet Feed UR Ls In Emails

Query

let TwitterFeed_today = externaldata (Date: string, Src: string, Art: string, Value: string) ["https://raw.githubusercontent.com/0xDanielLopez/TweetFeed/master/today.csv"];
let TwitterFeed_week = externaldata (Date: string, Src: string, Art: string, Value: string) ["https://raw.githubusercontent.com/0xDanielLopez/TweetFeed/master/week.csv"];
let TwitterFeed_month = externaldata (Date: string, Src: string, Art: string, Value: string) ["https://raw.githubusercontent.com/0xDanielLopez/TweetFeed/master/month.csv"];
EmailUrlInfo 
| join EmailEvents on NetworkMessageId
| join (TwitterFeed_today
| where Art == "url"
) on $left.Url == $right.Value
| join (TwitterFeed_week
| where Art == "url"
) on $left.Url == $right.Value
| join (TwitterFeed_month
| where Art == "url"
) on $left.Url == $right.Value

Explanation

This KQL query is designed to analyze email data by cross-referencing it with Twitter data from three different timeframes: today, this week, and this month. Here's a breakdown of what the query does:

  1. Load Twitter Data:

    • It imports Twitter data from three CSV files hosted online, each representing a different timeframe: today, this week, and this month. Each dataset includes columns for Date, Source (Src), Article type (Art), and Value.

  2. Filter Twitter Data:

    • For each of the Twitter datasets (today, week, month), it filters the data to only include rows where the "Art" column is equal to "url". This means it focuses on URLs mentioned in tweets.

  3. Join with Email Data:

    • It starts with the EmailUrlInfo table and joins it with the EmailEvents table using the NetworkMessageId as the key.
    • Then, it performs a series of joins with the filtered Twitter datasets (today, week, month) using the URL as the matching key. Specifically, it matches the URL from the email data with the URL mentioned in the Twitter data.

In simple terms, this query is trying to find URLs that appear in both email data and Twitter data over different time periods, allowing for an analysis of how URLs in emails are being discussed on Twitter.

Details

Benjamin Zulliger profile picture

Benjamin Zulliger

Released: November 10, 2024

Tables

EmailUrlInfoEmailEvents

Keywords

TwitterFeedEmailEventsEmailUrlInfo

Operators

letexternaldatajoinonwhere==$left$right

Actions