KQL for devices integrated

[MiteshAgrawal]

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 Agrawal

[#2]

@MiteshAgrawal 

What do you mean by devices and which logs are you collecting?  This example could get all the unique Computer names in the Heartbeat Table.  

Go to Log Analytics and run query

count_Computer
32

Or from All Tables (I filtered on just top 10 by results)

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

or

Go to Log Analytics and run query

union withsource = TableName *

| distinct Computer

| where isnotempty(Computer)

| summarize by Computer

[#3]

Hi @Clive Watson,

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.

[#4]

@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  

[#5]

@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