Turning tables into a matrix form

[cindygibbs_08]

I hope you guys are doing very well,

I have a list of a series of task that are executed in an hourly fashion from Monday to Thursdays but the table that I get from my Splunk code shows the data as follows:

Time_Frame	num_task
Mon 14-16	48
Tue   14-16	14
Wed 14-16	69
Thu  14-16	4

Note: This table extends from 9-10 until 22-13 hours for each day (Mon-Thu)

but I would like this information to be shown as the following:

Day /Time Frame	14-16	16-18
Mon	48	14
Tue	14	25
Wed	69	14
Thu	4	05

Which will show the Days in a single column and each Time_Frame in their respective single columns the intercepting cells will represents the num_task for the corresponding  day and Time_frame,

I know the first step will be to split the strings "Mon 14-16" into Mon and time but I am getting lost as to how to make the, follow my desired matrix arrange. Thank you so much to everyone who can help me out.

Kindly,

Cindy

[gcusello · Accepted solution]

Hi @cindygibbs_08,

you can split the field "Time_Frame" into 2 fields using a regex like the following,

| rex "^(?<day>\w+)\s+(?<time>\d+-\d+)"

that you can test at https://regex101.com/r/KyiPaD/1

Then you can use the chart command to create your table.

There's only one problem, the order of the week days: that you can solve adding a number to the weekday before the chart command that you can remove after the chart command, something like this:

your_search
| rex "^(?<day>\w+)\s+(?<time>\d+-\d+)"
| eval day=case(day="Mon","1-".day,day="Tue","2-".day,day="Wed","3-".day,day="Thu","4-".day,day="Fri","5-".day)
| chart values(num_task) AS num_task OVER day BY time
| eval day=case(day="1-Mon","Mon",day="2-Tue","Tue",day="3-Wed","Wed",day="4-Thu","Thu",day="5-Fri","Fri")

Ciao.

Giuseppe