How to convert specific columns to rows in Search SPL?

[Jagadeesh2022]

Hi Friends,

I want to convert 2 specific columns to rows and remaining columns should be present.

This is my current SPL:

| inputlookup PG_WHSE_PrIME_TS

| search Server IN ("*")

| rename Site_Name as "Site Name" Name as NAME

| join type=left max=0 NAME

[search index="pg_idx_whse_prod_database" source=Oracle sourcetype=PG_ST_WHSE_DPA_NEW host="*"

| stats latest(PERCENTAGE) as PERCENTAGE by DB_ID PARAMETERNAME1 NAME ]

|table "Site Name" NAME DB_ID PARAMETERNAME1 PERCENTAGE

|sort DB_ID

Site Name	Name	DB_ID	PARAMETERNAME1	PERCENTAGE
Crailsheim	CRL-PRIME-PROD.EU.PG.COM-PROD	24	AUDIT_TBS	81.38
Crailsheim	CRL-PRIME-PROD.EU.PG.COM-PROD	24	DLX_DATA_TS	38.24
Crailsheim	CRL-PRIME-PROD.EU.PG.COM-PROD	24	DLX_INDEX_TS	99.98
Crailsheim	CRL-PRIME-PROD.EU.PG.COM-PROD	24	DPA_TS	95
Bangkok	BKK-PRIME-PROD.AP.PG.COM-PROD	38	AUDIT_TBS	62.62
Bangkok	BKK-PRIME-PROD.AP.PG.COM-PROD	38	DLX_DATA_TS	75.21
Bangkok	BKK-PRIME-PROD.AP.PG.COM-PROD	38	DLX_INDEX_TS	96.24
Bangkok	BKK-PRIME-PROD.AP.PG.COM-PROD	38	DPA_TS	84

[gcusello · Accepted solution]

Hi @Jagadeesh2022,

at first you should use a different approach to this kind of searches using lookup command (that in few words is a left join) instead the join command, then you can put a search as subsearch only if you're sure that you have less than 50,000 results.

Anyway, you could try something like this:

index="pg_idx_whse_prod_database" source=Oracle sourcetype=PG_ST_WHSE_DPA_NEW 
| stats latest(PERCENTAGE) as PERCENTAGE by DB_ID PARAMETERNAME1 NAME
| lookup PG_WHSE_PrIME_TS Name AS NAME
| eval column=Site_Name."|".Name."|"DB_ID
| chart values(PERCENTAGE) AS PERCENTAGE over column BY PARAMETERNAME1
| rex field=column "^(?<Site_Name>[^\|]+)\|(?<Name>[^\|]+)\|(?<DB_ID>.+)"
| table Site_Name Name DB_ID *
| fields - column
| rename Site_Name AS "Site Name"
| sort DB_ID

Ciao.

Giuseppe

[Jagadeesh2022]

@gcusello

Thank you so much. You are genius 🙂

[gcusello]

Hi @Jagadeesh2022,

you are too good: let's not exaggerate!

see next time!

Ciao and happy splunking

Giuseppe

[Jagadeesh2022]

This is my current output. I want to change like below:

@gcusello

Site Name	Name	DB_ID	AUDIT_TBS	DLX_DATA_TS	DLX_INDEX_TS	DPA_TS
Crailsheim	CRL-PRIME-PROD.EU.PG.COM-PROD	24	81.38	38.24	99.98	95
Bangkok	BKK-PRIME-PROD.AP.PG.COM-PROD	38	62.62	75.21	96.24	84