topic Re: transpose on selected fields in Splunk Search

transpose on selected fields

Susha — Tue, 07 Sep 2021 14:35:56 GMT

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

Re: transpose on selected fields

ITWhisperer — Tue, 07 Sep 2021 14:47:32 GMT

_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?

Re: transpose on selected fields

Susha — Tue, 07 Sep 2021 14:57:57 GMT

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

Re: transpose on selected fields

PickleRick — Tue, 07 Sep 2021 20:19:21 GMT

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.

Re: transpose on selected fields

ITWhisperer — Tue, 07 Sep 2021 22:13:52 GMT

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

Re: transpose on selected fields

Susha — Mon, 13 Sep 2021 15:03:46 GMT

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