Splunk Search

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

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

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

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

Communicator

you are the guru! thank you!!!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!