Splunk Search

Issue with aligning events from three separate source types in a table

beetlegeuse
Path Finder

I am taking events from three source types (same index; two common fields present across all three) and creating a table with the results. The events are indexed using a "timestamps" field that is present in the raw data (the result of an API call to a monitoring tool and a subsequent JSON payload retrieval of synthetic test metrics; the value is in epoch time and pushed into _time using a transform aligned with the source types).

Here's the query I'm using:

index=smoketest_* sourcetype=smoketest_json_dyn_result OR sourcetype=smoketest_json_dyn_duration OR sourcetype=smoketest_json_dyn_statuscode | rename dt.entity.synthetic_location AS synLoc, dt.entity.http_check AS httpCheck
| stats values(*) AS * by httpCheck, synLoc, _time | rename "responseTime{}" AS "Response Time (ms)" | table _time, synLoc, httpCheck, status, "Response Time (ms)", "Status code"

The common fields found in all three source types are "synLoc" and "httpCheck". 95% of the time, I get the desired result pictured here (requested fields from all three sourcetypes align as a single row on the table):

beetlegeuse_1-1640617859391.png

In this example, you can see the results of two unique tests (executing every five minutes, over a 15 minute period). Since the events being grabbed from the three source types all have the same _time value, this works as expected.

If, however, one or two of the source types have events with a _time value that does not match the others, this happens:

beetlegeuse_2-1640618248327.png

Again, there are two unique tests represented here. However, note that one row reflects a value from one source type at 10:01 while the two values from the other two source types are on a separate row at 10:02. Ideally, all three values should be on the same row (much like the 10:06 and 10:11 entries).

How can I alter my search query to account for this behavior?

Labels (2)
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Rounding off the timestamps may help.  Try this query.

index=smoketest_* sourcetype=smoketest_json_dyn_result OR sourcetype=smoketest_json_dyn_duration OR sourcetype=smoketest_json_dyn_statuscode 
| rename dt.entity.synthetic_location AS synLoc, dt.entity.http_check AS httpCheck
| bin span=5m _time
| stats values(*) AS * by httpCheck, synLoc, _time 
| rename "responseTime{}" AS "Response Time (ms)" 
| table _time, synLoc, httpCheck, status, "Response Time (ms)", "Status code"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

tscroggins
Influencer

@beetlegeuse 

If you're less concerned with the time than the result and your tests execute every five minutes, you can bin the time values into five minute buckets:

index=smoketest_* sourcetype=smoketest_json_dyn_result OR sourcetype=smoketest_json_dyn_duration OR sourcetype=smoketest_json_dyn_statuscode | rename dt.entity.synthetic_location AS synLoc, dt.entity.http_check AS httpCheck
| bin _time span=5m
| stats values(*) AS * by httpCheck, synLoc, _time
| rename "responseTime{}" AS "Response Time (ms)"
| table _time, synLoc, httpCheck, status, "Response Time (ms)", "Status code"

This assumes correlated result, duration, and status events do not overlap five-minute windows.

If the events always arrive in the same sequence, you can also fall back to the transaction command:

index=smoketest_* sourcetype=smoketest_json_dyn_result OR sourcetype=smoketest_json_dyn_duration OR sourcetype=smoketest_json_dyn_statuscode
| rename dt.entity.synthetic_location AS synLoc, dt.entity.http_check AS httpCheck
| transaction syncLoc httpCheck startswith=eval(sourcetype=="smoketest_json_dyn_result") endswith=eval(sourcetype=="smoketest_json_dyn_duration") ```or whichever event is last```
| stats values(*) AS * by httpCheck, synLoc, _time
| rename "responseTime{}" AS "Response Time (ms)"
| table _time, synLoc, httpCheck, status, "Response Time (ms)", "Status code"

richgalloway
SplunkTrust
SplunkTrust

Rounding off the timestamps may help.  Try this query.

index=smoketest_* sourcetype=smoketest_json_dyn_result OR sourcetype=smoketest_json_dyn_duration OR sourcetype=smoketest_json_dyn_statuscode 
| rename dt.entity.synthetic_location AS synLoc, dt.entity.http_check AS httpCheck
| bin span=5m _time
| stats values(*) AS * by httpCheck, synLoc, _time 
| rename "responseTime{}" AS "Response Time (ms)" 
| table _time, synLoc, httpCheck, status, "Response Time (ms)", "Status code"
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...