Forum Discussion
tipper1510
Mar 22, 2023Brass Contributor
KQL to show missing or added devices between a time period
Hi,
Currently using the following kql on various tables to check whether the number of devices has changed between the current week and the previous.
Syslog
| summarize count_ = dcount(Computer) by bin(TimeGenerated, 7d)
| order by TimeGenerated asc
| serialize
| extend Type = "syslog"
| extend changeInCount = count_ - prev(count_,1)
| extend changeInPct = (changeInCount * 100) / prev(count_,1)
Has anyone done something similar but where a difference is found the device name(s) can be shown...
Any help with this would be much appreciated.
Regards,
Tim
- GBushey
Microsoft
Not quite clear what you are asking. Are you trying to find computers that show up now that were not showing up 7 days ago?- tipper1510Brass ContributorHi Gary,
Yes, so if the counts are different then to be able to show the device(s) that have either disappeared or been added in the space of two weeks.
Thanks,
Tim- Clive_WatsonBronze Contributor
tipper1510 The other way would be to just show the new Computers not seen before (added)
let previousComputers = Syslog | where TimeGenerated between (ago(7d) .. ago(1d)) | distinct Computer; Syslog | where TimeGenerated > ago(1d) | where Computer !in(previousComputers)
Then as a rough example, you can do the counts for old vs. added - there are other ways to do this but I kept it simple ( I hope).
let previousComputersCount = toscalar( Syslog | where TimeGenerated between (ago(7d) .. ago(1d)) | summarize dcount(Computer)); let previousComputers = Syslog | where TimeGenerated between (ago(7d) .. ago(1d)) | distinct Computer; Syslog | where TimeGenerated > ago(1d) | where Computer !in(previousComputers) | summarize addedCount_ = dcount(Computer), addedComputers=make_set(Computer), previousComputersCount=any(previousComputersCount) // now do your percent calc