Splunk Search

Transposing data where two columns are fixed

New Member

HI All,

I am struggling with a query where i have made the data like the following

Type122/06/2020 11:00Store110
Type122/06/2020 11:00Store220
Type122/06/2020 11:00Store330
Type222/06/2020 11:00Store1100
Type222/06/2020 11:00Store2200
Type222/06/2020 11:00Store3300


And I need it to be like the below. Any help on this please ?

Type122/06/2020 11:00102030
Type222/06/2020 11:00100200300


Labels (1)
0 Karma


@bismsit29 , Try this:

| strcat Type "-" _time column
| xyseries column, Store, Counts
| eval column=split(column, "-"), Type=mvindex(column, 0), _time=mvindex(column, 1)
| fields Type, _time, Store*

0 Karma


Without knowing the underlying query that is generating the table you provided here's the way you can get to what you want from that table. There is a more efficient way of doing this directly from underlying data if you can provide what that is.  If not here is how you can generate the desired table from the table you have.  Refer to lines 13-17.  The first 12 lines are me recreating the table you provided as an example.

| makeresults
| eval Data="Type1|22/06/2020 11:00|Store1|10
Type1|22/06/2020 11:00|Store2|20
Type1|22/06/2020 11:00|Store3|30
Type2|22/06/2020 11:00|Store1|100
Type2|22/06/2020 11:00|Store2|200
Type2|22/06/2020 11:00|Store3|300"
| makemv tokenizer="(?<Data>[^\n]+)" Data
| mvexpand Data
| rex field=Data "^(?<Type>[^\|]+)\|(?<time>[^\|]+)\|(?<Store>[^\|]+)\|(?<Counts>[^\e]+)"
| eval _time=strptime(time, "%d/%m/%Y %H:%M")
| table Type _time Store Counts
| eval ClownCar=Type."|"._time
| chart values(Counts) as Counts over ClownCar by Store
| rex field=ClownCar "(?<Type>[^\|]+)\|(?<time>[^\e]+)"
| eval _time=time
| table Type _time Store*
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma