0

I am trying to get the servers which did not report for more than 24 hours in ATP and running the following, but getting incorrect data:

DeviceInfo
| where isnotnull(OSBuild) 
| summarize last_seen = max(Timestamp) by  DeviceId, DeviceName, OSPlatform, OSBuild
| project last_seen, DeviceId, DeviceName, OSPlatform, OSBuild
| where OSPlatform contains "server" //added this line to filter for servers only
| where last_seen > ago(24h)
| sort by last_seen asc

Result of query shows not only devices with yesterday's timestamp, but today as well... Running the query in advanced hunting of Defender ATP.

enter image description here

1nkotb
  • 7
  • 9

1 Answers1

0

You should change:

| where last_seen > ago(24h)

To

| where last_seen < ago(24h)

Slavik N
  • 4,705
  • 17
  • 23
  • getting a similar result...I would like to narrow it down to devices not seen for more than 24 hours – 1nkotb Mar 09 '21 at 20:57
  • That's too strange... When you have a filter `| where last_seen < ago(24h)` you're only supposed to see records in which the value of `last_seen` is smaller than `ago(24h)`. Are you seeing something else..? – Slavik N Mar 10 '21 at 06:40
  • just to make sure we are on the same page... form the drop down on the right hand side, I have selected 7 days. run the query and getting devices with last_seen as old as march 3 and new as march 5 – 1nkotb Mar 10 '21 at 19:37
  • So the query seems to work correctly - indeed the value of last_seen is older than ago(1d). So I don't understand what's missing... – Slavik N Mar 10 '21 at 20:42
  • Hi @Slavik N it seems like the results are coming now, but the issue I am facing is the timestamp, which is not correct. I am getting a Timestamp in my results which would indicate that my server is not reporting in ATP, but when actually going on the specific server, everything is fine... I would like to know why such discrepancy...Timezone is setup to local. – 1nkotb Mar 11 '21 at 16:27
  • Well, that question should go to ATP... As for ADX/Kusto - the query works fine. The only mistake you had in your query is the usage of '>' instead of '<', as I indicated in my answer. If this helps, please accept the answer. Thanks. – Slavik N Mar 11 '21 at 20:44