Splunk Search

transpose on selected fields

Susha
Engager

Hi Team,

I want to transpose few fields as below ..

(index=abc OR index=def) category= * OR NOT blocked =0 AND NOT blocked =2
|rex field=index "(?<Local_Market>[^cita]\w.*?)_"
| stats count(Local_Market) as Blocked by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count as Detected by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"]
| stats values(*) as * by Local_Market
| transpose 0 header_field=Local_Market column_name=Local_Market

here i want to add one column of date ( eval Time=strftime(_time,"%m/%d/%y")) which should not be transpose ..

date                    Local_Market    Total   a  b   c
05-09-2021       INDIA                     3      1  1  1
05-09-2021       UK                          5       3  2  0

Labels (1)
Tags (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

I don't follow. You want to have the date column left alone and all other columns transposed? It can't work even from simply algebraic point of view - you have two rows but three columns you want to transpose to rows.

Or is the table you presented the desired format?

Either way, I'd just add "static" column or row and transpose it if needed.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

_time does not exist due to the stats commands removing it from the event pipeline. Which value of time do you want to use? now() or earliest/latest from the search timeframe or something else?

0 Karma

Susha
Engager

@ITWhisperer  this report is scheduled for yesterday so i want to give yesterday date only .. like only 09-06-2021

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| addinfo
| eval date=info_min_time
| fieldformat date=strftime(date,"%m-%d-%Y")
| fields - info_*
0 Karma

Susha
Engager

thanks a lot @ITWhisperer  its working perfectly .. just one more thing .. as of now date column is coming at last column .. how can we place it in first column ..

 

 

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 ...