Can columns created by a transpose be ordered?

Follow
Avatar
Product Team

I'm using transpose on error severity per day like so:

_source=Alarms
| timeslice 1d
| count by _timeslice, severity
| fillmissing timeslice(1d), values("0", "1", "2", "3", "4", "5", "6", "7") in severity
| transpose row _timeslice column severity

So I wind up with data that looks like this:

|| _timeslice || 4 || 5 || 6 || 1 || 0 || 2 || 7 || 3 ||
| 2017/09/01 00:00:00.000 +0000 | 328 | 127 | 0 | 62 | 2 | 720 | 236 | 163 |
| 2017/09/02 00:00:00.000 +0000 | 236 | 97 | 0 | 48 | 0 | 374 | 63 | 112 |
| 2017/09/03 00:00:00.000 +0000 | 237 | 78 | 0 | 176 | 0 | 590 | 86 | 140 |
| 2017/09/04 00:00:00.000 +0000 | 160 | 125 | 0 | 53 | 12 | 417 | 84 | 127 |
| 2017/09/05 00:00:00.000 +0000 | 255 | 590 | 2 | 335 | 14 | 3715 | 231 | 369 |
| 2017/09/06 00:00:00.000 +0000 | 257 | 290 | 0 | 391 | 2 | 2185 | 257 | 643 |
| 2017/09/07 00:00:00.000 +0000 | 412 | 131 | 0 | 193 | 38 | 3582 | 230 | 256 |
| 2017/09/08 00:00:00.000 +0000 | 204 | 113 | 0 | 82 | 7 | 559 | 253 | 1541 |
| 2017/09/09 00:00:00.000 +0000 | 259 | 78 | 0 | 25 | 0 | 407 | 163 | 124 |
| 2017/09/10 00:00:00.000 +0000 | 279 | 93 | 0 | 9 | 0 | 412 | 91 | 64 |

Is there a way to order those transposed columns? I want the current order (4,5,6,1,0,2,7,3) changed to (0,1,2,3,4,5,6,7) so it makes more sense when I bar-chart it.

Thanks.

0

Comments

5 comments

Sort by Date Votes
  • Official comment
    Avatar
    Nick Wilson

    Hello,

    In your case, since you know the column names ahead of time, you could manually order them by doing something like this:

    _source=Alarms
    | timeslice 1d
    | count by _timeslice, severity
    | fillmissing timeslice(1d), values("0", "1", "2", "3", "4", "5", "6", "7") in severity 
    | transpose row _timeslice column severity as (%"0", %"1", %"2", %"3", %"4", %"5", %"6", %"7")
    | fields _timeslice,%"0", %"1", %"2", %"3", %"4", %"5", %"6", %"7"

    Let me know if that helps.

    Additionally, there is a feature request out there to implement dynamic sorting on the transpose operator output, and I recommend voting on it if you think it would be useful: https://ideas.sumologic.com/ideas/SL-I-1293

    Thanks,
    Nick Wilson

    Comment actions Permalink
  • Avatar
    Product Team

    That worked. Thanks!

    Charles

    0
    Comment actions Permalink
  • Avatar
    Nick Wilson

    You're welcome, Charles!

    0
    Comment actions Permalink
  • Avatar
    Steven Bogar

    what if the columns are not known ahead of time?  I am trying to do a similar query but running into the same issue, the problem is, I have a variable number of strings that are returned.

    _source="RDS Events" and _collector="aws_config" and "Finished DB Instance Backup"
    | parse "\\\"Source ID\\\":\\\"*\\\"" as DBInstanceIdentifier
    | timeslice 24h
    | count by _timeslice, DBInstanceIdentifier
    | transpose row _timeslice column dbinstanceidentifier
    | order by _timeslice desc

    0
    Comment actions Permalink
  • Avatar
    Nick Wilson

    Hi Steven,

    This is currently on our feature request portal. I highly recommend that you vote and comment on it to let our product team know that it's important to you. https://ideas.sumologic.com/ideas/SL-I-1293

    Thanks,
    Nick

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post