Forum Discussion

tipper1510's avatar
tipper1510
Brass Contributor
Mar 22, 2023

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(Com...
monitoring
siem
Workbooks
GBushey's avatar
GBushey
Icon for Microsoft rankMicrosoft
Mar 23, 2023
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?
  • tipper1510's avatar
    tipper1510
    Brass Contributor
    Mar 23, 2023
    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
    • Clive_Watson's avatar
      Clive_Watson
      Bronze Contributor
      Mar 24, 2023

      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

       

Resources