@SN1 There is some other stuff going on in your search that is odd - 3 times the same lookup and the renaming of code and CC is unnecessary. You can distill this down and optimise it by doing the ...
See more...
@SN1 There is some other stuff going on in your search that is odd - 3 times the same lookup and the renaming of code and CC is unnecessary. You can distill this down and optimise it by doing the stats latest early in the piece instead of dedup, which is not a command that should be used unless it's really needed, then doing the remainder of rex/eval/lookup tasks on the small subset of data. Your device type search should be done up top - The other searches are looking at latest state so are in the right place. But even this example doesn't deal with your "Company Code" field, which you've used OUTPUTNEW for, but then do not use, so you can probably junk the coalesce there, but without knowing your data, it's hard to say... index=endpoint_defender source="AdvancedHunting-DeviceInfo"
DeviceType IN ("Workstation","Server")
``` These are the fields you want ```
| fields DeviceType DeviceName SensorHealthState Timestamp DeviceDynamicTags
``` So get the latest of each field for the device ```
| stats latest(*) as * by DeviceName
``` Extract the fields you want ```
| rex field=DeviceDynamicTags "\"(?<CC>(?!/LINUX)[A-Z]+)\""
| rex field=Timestamp "(?<timeval>\d{4}-\d{2}-\d{2})"
| rex field=DeviceName "^(?<Hostname>[^.]+)"
``` Only a single lookup is needed ```
| lookup lkp-GlobalIpRange.csv 3-Letter-Code as CC OUTPUT "Company Code" as 4LetCode Region
``` Your use of OUTPUTNEW is handled this way ```
| eval "Company Code"=coalesce('Company Code', 4LetCode)
| eval Region=mvindex('Region',0) , "4LetCode"=mvindex('4LetCode',0)
| search DeviceName="bie-n1690.emea.duerr.int"
| search SensorHealthState = "active" OR SensorHealthState = "Inactive" OR SensorHealthState = "Misconfigured" OR SensorHealthState = "Impaired communications" OR SensorHealthState = "No sensor data"
| table Hostname CC 4LetCode DeviceName timeval Region SensorHealthState