How to filter fields (Stats: Value, List, Latest)

[JoshuaJohn]

I have 5 fields of data I want in a stats table, some of these fields have more than 1 value inside and they all correlate with each other.

Mac_Address (1 mac per device)
Apps (4 apps per device)
Compliance (1 compliance statuses for each of the apps (so 4))
LastCheckIn (I want the latest check-in, so 1)
Firmware (1 firmware level per device)

I wrote this query:

|stats list(Apps) as AllApps list(Compliance) as AllComp latest(LastCheckIn) as LatestCheckIn latest(Firmware) as latestFW by Mac_Address

The problem is there are duplicates in AllApps (Due to the nature of logs coming in etc.) I tried using values on apps, but then compliance is wrong (No longer paired correctly). Then tried making both app and comp values instead of list but then AllComp only provides 3 options (Compliant, non-compliant, error) and is not correlated to the apps

I could filter by moving compliance to the Mac_Address area but I do not want to have my table split by compliance level, I want it exclusively split by Mac_Address.

Ideal:

Any ideas? Thank you!

[somesoni2]

Run a |dedup Mac_Address Apps Compliance before your stats with list function.

[493669]

try this:

...|makemv Apps|mvexpand Apps|stats values(Apps) as AllApps values(Compliance) as AllComp latest(LastCheckIn) as LatestCheckIn latest(Firmware) as latestFW by Mac_Address