Splunk Search

How do I create a query to retrieve all info in one row from the following table below?

maximusdm
Communicator

I need to create a query that will show all the cells from the table below which exceed 80%.

alt text

Here is the query I was given (quite long). I am a beginner and this query is too advanced for me.
I need to add a statement which will retrieve the Channel name and column name as well as the %number > 80%, all in one row.
Thank you.

| dbxquery connection=BDMS shortnames=t query="select *
from (
select 
       p.ZoneTargetAreaName DMA,
       p.ZoneTargetAreaChannel Channel,
       cast(cast(sum(case when p.NumberSTBRanAd = 0 and p.NumberSTBNotRanAd = 0 and p.ExternalStatusCode = 1 then 1 else 0 end) / cast(count(p.PlacementID) as decimal)  *100 as decimal(18,2)) as varchar(6))+'%' percents

from placements p
inner join AiredBreaks ab on p.BreakID = ab.BreakID
where ExternalStatusCode in (1,2,12)
       and ab.ViewsrvID = '1P' -- using only primary server
  and (
  (ab.AiredTime >= dateadd(hour,5,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,5,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('ATL','BLW','BOS','BTM','CBA','CHR','CLB','CLE','CNC','DET','DTN','FMN','GBO','GDP','GSP','HLB','HTF','IDY','JAX','KNX','LSV','LXT','MIA','NLK','NYC','ORL','PBG','PHL','RAL','RMP','RNK','TPA','WBS','WDC','WPM')) or 
  (ab.AiredTime >= dateadd(hour,6,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,6,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('AST','BHM','CHG','CSD','DFW','DSO','HOU','HTS','JKS','KTY','LRK','MBP','MMS','MSP','MWK','NOL','NSH','OKC','OMH','PDC','SAN','SFM','STL','TLS','WTA')) or 
  (ab.AiredTime >= dateadd(hour,7,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,7,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('ABQ','CSP','DEN','SLC','PHX','TUS')) or
  (ab.AiredTime >= dateadd(hour,8,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,8,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('FAT','LAS','LAX','PRT','SAC','SDC','SEA','SFO','SKN')) ) 
group by p.ZoneTargetAreaName, p.ZoneTargetAreaChannel
) as P
pivot (
       min(percents) for P.DMA in (ATL,BLW,BOS,BTM,CBA,CHR,CLB,CLE,CNC,DET,DTN,FMN,GBO,GDP,GSP,HLB,HTF,IDY,JAX,KNX,LSV,LXT,MIA,NLK,NYC,ORL,PBG,PHL,RAL,RMP,RNK,TPA,WBS,WDC,WPM,AST,BHM,CSD,CHG,DFW,DSO,HOU,HTS,JKS,KTY,LRK,MBP,MMS,MSP,MWK,NOL,NSH,OKC,OMH,PDC,SAN,SFM,STL,TLS,WTA,ABQ,CSP,DEN,PHX,SLC,TUS,FAT,LAS,LAX,PRT,SAC,SDC,SEA,SFO,SKN)
) as PIV"
| fields - _raw _time

| fillnull value=NULL ATL BLW BOS BTM CBA CHR CLB CLE CNC DET DTN FMN GBO GDP GSP HLB HTF IDY JAX KNX LSV LXT MIA NLK NYC ORL PBG PHL RAL RMP RNK TPA WBS WDC WPM AST BHM CSD CHG DFW DSO HOU HTS JKS KTY LRK MBP MMS MSP MWK NOL NSH OKC OMH PDC SAN SFM STL TLS WTA ABQ CSP DEN PHX SLC TUS FAT LAS LAX PRT SAC SDC SEA SFO SKN

| table Channel ATL BLW BOS BTM CBA CHR CLB CLE CNC DET DTN FMN GBO GDP GSP HLB HTF IDY JAX KNX LSV LXT MIA NLK NYC ORL PBG PHL RAL RMP RNK TPA WBS WDC WPM AST BHM CSD CHG DFW DSO HOU HTS JKS KTY LRK MBP MMS MSP MWK NOL NSH OKC OMH PDC SAN SFM STL TLS WTA ABQ CSP DEN PHX SLC TUS FAT LAS LAX PRT SAC SDC SEA SFO SKN
0 Karma
1 Solution

somesoni2
Revered Legend

Try like this

your above query to generate that table
| untable Channel Location Value 
| eval Value_n=replace(Value,"%","") | where Value_n>80

View solution in original post

0 Karma

somesoni2
Revered Legend

Try like this

your above query to generate that table
| untable Channel Location Value 
| eval Value_n=replace(Value,"%","") | where Value_n>80
0 Karma

maximusdm
Communicator

you are the guru! thank you!!!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...