Splunk Search

How to transpose data and create a new row?

kevingo75
New Member

Hi all,

I have a table data looks like:

Time              Type          Count
--------       ----------     --------------
2018/01          A                 10
2018/01          B                  5
2018/02          A                  20
2018/02          B                  10
...                      ...                ...

I want to transpose it and create a new row by B divide by A :

Type     2018/01   2018/02    ...
A          10           20
B          5             10
C          0.5           0.5

I tried | transpose 0 header_field=Time but it does not work.

Tags (2)
0 Karma
1 Solution

harishalipaka
Motivator

hi @kevingo75

|  makeresults |  eval  Time ="2018/01", Type="A", Count=10 |append [|  makeresults |  eval  Time ="2018/01", Type="B", Count=5] |append [|  makeresults |  eval  Time ="2018/02", Type="A", Count=20]|append [|  makeresults |  eval  Time ="2018/02", Type="B", Count=10] |fields - _time |chart values(Count) over Time by Type |eval C=B/A  |transpose header_field=Time
Thanks
Harish

View solution in original post

0 Karma

harishalipaka
Motivator

hi @kevingo75

|  makeresults |  eval  Time ="2018/01", Type="A", Count=10 |append [|  makeresults |  eval  Time ="2018/01", Type="B", Count=5] |append [|  makeresults |  eval  Time ="2018/02", Type="A", Count=20]|append [|  makeresults |  eval  Time ="2018/02", Type="B", Count=10] |fields - _time |chart values(Count) over Time by Type |eval C=B/A  |transpose header_field=Time
Thanks
Harish
0 Karma

kevingo75
New Member

Great answer! Thanks!

0 Karma

niketn
Legend

@kevingo75, instead of transpose try the following chart command on existing table as per your question:

<yourCurrentSearchResultingInFirstTable>
| chart sum(Count) as count by Type Time

Following is a run anywhere search based on the sample data provided in the question (commands from |makeresults till the |table command generate the dummy data as per the question:

| makeresults 
| eval data="2018/01,A,10;2018/01,B,5;2018/02,A,20;2018/02,B,10" 
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval Time=mvindex(data,0),Type=mvindex(data,1),Count=mvindex(data,2)
| table Time Type Count
| chart sum(Count) as count by Type Time
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

kevingo75
New Member

Thanks! It looks good!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...