topic Re: How to convert rows to columns in Splunk Search

How to convert rows to columns

sugername — Tue, 16 Jul 2019 13:59:27 GMT

I have this result

I whant convert in this

transpose command does not work
the stats command may work, but I don't know how

Re: How to convert rows to columns

jutzasconsist — Tue, 16 Jul 2019 14:20:14 GMT

Hi sugername,

can you please provide more details?
What does your stats command look like?
I woud suggest the easiest way was to change x and y-axis within the stats command.
Kind regards,
Michael

Re: How to convert rows to columns

sugername — Tue, 16 Jul 2019 14:35:49 GMT

| bin _time as spantime span=1d
| stats list(Remark) as Remark list(EventContents) as EventContents count list(_time) as times earliest(_time) as earliesttimes latest(_time) as latestt by AccessOwnerNAme spantime
| convert timeformat="%d.%m.%Y" ctime(spantime) as spantime
| convert timeformat="%T" ctime(times) as times, ctime(earliesttimes) as earliesttimes, ctime(latestt) as latestt
| eval latestt1=latestt, earliesttimes1=earliesttimes | convert dur2sec(latestt1), dur2sec(earliesttimes1)
| eval dayduration1=latestt1-earliesttimes1 | eval dayduration=tostring(dayduration1, "duration")
| eval earliesttimes2=earliesttimes1-32400 | eval field_in_hhmmss1=tostring(earliesttimes2, "duration")
| eval latestt2=64800-latestt1 | eval field_in_hhmmss2=tostring(latestt2, "duration")
| eval latestt3=latestt1-64800 | eval field_in_hhmmss3=tostring(latestt3, "duration")
| eval All_time="Come ".earliesttimes."
"."Has left ".latestt
| sort -Remark
| fields AccessOwnerNAme spantime All_time dayduration field_in_hhmmss1 field_in_hhmmss2 field_in_hhmmss3 Remark times
| rename spantime as Day, earliesttimes as "First event", latestt as "Last event", field_in_hhmmss1 as "Late", dayduration as "Difference between first and last event", field_in_hhmmss2 as "Early departure", field_in_hhmmss3 as "Recycling"

Re: How to convert rows to columns

sugername — Thu, 18 Jul 2019 04:10:22 GMT

I used this command

| chart values(All_time) over AccessOwnerNAme BY Day

But I don't know how to do count of some date

Re: How to convert rows to columns

thomasroulet — Fri, 19 Jul 2019 15:12:05 GMT

You could try to mix stats and transpose.
Here is my proposition based on your dataset :

.
. SPL producing screenshot-1.png
.

| stats values(time) as time, values(some_time) as some_time, count(date) as date, count(some_date) as some_date by name
| eval j=mvzip(time,some_time), j=mvjoin(j,"@")
| map search="| makeresults 1 
| eval j=$j$ 
| makemv delim=\"@\" j 
| mvexpand j 
| rex field=j \"(?\S+),(?\S+)\"
| table some_time, time 
| transpose header_field=time 
| eval name=\"$name$\" , date=\"$date$\", some_date=\"$some_date$\" 
| fields - column "
| table name, tim*, date, some_date

edit :
an other option

| makeresults 
 | eval _raw="  name     time     sometime      date     somedate
  name1    123    some_time1    date1    some_date1
  name1    456    some_time2    date2     
  name1    time3    some_time3    date3    some_date3
  name1    time4    some_time4    date4    some_date4
  name1    time5    some_time5    date5    some_date5
  name1    789    some_time6    date6    some_date6
  name2    123    some_time1    date1    some_date1
  name2    456    some_time2    date2    some_date2
  name2    time3    some_time3    date3    some_date3
  name2    time4    some_time4    date4    some_date4
  name2    time5    some_time5    date5    some_date5" 
 | multikv forceheader=1 | fields - _raw, _time
 | eval {time}=sometime
 | stats values(*) as *, count(date) as date2, count(somedate) as somedate2 by name
 | fields - time, somedate, sometime, date, linecount
 | rename date2 as date, somedate2 as somedate

Re: How to convert rows to columns

nsaveljevas — Thu, 27 Feb 2025 21:52:05 GMT

Thanks! It was help me A LOT to solve my SPL problem!