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(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
  • 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
      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

        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