Splunk Search

How to convert specific columns to rows in Search SPL?

Jagadeesh2022
Path Finder

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

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

@gcusello

Thank you so much. You are genius 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Jagadeesh2022,

you are too good: let's not exaggerate!

see next time!

Ciao and happy splunking

Giuseppe

0 Karma

Jagadeesh2022
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...