latest result

[SN1]

hello i have a search and i want only latest result of this search . ok so the problem is for 1 DeviceName there are multiple SensorHealthState  , now it was Inactive but latest event shows that the device is active now . But this search shows Inactive . How can I get latest result .

index=endpoint_defender source="AdvancedHunting-DeviceInfo"
| dedup DeviceName
| search DeviceType=Workstation OR DeviceType= Server
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=Timestamp "(?<timeval>\d{4}-\d{2}-\d{2})"
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| rename code as 3-Letter-Code
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUTNEW "Company Code"
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT "Company Code" as 4LetCode
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT Region as Region
| eval Region=mvindex('Region',0) , "4LetCode"=mvindex('4LetCode',0)
| rename "3-Letter-Code" as CC
| 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

[PrewinThomas]

@SN1 

stats latest(SensorHealthState) by DeviceName is far more efficient than dedup, especially when you're only interested in the most recent state. It reduces the dataset early and avoids unnecessary processing.

Try below,

index=endpoint_defender source="AdvancedHunting-DeviceInfo" (DeviceType=Workstation OR DeviceType=Server)
| stats latest(SensorHealthState) as SensorHealthState latest(_time) as _time by DeviceName
| search SensorHealthState IN ("active", "Inactive", "Misconfigured", "Impaired communications", "No sensor data")
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| lookup lkp-GlobalIpRange.csv code OUTPUT "Company Code", Region
| eval Region=mvindex(Region, 0)
| search DeviceName="bie-n1690.emea.duerr.int"
| table Hostname code "Company Code" DeviceName _time Region SensorHealthState

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[PrewinThomas]

@SN1 

stats latest(SensorHealthState) by DeviceName is efficient than dedup, especially when you're only interested in the most recent state. It reduces the dataset early and avoids unnecessary processing.

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[PrewinThomas]

@SN1 

stats latest(SensorHealthState) by DeviceName is far efficient than dedup, especially when you're only interested in the most recent state. It reduces the dataset early and avoids unnecessary processing.

Try below,

index=endpoint_defender source="AdvancedHunting-DeviceInfo" (DeviceType=Workstation OR DeviceType=Server)
| stats latest(SensorHealthState) as SensorHealthState latest(_time) as _time by DeviceName
| search SensorHealthState IN ("active", "Inactive", "Misconfigured", "Impaired communications", "No sensor data")
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| lookup lkp-GlobalIpRange.csv code OUTPUT "Company Code", Region
| eval Region=mvindex(Region, 0)
| search DeviceName="bie-n1690.emea.duerr.int"
| table Hostname code "Company Code" DeviceName _time Region SensorHealthState

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[PrewinThomas]

@SN1 

stats latest(SensorHealthState) by DeviceName is far more efficient than dedup, especially when you're only interested in the most recent state. It reduces the dataset early and avoids unnecessary processing.

Try below,

index=endpoint_defender source="AdvancedHunting-DeviceInfo" (DeviceType=Workstation OR DeviceType=Server)
| stats latest(SensorHealthState) as SensorHealthState latest(_time) as _time by DeviceName
| search SensorHealthState IN ("active", "Inactive", "Misconfigured", "Impaired communications", "No sensor data")
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| lookup lkp-GlobalIpRange.csv code OUTPUT "Company Code", Region
| eval Region=mvindex(Region, 0)
| search DeviceName="bie-n1690.emea.duerr.int"
| table Hostname code "Company Code" DeviceName _time Region SensorHealthState

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[bowesmana]

@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

[tej57]

Hey @SN1,

What @PickleRick said is correct. You'll be receiving the latest result only since you're using dedup. However, since it is an expensive command, you can use transforming command like stats as well to fetch the latest results. Your query should look something like below:

index=endpoint_defender source="AdvancedHunting-DeviceInfo"
| search (DeviceType=Workstation OR DeviceType= Server) AND DeviceName="bie-n1690.emea.duerr.int"
| search SensorHealthState = "active" OR  SensorHealthState = "Inactive" OR SensorHealthState = "Misconfigured" OR SensorHealthState = "Impaired communications" OR SensorHealthState = "No sensor data"
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=Timestamp "(?<timeval>\d{4}-\d{2}-\d{2})"
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| rename code as 3-Letter-Code
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUTNEW "Company Code"
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT "Company Code" as 4LetCode
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT Region as Region
| eval Region=mvindex('Region',0) , "4LetCode"=mvindex('4LetCode',0)
| rename "3-Letter-Code" as CC
| stats latest(SensorHealthState) as latest_SensorHealthState by DeviceName Region ...

The latest function will always fetch the latest value of the field passed as an argument on the basis of time. You can add the fields that you want to group the results in the by clause. Hope this helps you optimize your query.

Thanks,
Tejas.

---
If the above solution helps, an upvote is appreciated..!!

[PickleRick]

There are two things here which caught my attention.

1. You're doing some operations on your data (which prevent Splunk from auto-optimizing your search) and then way down the road you add | search DeviceName=something

If you add this condition to the initial search you will be processing just a small subset of your events, not the whole lot.

2. The use of dedup. Are you absolutely sure you want to use this command? It keeps just first result with given field(s).

[SN1]

you can ignore search= command and the reason i am using dedup is because there are large number of devices . like 1 device has like 20 25 events . 

[PickleRick]

Yes, but do you know what dedup does? With a search like that you are getting only a latest (since Splunk returns events in reverse chronological order) event for each DeviceName. So that should already be pretty much what you wanted.

[PrewinThomas]

@SN1 

You can sort _time and dedup device or you can use stats last() also,

Eg:

index=endpoint_defender source="AdvancedHunting-DeviceInfo"
DeviceType=Workstation OR DeviceType=Server
SensorHealthState IN ("active", "Inactive", "Misconfigured", "Impaired communications", "No sensor data")
DeviceName="bie-n1690.emea.duerr.int"
| sort 0 - _time
| dedup DeviceName
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=Timestamp "(?<timeval>\d{4}-\d{2}-\d{2})"
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| rename code as 3-Letter-Code
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUTNEW "Company Code"
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT "Company Code" as 4LetCode
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT Region as Region
| eval Region=mvindex(Region, 0), "4LetCode"=mvindex('4LetCode', 0)
| rename "3-Letter-Code" as CC
| table Hostname CC 4LetCode DeviceName timeval Region SensorHealthState

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[PrewinThomas]

@SN1 

You can sort _time and dedup device or you can use stats last() also,

Eg:

index=endpoint_defender source="AdvancedHunting-DeviceInfo"
DeviceType=Workstation OR DeviceType=Server
SensorHealthState IN ("active", "Inactive", "Misconfigured", "Impaired communications", "No sensor data")
DeviceName="bie-n1690.emea.duerr.int"
| sort 0 - _time
| dedup DeviceName
| rex field=DeviceDynamicTags "\"(?<code>(?!/LINUX)[A-Z]+)\""
| rex field=Timestamp "(?<timeval>\d{4}-\d{2}-\d{2})"
| rex field=DeviceName "^(?<Hostname>[^.]+)"
| rename code as 3-Letter-Code
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUTNEW "Company Code"
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT "Company Code" as 4LetCode
| lookup lkp-GlobalIpRange.csv 3-Letter-Code OUTPUT Region as Region
| eval Region=mvindex(Region, 0), "4LetCode"=mvindex('4LetCode', 0)
| rename "3-Letter-Code" as CC
| table Hostname CC 4LetCode DeviceName timeval Region SensorHealthState

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!