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

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

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!

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