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
Motivator

@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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...