topic Re: Multiple Rows into one row with primary key in Splunk Search

Multiple Rows into one row with primary key

jimhill — Sat, 24 Jul 2021 10:44:59 GMT

Hi,

I have data that looks like this (as you can see user_id 9 has filled numerous rows). This is just a csv ingested and being searched via lookup

user_id	meta_key	meta_value
9	nickname	341
9	first_name	Gilda
9	last_name	Lilia
9	description
9	rich_editing	TRUE
9	syntax_highlighting	TRUE
9	comment_shortcuts	FALSE
9	bz_last_active	202024300
9	_sd_last_login	2251532
9	_jackqueline_persistent_cart_1	a:1:{s:4:"cart";a:0:{}}
9	_order_count	0
9	new_users_id	XM00360
9	antonetta	a:0:{}
9	rank_on_departure	TAD 0fr Class
9	phone_number	12003601
9	add_love	1/120 CARSON ROAD
9	last_name_01	Lashawnda
9	christina_code	1100
9	last_name_01_05	Wendolyn
9	birth_date	05/00/0451
9	country	Stephania
9	join_date	13/02/2003
9	Date_left	1/05/2010
9	full_name	gilda lilia
9	email	gilda_lilia@outlook.com

I really want the output to look like this, where the items in red, come on a single row linked to the unique user_id. At this stage i have over 3600 unique users, and on average, there are about 40 rows per user.

user_id	first_name	last_name	new_users_id	rank_on_departure	add_love	last_name_01	christina_code	last_name_01_05	country	email
9	Gilda	Lilia	XM00360	TAD 0fr Class	1/120 CARSON ROAD	Lashawnda	1100	Wendolyn	Stephania	gilda_lilia@outlook.com

Re: Multiple Rows into one row with primary key

ITWhisperer — Sat, 24 Jul 2021 11:14:27 GMT

| makeresults | eval _raw="user_id meta_key meta_value 9 nickname 341 9 first_name Gilda 9 last_name Lilia 9 description 9 rich_editing TRUE 9 syntax_highlighting TRUE 9 comment_shortcuts FALSE 9 bz_last_active 202024300 9 _sd_last_login 2251532 9 _jackqueline_persistent_cart_1 a:1:{s:4:\"cart\";a:0:{}} 9 _order_count 0 9 new_users_id XM00360 9 antonetta a:0:{} 9 rank_on_departure TAD 0fr Class 9 phone_number 12003601 9 add_love 1/120 CARSON ROAD 9 last_name_01 Lashawnda 9 christina_code 1100 9 last_name_01_05 Wendolyn 9 birth_date 05/00/0451 9 country Stephania 9 join_date 13/02/2003 9 Date_left 1/05/2010 9 full_name gilda lilia 9 email gilda_lilia@outlook.com" | multikv forceheader=1 | fields - linecount | where match(meta_key, "first_name|last_name|new_users_id|rank_on_departure|add_love|last_name_01|christina_code|last_name_01_05|country|email") | eval {meta_key}=meta_value | fields - meta_key meta_value _raw | stats values(*) as * by user_id

Re: Multiple Rows into one row with primary key

jimhill — Sat, 24 Jul 2021 22:07:46 GMT

Thank you so much. Thats perfect.

Now how do I make it so that I can replicate it for all other users and bring it back in one spreadsheet. Is this achievable?

Thanks again

Re: Multiple Rows into one row with primary key

ITWhisperer — Sat, 24 Jul 2021 22:41:44 GMT

The first part (before the blank lines) just sets up your example data - the piece after the blank lines does the work - notice the by user_id on the stats command, this repeats the process for all your users.

Re: Multiple Rows into one row with primary key

jimhill — Sat, 24 Jul 2021 23:26:53 GMT

Thank you so much. Extremely helpful