Forum Discussion
KQL for devices integrated
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
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.
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 descGo 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
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 FirewallWindowsFirewall
| summarize count() by FirewallAction//BarracudaCGFWFirewallActivity//Barracuda WAFCommonSecurityLog
| 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 , DeviceVendorI only have Palo Alto data (so the above line for "Palo Alto" is the most accurate). The sample output looks like
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.
