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!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...