Feb 03 2020 02:00 AM
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 @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.
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