KQL for devices integrated

Brass Contributor

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

6 Replies

@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
 

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.

@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  

@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

 

clipboard_image_0.png

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

@MiteshAgrawal  

Hello if i may ask did you manage to create it?

I am looking for something similar as well and i am not able to do it.

I want to collect all the devices list and then create an alert when a device is not reporting .

 

Thanks in advance