Where does the MaliciousIP field come from in this query?

[Gary Bushey]

This is the query for the Potential Malicious Events map on the Azure Sentinel homepage.

union isfuzzy=true
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound", "InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND", "InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude),
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound", "InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription),
(VMConnection
| where Type == "VMConnection"
| extend TrafficDirection = iff(Direction != "outbound", "InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)
| where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude)

Where the heck does that MaliciousIP field come from?  If I run the query without the last "where" clause I do not see it but when I run the entire query it shows up.

[#2 · Accepted solution]

We have looked into this somewhat as well. If you pull up the log table for something like CommonSecurityLog/Zscaler/WindowsFireWall) you will see that MaliciousIP (and Mal Lat/Long/Country) are already in those tables. You will need to make sure those boxes are checked in the Columns Drop down in the results. When you do see them almost all of them are empty. It seems that those boxes DO get filled in as the logs come in and match a corresponding Malicious entity that exists in the tables of ThreatIntelligenceIndicators or are a part of the Threat Intel Data Connector, or even part of the "under the hood" threat intel that MSFT provides. I think there is a bit of "under the hood" stuff going on as logs come in, which makes this different than say a lookback on DNS requests compared to Domains in the TIindicators.

I have not checked if the MalciousIP table columns exist before Threat Intel is turned on - but might fun to check when spinning up your next instance.

[#3]

Got it. It is strange, I thought that when you opened a result using the greater than sign on the left it would show all the columns. I guess I was wrong. Thanks for the answer