Deployment Architecture

How to split single column into muliple columns and place them as adjecent colums?

DarshanBK
Explorer

Hi All,

 

I have data as below, my requirement is to append/merge both the columns and then for each year split the column into multiple and place the details for one year field value adjacent to the previous one.

 

Merging part can be taken care, i need the solution for splitting columns part.

Can someone please help how to achieve this.

 

SPL:

 

|rex field=_raw "(?<Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})"
| rex field=_raw "\w+:\s(?<Year>(\d+))\sQ"
| rex field=_raw "\d+\s(?<Quarter>(Q\d)):"
|rex field=_raw "\s+(?<Count>(\d+M))"
|table Year,Quarter,Count
|strcat Quarter " " Count Task
|fields - Quarter Count
|strcat Year " " Task Ask
|fields - Year Task

DarshanBK_2-1668609170962.png

 

Below is the expected result.

2021           2022

Q4 2m         Q4 5m

Q3 1m         Q3 7m

Q2 2m         Q2 8m

Q1 0m         Q1 5m

Labels (3)
0 Karma

johnhuang
Motivator
| eval Count=LOWER(Count)
| fillnull value="0m" Count
| eval {Year}=Quarter." ".Count
| stats max(20*) AS 20* BY Quarter
| sort -Quarter
| table 20*

 

With test data:

| makeresults | eval data="2021,Q4,2m;2021,Q3,1m;2021,Q2,2m;2021,Q1,;2022,Q4,5m;2022,Q3,7m;2022,Q2,8m;2022,Q1,5m"
| eval data=split(data, ";") | mvexpand data
| rex field=data "(?<Year>\d+)\,(?<Quarter>\w+)\,(?<Count>\w+)?"
| table Year Quarter Count
| eval Count=LOWER(Count)
| fillnull value="0m" Count
| eval {Year}=Quarter." ".Count
| stats max(20*) AS 20* BY Quarter
| sort -Quarter
| table 20*

 

0 Karma

PaulPanther
Builder

Try it with  command xyseries like

 

| table _time,Year,Task
| xyseries Task Year Task
| fields - Task

 

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...