Splunk Search

How to convert rows to columns

sugername
New Member

I have this result
alt text
I whant convert in this
alt text
transpose command does not work
the stats command may work, but I don't know how

0 Karma

thomasroulet
Path Finder

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
0 Karma

jutzasconsist
New Member

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

0 Karma

sugername
New Member

I used this command

| chart values(All_time) over AccessOwnerNAme BY Day

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

0 Karma

sugername
New Member
| 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"
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 ...