Dashboards & Visualizations

How to put values from a columns on a row for a commun field

Alaza
Explorer

Hello,

in my index I have values like below :

field _1 | field _2
null |1
6000 |2
3000 |2
6000 |3
3000 |3
null |4

How can I put the value on one row like this result ?

field_1 |field_2 |field_3
1 | null |null
2 |6000 |3000
3 |6000 |3000
4 |null |null

Thanks for you help.

Tags (1)
0 Karma

Alaza
Explorer

To be more clear on my demand.
My index contains row with differents fields : field_1 is the primary key (person) and field_2 is the function of this person.
A person can have 4 functions, or 3, or 2 or 1 or not (null).
For example a person with 4 fonctions will be represent by 4 rows. Each row with the field_2 fill differently.
For example a person with 3 fonctions will be represent by 3 rows. Each row with the field_2 fill differently.
A person with no function will be represent by 1 row with the field_2 null.

I want to have one row by person with the differents commun fields, and 4 fields representing each function possible fill if the person have this function.

0 Karma

mayurr98
Super Champion

I may be wrong but you can try something like this

|makeresults|eval data="field_1=null,field_2=1 field_1=6000,field_2=2 field_1=3000,field_2=2 field_1=6000,field_2=3 field_1=3000,field_2=3 field_1=null,field_2=4" |makemv data |mvexpand data |rename data as _raw|kv|table field_1 field_2 | stats list(field_1) as field_1 by field_2   | eval field1=mvindex(field_1,0) | eval field2=mvindex(field_1,1) | fields- field_1
0 Karma

cmerriman
Super Champion

I'm using this run-anywhere query, so you might need to tweak it to fit your needs. it isn't 100% what you're looking for, as I'm not sure if there will be more fields, or more values than 3000, 6000, and null.

|makeresults|eval data="field_1=null,field_2=1 field_1=6000,field_2=2 field_1=3000,field_2=2 field_1=6000,field_2=3 field_1=3000,field_2=3 field_1=null,field_2=4"|makemv data|mvexpand data|rename data as _raw|kv|table field_1 field_2
|eval field_3=field_1
|xyseries  field_2 field_3 field_1

using the query from eval field_3... should get you a table similar to what you want. you can add |fillnull value="null"|fields - null to the end and perhaps a |rename "field_2" as "field_1" |rename "3000" as "field_2" "6000" as "field_3" I think all of those additions will get you what you're looking for above.

0 Karma

Alaza
Explorer

I'm sorry but it doesn't work, I try to be more clear on my demand.
My index contains row with differents fields : field_1 is the primary key (person) and field_2 is the function of this person.
A person can have 4 functions, or 3, or 2 or 1 or not (null).
For example a person with 4 fonctions will be represent by 4 rows. Each row with the field_2 fill differently.
For example a person with 3 fonctions will be represent by 3 rows. Each row with the field_2 fill differently.
A person with no function will be represent by 1 row with the field_2 null.

I want to have one row by person with the differents commun fields, and 4 fields representing each function possible fill if the person have this function.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...