Feb 03 2020
02:00 AM
- last edited on
Apr 08 2022
10:16 AM
by
TechCommunityAP
Feb 03 2020
02:00 AM
- last edited on
Apr 08 2022
10:16 AM
by
TechCommunityAP
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
Feb 03 2020 07:26 AM
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
Feb 03 2020 10:48 PM
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.
Feb 04 2020 01:37 AM
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
Feb 04 2020 11:20 AM
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
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
Dec 07 2021 06:11 AM
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.
May 11 2023 01:50 AM
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