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

[beetlegeuse]

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):

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:

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?

[tscroggins]

@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 · Accepted solution]

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"