KQL for devices integrated

Question

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

clivewatson · Answer

MiteshAgrawal&nbsp;
&nbsp;
What do you mean by devices and which logs are you collecting?&nbsp; This example could get all the unique Computer names in the Heartbeat Table.&nbsp;&nbsp;
&nbsp;
Go to Log Analytics and run query

count_Computer

32

&nbsp;
Or from All Tables (I filtered on just top 10 by results)
&nbsp;
Go to Log Analytics and run query

TableName
dcount_Computer

Operation
33

Perf
33

Heartbeat
32

InsightsMetrics
32

ConfigurationData
32

Update
31

ProtectionStatus
30

SecurityBaseline
25

SecurityBaselineSummary
25

ConfigurationChange
15

&nbsp;
or
&nbsp;
Go to Log Analytics and run query

union withsource = TableName *
| distinct Computer
| where isnotempty(Computer)
| summarize by Computer
&nbsp;

miteshagrawal · Answer

Hi CliveWatson,&nbsp;Appreciate your quick reply. I guess my requirement wasn't clear.&nbsp;I want to know the devices sending logs to Azure Sentinel. We have Windows servers, Syslog devices (Firewalls, WAF, etc.), Linux servers, AV, etc.&nbsp;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 · Answer

MiteshAgrawal&nbsp;
&nbsp;
For Servers - Windows or Linux, use this but please adjust the top two lines to suit your own criteria&nbsp;
let timeRangeQuery = 1h;
let UnhealthyCriteria = 1m;
Heartbeat
| where TimeGenerated &gt; startofday(ago(timeRangeQuery))
| summarize LastHeartbeat = max(TimeGenerated) by Computer, OSType, OSName
| extend State = iff(LastHeartbeat &lt; 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

&nbsp;
&nbsp;
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

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

clivewatson · Answer

MiteshAgrawal&nbsp;
&nbsp;
Here are some Tables for the other data sets you require.&nbsp; 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.&nbsp; This isn't a full list...you will need to check your own data.
&nbsp;
// find Azure Firewalls&nbsp;

AzureDiagnostics&nbsp;
|&nbsp;where&nbsp;ResourceType&nbsp;==&nbsp;"AZUREFIREWALLS"&nbsp;
&nbsp;
//Windows Firewall
WindowsFirewall| summarize count() by FirewallAction
&nbsp;
//Barracuda

CGFWFirewallActivity
&nbsp;
//Barracuda WAF&nbsp;
CommonSecurityLog​ | where DeviceVendor == "Barracuda"
&nbsp;
//CommonSecurityLog​ | where DeviceVendor == "Check Point"
&nbsp;
CommonSecurityLog​ | where DeviceVendor == "Cisco"| where DeviceProduct == "ASA"
&nbsp;
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

&nbsp;
I only have Palo Alto data (so the above line for "Palo Alto" is the most accurate). The sample output looks like
&nbsp;

ankur1460 · Answer

CliveWatsonSomething 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.

Forum Discussion

KQL for devices integrated

6 Replies

Resources

TableName	dcount_Computer
Operation	33
Perf	33
Heartbeat	32
InsightsMetrics	32
ConfigurationData	32
Update	31
ProtectionStatus	30
SecurityBaseline	25
SecurityBaselineSummary	25
ConfigurationChange	15

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