How to find field value, based on where condition, and assign to variable?

[sarge338]

Good Morning!

I rarely get to dabble in SPL, and as such, some (probably simple) things stump me.  That is what brought me here today.

I have a scenario in which I need to pull SYSLOG events from a series of machines that all report the field names.  One of those machines is the authoritative source of values, which all of the other systems should have.  As an example, I have 3 machines... M1, M2, M3, and each machine reports three field/value pairs... sync-timestamp, version-number, machine-name.

I need to compare the sync-timestamp of M1 with the sync-timestamp of the other two machines.  My idea is to assign the "sync-timestamp value WHERE computer-name=M1" to a variable by which to compare the other two machines' values.  I intend to use this report to ultimately create an alert, so we know if machines are not syncing properly.

I just cannot figure out the syntax to make this happen.  Can anyone provide some guidance on this?

Thank you in advance!

[sarge338]

@gcusello  Thank you for all of your help!  I apologize for the lag in responding to you.  I have been very busy on another project.  Your suggestion was spot-on, though, so I wanted to be sure to thank you for your assistance.

[gcusello]

Hi @sarge338 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[bowesmana]

You need to collect the sync-timestamp field from M1 to the other events and then compare, which can be done using eventstats, as in this example.

| makeresults  count=3
| streamstats c
| eval machine-name="M".c
| eval sync-timestamp=now() - 300 + (if(c=3,60,0)), version-number="1.2.3"
``` Data setup above where M3 is 1 minute in front of M1 and M2 ```

``` Collect the master timestamp to the other events ```
| eventstats values(eval(if('machine-name'="M1", 'sync-timestamp', null()))) as m1-timestamp

| where 'sync-timestamp'!='m1-timestamp'

[sarge338]

Hello @gcusello ,

The data is already being ingested into Splunk, and if I look at events from the last 10 minutes (index="my-index" earliest=-10m@m latest=@m), the syslog messages from ALL machines are showing up as a single event timestamp.  So, I need to compare the sync-time field, which is is epoch, rather than the _time value assigned by Splunk.

Thank you for your assistance.

[gcusello]

Hi @sarge338 ,

the solution is the same using the sync-time field instead _time, being in epochtime it's easier to manage.

As I said you have only to define if you want the exact sync-time or a period (e.g. 5 minutes) and what's the rule to apply filter.

index=your_index host IN (M1, M2, M3)
| stats dc(host) AS host_count BY "time-sync"
| where host_count=3

if the timestamps must be exactly the same, if instead they must be similar (e.g. 5 minutes ranges), you could run:

index=your_index host IN (M1, M2, M3)
| bin span=5m "time-sync"
| stats dc(host) AS host_count BY "time-sync"
| where host_count=3

If possible, don't use the minus char "-", but understand char "_", because Splunk read it as the minus operator, so yu have to use quotes.

Ciao.

Giuseppe

[gcusello · Accepted solution]

Hi @sarge338 ,

let me understand: you have three syslog sources to ingest in Splunk, and then you whould compare events from the three sources, is it correct?

if this is your requirements you should follow these preliminary steps to ingest data (if you already ingested data jump these steps):

• identify the data type (technology, model, type of data),
• identify IP address, protocol and port of each source,
• identify the correct Add-on to parse these data source,
• put in listening your heavy forwarder on the defined ports and protocols,
• configure your sources to send logs to the heavy forwarder using the defined protocol and port,
• configure the input on heavy forwarder assigning the correct sourcetype (based on the choosed Add-On) and the correct index,
• the host is automatically assigned using the IP address.

then in your Splunk you can run a search like the following (Not having any information on data sources I cannot be detailed and I could be vague):

index=your_index host IN (M1, M2, M3)
| stats dc(host) AS host_count BY _time
| where host_count=3

if the timestamps must be exactly the same, if instead they must be similar (e.g. 5 minutes ranges), you could run:

index=your_index host IN (M1, M2, M3)
| bin span=5m _time
| stats dc(host) AS host_count BY _time
| where host_count=3

In this way you have the events with the same timestamp in all the hosts, if you want a different condition, you have to modify the final where command.

Ciao.

Giuseppe