Reporting

Turning tables into a matrix form

cindygibbs_08
Communicator

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_Framenum_task
Mon 14-1648
Tue   14-1614
Wed 14-1669
Thu  14-164

 

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 Frame14-1616-18
Mon4814
Tue1425
Wed6914
Thu405

 

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

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...