topic How can I stack the rows vertically while preserving the original columns in Splunk Search

How can I stack the rows vertically while preserving the original columns

rar0 — Thu, 16 May 2024 15:06:45 GMT

I have a search that returns the following table (after transpose):

column	row 1	row 2
search_name	UC-315	UC-231
ID	7zAt/7	5Dfxdf
Time	13:27:17	09:17:09

And I need it to look like this:

column	new_row
search_name	UC-315
ID	7zAt/7
Time	13:27:17
search_name	UC-231
ID	5Dfxdf
Time	09:17:09

This should work independently of the amount of rows.
I've tried using mvexpand, and streamstats but without any luck.

Re: How can I stack the rows vertically while preserving the original columns

ITWhisperer — Thu, 16 May 2024 16:59:03 GMT

Assuming you can still be dependent on column names, you should go back to pre-transpose and add the following

Re: How can I stack the rows vertically while preserving the original columns

yuanliu — Fri, 17 May 2024 00:04:04 GMT

As always, the answer depends a lot on data characteristics and the real problem you are trying to solve. Maybe you can explain why the second look, which is highly unconventional, is more desirable? Is it safe to say that search_name, ID, and Time are a triplet that should be treated as a unit? In that case, wouldn't this form be more human friendly?

Time	search_name	ID
13:27:17	UC-315	7zAt/7
13:27:17	UC-231	5Dfxdf

(This, of course is the default time series aka Splunky presentation.)

Re: How can I stack the rows vertically while preserving the original columns

rar0 — Tue, 21 May 2024 09:51:18 GMT

Thanks for your fast responses.

Some more context: For incidence response, we want to generate a text block with all notables below each other where a security analyst can write his conclusion below every notable, like this:

* Short-lived group membership ID: F3y4IS Time: 05/14/2024 11:12:28 Destination: xyz Source User: abc User / Group: def Destination Group: ghi --> * Usage of Default Account "administrator" ID: L32op/, WTBxMy Date: 05/17/2024 20:39:04 Destination: xyz Source User: abc User / Group: def Destination Group: ghi --> * Malware detected ...

For this, I already wrote the following query to get the current notables to analyse:

If I transpose this, I get the first table I posted. In the macro `text_block_fields_default` are interesting fields to include in the report like "action, app, dest, src, etc". So the solution shouldn't be dependent on column names.

I already have this query which generates the text block for 1 specific notable:

`notable` | search event_id="BAAAD325-8391-4075-81A2-AB145A1FA2FB@@notable@@80497c055c45b92d73bd74e700c1b6f9" | lookup notable_xref_lookup event_id OUTPUT xref_id AS ID | table search_name ID _time `text_block_fields_default` | `ctime(_time)` | rename _time as time | transpose include_empty=False column_name=text_block | eval row 1=mvjoin('row 1',",") | eval text_block=if(text_block="search_name","* ",text_block.": "),text_block=replace(text_block,"(.{21}).*","\1") | eval text_block=text_block . 'row 1' | append [| makeresults | eval text_block="--> " ] | table text_block

Thanks in advance for your help!

Re: How can I stack the rows vertically while preserving the original columns

yuanliu — Wed, 22 May 2024 06:50:25 GMT

This is an interesting use case. Here is an idea based on @ITWhisperer's prototype.

`notable` | search urgency IN ("high", "critical") status_label IN ("Unassigned", "New", "In Progress") NOT `suppression` | lookup notable_xref_lookup event_id OUTPUT xref_id AS ID | foreach search_name ID _time `text_block_fields_default` [eval row = mvappend(row, json_object("name", "<<FIELD>>", "value", <<FIELD>>))] | table row | mvexpand row | eval column = json_extract(row, "name"), new_row = json_extract(row, "value") | fields - row

(JSON functions requires 8.1 or higher. The same can be achieved using the traditional string concatenation method.) Using this mock data

search_name	ID	_time	bar	foo
UC-315	7zAt/7	13:27:17	bar1	foo1
UC-231	5Dfxdf	13:27:17	bar2	foo2

output will be: (bar, foo, of course, represents `text_block_fields_default`)

column	new_row
search_name	UC-315
ID	7zAt/7
_time	13:27:17
bar	bar1
foo	foo1
search_name	UC-231
ID	5Dfxdf
_time	13:27:17
bar	bar2
foo	foo2

Here is the full emulation

| makeresults format=csv data="search_name, ID, _time, foo, bar UC-315, 7zAt/7, 13:27:17, foo1, bar1 UC-231, 5Dfxdf, 13:27:17, foo2, bar2" | table search_name ID _time * ``` the above emulates `notable` | search urgency IN ("high", "critical") status_label IN ("Unassigned", "New", "In Progress") NOT `suppression` | lookup notable_xref_lookup event_id OUTPUT xref_id AS ID ``` | foreach search_name ID _time * ``` * represents `text_block_fields_default` ``` [eval row = mvappend(row, json_object("name", "<<FIELD>>", "value", <<FIELD>>))] | table row | mvexpand row | eval column = json_extract(row, "name"), new_row = json_extract(row, "value") | fields - row