Forum Discussion

Brass Contributor

Feb 03, 2020

KQL for devices integrated

Hi Everyone, Please help with a query (KQL) in order to collect all the devices list (from where our Log Analytics, Sentinel is collecting logs). Thanks in Advance. Regards, Mitesh Agraw...

azure monitor

Query Language

MiteshAgrawal

Brass Contributor

Feb 03, 2020

Hi CliveWatson,

Appreciate your quick reply. I guess my requirement wasn't clear.

I want to know the devices sending logs to Azure Sentinel. We have Windows servers, Syslog devices (Firewalls, WAF, etc.), Linux servers, AV, etc.

I need to know:

1. The total count and the list of devices integrated (sending or configured to send but not sending logs - log stoppage).

2. The total count and the list of devices sending logs currently.

CliveWatson

Former Employee

Feb 04, 2020

MiteshAgrawal

For Servers - Windows or Linux, use this but please adjust the top two lines to suit your own criteria

let timeRangeQuery = 1h;
let UnhealthyCriteria = 1m;
Heartbeat
| where TimeGenerated > startofday(ago(timeRangeQuery))
| summarize LastHeartbeat = max(TimeGenerated) by Computer, OSType, OSName
| extend State = iff(LastHeartbeat < ago(UnhealthyCriteria), 'Unhealthy', 'Healthy')
| extend TimeFromNow = now() - LastHeartbeat
| extend ["TimeAgo"] = strcat(toint(TimeFromNow / 1s), ' seconds')
| project Computer, State, TimeAgo, TimeFromNow, OSType
| order by TimeAgo desc

Go to Log Analytics and run query

Computer	State	TimeAgo	TimeFromNow	OSType
ContosoASCAlert	Unhealthy	97 seconds	00:01:37.8659845	Windows
ContosoSQLSrv1.ContosoRetail.com	Healthy	8 seconds	00:00:08.1359845	Windows
aks-nodepool1-25494468-3	Healthy	7 seconds	00:00:07.0689845	Linux
rancher-node-3	Unhealthy	69 seconds	00:01:09.4889845	Linux
gangams-kind-k8s-cluster-master	Unhealthy	69 seconds	00:01:09.0929845	Linux
aks-agentpool-18945339-1	Unhealthy	67 seconds	00:01:07.6729845	Linux
hardening-demo	Unhealthy	66 seconds	00:01:06.3559845	Linux
aks-agentpool-14727540-0	Unhealthy	66 seconds	00:01:06.6129845	Linux
MarketingLinux1	Unhealthy	65 seconds	00:01:05.2389845	Linux
ContosoAppSrv1	Unhealthy	63 seconds	00:01:03.8259845	Windows
TargetVM	Unhealthy	60 seconds	00:01:00.0759845	Windows
aks-nodepool1-25494468-4	Unhealthy	60 seconds	00:01:00.4929845	Linux
aks-agentpool-18945339-2	Healthy	59 seconds	00:00:59.9459845	Linux
aks-nodepool1-25494468-2	Healthy	56 seconds	00:00:56.8229845	Linux
aks-agentpool-14727540-2	Healthy	56 seconds	00:00:56.1859845	Linux
rancher-node-2	Healthy	55 seconds	00:00:55.4159845	Linux
aks-agentpool-18945339-0	Healthy	54 seconds	00:00:54.2189845	Linux
ContosoJbFwJb	Healthy	50 seconds	00:00:50.8689845	Windows
demo2	Healthy	50 seconds	00:00:50.3389845	Linux
k8s-master-14134042-0	Healthy	45 seconds	00:00:45.8929845	Linux
aks-nodepool1-42911611-2	Healthy	41 seconds	00:00:41.8159845	Linux
InfraScaleVMs	Healthy	40 seconds	00:00:40.0389845	Linux
AmberIgniteDemo	Healthy	39 seconds	00:00:39.9889845	Windows
aks-agentpool-14727540-1	Healthy	39 seconds	00:00:39.2529845	Linux
aks-nodepool1-25494468-1	Healthy	36 seconds	00:00:36.5129845	Linux
aks-agentpool-40719753-2	Healthy	35 seconds	00:00:35.9229845	Linux
ContosoWeb1.ContosoRetail.com	Healthy	33 seconds	00:00:33.8189845	Windows
rancher-node-1	Healthy	25 seconds	00:00:25.1429845	Linux
aks-agentpool-40719753-1	Healthy	22 seconds	00:00:22.6459845	Linux
AmberIgnite1803	Healthy	16 seconds	00:00:16.5589845	Windows
node-4	Unhealthy	126 seconds	00:02:06.1189845	Linux
ContosoAzLnx1	Healthy	-4 seconds	-00:00:04.4010155	Linux

Syslog devices like Firewalls, maybe in the Syslog or CommonSecurity (CEF) tables....more later

CliveWatson
Former Employee
Feb 04, 2020
MiteshAgrawal

Here are some Tables for the other data sets you require. As you can see it will be helpful if you listed the products to help you identify the Tables the devices "might" send data to. This isn't a full list...you will need to check your own data.

// find Azure Firewalls

AzureDiagnostics

| where ResourceType == "AZUREFIREWALLS"

//Windows Firewall

WindowsFirewall
| summarize count() by FirewallAction

//Barracuda

CGFWFirewallActivity

//Barracuda WAF

CommonSecurityLog
| where DeviceVendor == "Barracuda"

//CommonSecurityLog
| where DeviceVendor == "Check Point"

CommonSecurityLog
| where DeviceVendor == "Cisco"
| where DeviceProduct == "ASA"

Here is a sample query, you will need to add the logic from the Heartbeat table I showed before (to get last record etc...), and also tweak each line to match what ever devices and data you have.

union isfuzzy=true withsource = tt (AzureDiagnostics | where ResourceType == "AZUREFIREWALLS" ), (WindowsFirewall | summarize count() by FirewallAction ), (CGFWFirewallActivity| summarize count() by DeviceName = Computer ), (CommonSecurityLog | where DeviceVendor == "Barracuda" ), (CommonSecurityLog | where DeviceVendor == "Palo Alto Networks" | where isnotempty(DeviceName) | summarize count() by DeviceVendor, DeviceName) // show devices found | summarize count() by DeviceName , DeviceVendor

I only have Palo Alto data (so the above line for "Palo Alto" is the most accurate). The sample output looks like
- Ankur1460
  Copper Contributor
  Dec 07, 2021
  CliveWatson
  Something related to this, I am trying to build a query where I could get an alert when excessive number of requests going out through firewall to hit an untrusted zone and since the destination we found to be malicious we have already listed it in our denial rule in the firewall. So the status in the firewall logs shows Drop.
  One thing I figured out is to create a watchlist, add all the IP address that we have added in our firewall as untrusted zones and filter out commonsecuritylogs with Devicevendor and watchlist. Then summarize it. Please suggest if I am missing something which I believe I am, or any better workaround to my ask.