Splunk Search

Put the time into a column instead of a row

egreibl
Engager

Hi everyone,

I hope you can help.
I have the following search:

index=rb_idx_default_summary (report=EXCHANGE_Mailboxuser OR report=ESA_textmail) (NumberResourceMailBoxes=* OR NumberSharedMailBoxes=* OR NumberUserMailBoxes=* OR NumberAntivirusPositive=*) | eval MAXNumberResourceMailBoxes=max(NumberResourceMailBoxes) | eval MAXSharedMailBoxes=max(NumberSharedMailBoxes) | eval MAXNumberUserMailBoxes=max(NumberUserMailBoxes) | eval MAXNumberAntivirusPositive=max(NumberAntivirusPositive) | timechart span=1mon max(NumberResourceMailBoxes) max(NumberSharedMailBoxes) | eval timeprevmonth=strftime(_time,"%Y-%m") | table timeprevmonth, max(NumberResourceMailBoxes), max(NumberSharedMailBoxes) | rename max(NumberResourceMailBoxes) AS "Resource MailBoxes", max(NumberSharedMailBoxes) AS "Number of Shared Mailboxes" | transpose

so, and it gives me the following result:

column                   | row 1
timeprevmonth             | 2017-07
Resource MailBoxes          | 123
Number of Shared Mailboxes  | 456 

But I want to have the time also in the first column and not as a row.
Is there another option besides transpose to have the search result in one column? I want to have it like this all in one search result (and there will be other sources included):

2017-07 | Resource MailBoxes         | 123
2017-07 | Number of Shared Mailboxes | 456
2017-07 | Number of ValueXY          | 789
2017-07 | Number of ValueABC         | 101112

thanks, Lisi

0 Karma

sbbadri
Motivator

Try this,

index=rb_idx_default_summary (report=EXCHANGE_Mailboxuser OR report=ESA_textmail) (NumberResourceMailBoxes=* OR NumberSharedMailBoxes=* OR NumberUserMailBoxes=* OR NumberAntivirusPositive=*) | eval _time = strftime(_time,"%Y-%m") | bucket span=1mon _time | stats max(NumberResourceMailBoxes) as "Resource MailBoxes", max(NumberSharedMailBoxes) as "Number of Shared Mailboxes", max(NumberUserMailBoxes) as MAXNumberUserMailBoxes, max(NumberAntivirusPositive) as MAXNumberAntivirusPositive by _time

DalJeanis
SplunkTrust
SplunkTrust

@sbbadri - Pretty good. Be sure to mark your code as code so that asterisks and angle brackets don't disappear.

These two lines are redundant with each other, so pick either one, but not both...

| eval _time = strftime(_time,"%Y-%m")
| bucket span=1mon _time

The first one leaves the underlying field formatted as epoch time, the second turns it into a text display field. Or you could switch the order, and they at least wouldn't be redundant.

0 Karma

egreibl
Engager

thanks to you both.
but the result is still in one row and not in one column.
so, if I try to use your tip, I'll get the following outcome:

_time | max(NumberResourceMailBoxes) | max(NumberSharedMailBoxes) | max(Number AnitvirusPositive)
2017-07 | 123 | 3456 | 8797
2017-08 | 111 | 222 | 3333

but I want to have it like this:

_time | Description | Value
2017-07 | max(NumberResourcemailBoxes) | 123
2017-07 | max(NumberSharedMailBoxes) | 3456
2017-07 | max(NumberAntivirusPositive) | 8797
......
0 Karma
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 ...