2

In Splunk, I have a search producing a result table like this:

_time A B C
2022-10-19 09:00:00 A1 B1 C1
2022-10-19 09:00:00 A2 B2 C2
2022-10-19 09:10:20 A3 B3 C3

Now, for each row, I want to run a second search, using the _time value as input parameter.

For above row 1 and 2 (same _time value), the result of the second search would be:

_time D E
2022-10-19 09:00:00 D1 E1

For above row 3, the result of the second search would be:

_time D E
2022-10-19 09:10:20 D3 E3

And now I want to output the results in a common table, like this:

_time A B C D E
2022-10-19 09:00:00 A1 B1 C1 D1 E1
2022-10-19 09:00:00 A2 B2 C2 D1 E1
2022-10-19 09:10:20 A3 B3 C3 D3 E3

I experimented with join, append, map, appendcols and subsearch, but I am struggling both with the row-by-row character of the second search and with pulling to data together into one common table.

For example, appendcols simply tacks one result table onto another, even if they are completely unrelated and differently shaped. Like so:

_time A B C D E
2022-10-19 09:00:00 A1 B1 C1 D1 E1
2022-10-19 09:00:00 A2 B2 C2 - -
2022-10-19 09:10:20 A3 B3 C3 - -

Can anybody please point me into the right direction?

rikinet
  • 93
  • 6
  • What does your data look like? – warren Oct 19 '22 at 15:16
  • 1
    Have you considered [`appendcols`](https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/Appendcols)? – PM 77-1 Oct 19 '22 at 16:48
  • Unless you're binning your `_time` in some manner, grouping by `_time` is going to be *very* problematic - while you can rely on the timestamps of an individual event being consistent, and the timestamps of an individual indexer to be consistent, you ***cannot*** rely on timestamps between senders and receivers to be consistent. Likewise, you may want to correlate event A of sourcetype 1 with event Q of sourcetype 9 - but the events that should be correlated are separated by seconds, minutes, hours, or even days – warren Oct 19 '22 at 17:30
  • @PM77-1, yes I have also considered appendcols as edited in the original question. – rikinet Oct 21 '22 at 11:47

1 Answers1

0

I think a left join will do what you want as long as the times in the subquery are the same as the times in the main query.

Here is a run-anywhere example:

| makeresults
| eval _raw = "
T                    A  B  C
2023-05-03 00:00:01 a1 b1 c1
2023-05-03 00:00:02 a2 b2 c2
2023-05-03 00:00:03 a3 b3 c3
"
| multikv forceheader=1 fields T A B C
| rename T as _time
| table _time A B C

| join _time type=left [
  makeresults
  | eval _raw = "
  T                    D  E
  2023-05-03 00:00:01 d1 e1
  2023-05-03 00:00:02 d1 e1
  2023-05-03 00:00:03 d3 e3
  "
  | multikv forceheader=1 fields T D E
  | rename T as _time
  | table _time D E
]
Jerry Jeremiah
  • 9,045
  • 2
  • 23
  • 32
  • What's the point of assigning `query=T` if `query` isn't used anywhere further down in the search? Besides, your example does not seem to provide the expected results in following cases: a) duplicate values for `T` in first table, b) different row count in first and second table, c) no match for `T` value in second table. – rikinet Mar 13 '23 at 10:37
  • Even more, I cannot use `T` as key to join rows from both tables. I rather need to use `T`(or, in my case above, `_time`) as *input parameter* for a secondary search. That secondary search shall yield exactly one row with the extra fields to be appended. – rikinet Mar 13 '23 at 10:49