Splunk Search

convert the row value to column

Susha
Engager

Hi All,

we have a query 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) by Local_Market
| rename count(Local_Market) as Total_Blocked | addcoltotals col=t labelfield=Local_Market label="Total Blocked"
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count by Local_Market | rename count as Detected_Count | addcoltotals col=t labelfield=Local_Market label="Total Detected")]


Local_market    total Blocked     total detected
Germany            20
ghana                   80
India                     91
total Blocked   191
Germany                                                 10
Ghana                                                       20
India                                                           10
total detected                                        40

i want data like

Local_Market        Germany   ghana   India   Total
total Blocked         20                80           91      191
Total Detected      10               20            10       40

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
(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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="Local_market,Blocked
Germany,20
Ghana,80
India,91"
| multikv forceheader=1 
| table Local_market Blocked
| addcoltotals col=t labelfield=Local_market label="Total"
| append 
    [| makeresults
    | eval _raw="Local_market,detected
Germany,10
Ghana,20
India,10"
| multikv forceheader=1 
| table Local_market detected
| addcoltotals col=t labelfield=Local_market label="Total"
]
| stats values(*) as * by Local_market
| transpose 0 header_field=Local_market column_name=Local_market
0 Karma

Susha
Engager

@ITWhisperer  thanks but  it seems given code is only for the 3 data but i have huge list of LM ..

how can we get that ..

i have tried below as well ..but its not working as required 

(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) by Local_Market
| rename count(Local_Market) as Total_Blocked | addcoltotals col=t labelfield=Local_Market label="Total Blocked" | transpose 0 header_field=Local_market column_name=Local_market)]
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count by Local_Market | rename count as Detected_Count | addcoltotals col=t labelfield=Local_Market label="Total Detected" | transpose 0 header_field=Local_market column_name=Local_market)]

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
(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
0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...