KQL to show missing or added devices between a time period

[tipper1510]

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

[#2]

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?

[#3]

Hi 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

[#4]

@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