Formatting a multi column report.

[tyronetv]

I managed to get the following report from Splunk (excuse the lines, trying to format it for viewing):

mrSTATUS----------------------All-------------------DBA----------------ProdEng
Pending Confirmation------------6        
Outstanding From Prior---------25        
Closed-------------------------26        
In Progress--------------------36
Touched------------------------62
Pending Confirmation----------------------------------1  
Closed------------------------------------------------3
Touched----------------------------------------------11
In Progress-------------------------------------------8
Outstanding From Prior--------------------------------4  
Pending Confirmation-----------------------------------------------------------5
Closed------------------------------------------------------------------------25
Touched-----------------------------------------------------------------------54
In Progress-------------------------------------------------------------------29
Outstanding From Prior--------------------------------------------------------22

How can I get them to collapse to:

mrSTATUS-----------------------All------------------DBA---------------------ProdEng
Pending Confirmation-------------6--------------------1---------------------------5
Outstanding From Prior----------25--------------------3--------------------------25
Closed--------------------------26-------------------11--------------------------54
In Progress---------------------36--------------------8--------------------------29
Touched-------------------------62--------------------4--------------------------22

Any ideas? The query I used is below. Might be a more efficient way to do it, but not sure how, yet.

| dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' OR 
  mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
  mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
  mrassignees LIKE '%PE5%' ) 
AND 
( 
   ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' AND mrUPDATEDATE < DATEADD( day, -7, CURRENT_TIMESTAMP ) ) 
   OR 
   ( mrUPDATEDATE >= DATEADD( day, -7, CURRENT_TIMESTAMP ) )
)"
| multireport 
[ stats count(eval(mrSTATUS="Pending__bConfirmation")) as All | eval mrSTATUS="Pending Confirmation" ]
[ stats count(eval(mrSTATUS="Closed")) as All | eval mrSTATUS="Closed" ]
[ stats count as All | eval mrSTATUS="Touched" ]

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' OR 
     mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
     mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
     mrassignees LIKE '%PE5%' ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_') 
   )"
   | stats count as All | eval mrSTATUS="In Progress"
]

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' OR 
     mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
     mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
     mrassignees LIKE '%PE5%' ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' and mrSUBMITDATE < DATEADD( day, -7, CURRENT_TIMESTAMP )) 
   )"
   | stats count as All | eval mrSTATUS="Outstanding From Prior"
]

| append [
| dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' ) 
AND 
( 
   ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' AND mrUPDATEDATE < DATEADD( day, -7, CURRENT_TIMESTAMP ) ) 
   OR 
   ( mrUPDATEDATE >= DATEADD( day, -7, CURRENT_TIMESTAMP ) )
)"
| multireport 
[ stats count(eval(mrSTATUS="Pending__bConfirmation")) as DBA | eval mrSTATUS="Pending Confirmation" ]
[ stats count(eval(mrSTATUS="Closed")) as DBA | eval mrSTATUS="Closed" ]
[ stats count as DBA| eval mrSTATUS="Touched" ]
]

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_') 
   )"
   | stats count as DBA | eval mrSTATUS="In Progress"
]

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%DBA%' OR mrassignees LIKE '%dba1%' OR mrassignees LIKE '%dba2%' ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' and mrSUBMITDATE < DATEADD( day, -7, CURRENT_TIMESTAMP )) 
   )"
   | stats count as DBA | eval mrSTATUS="Outstanding From Prior"
]

| append [ 
| dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
( mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
  mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
  mrassignees LIKE '%PE5%' 
) 
AND 
( 
   ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' AND mrUPDATEDATE < DATEADD( day, -7, CURRENT_TIMESTAMP ) ) 
   OR 
   ( mrUPDATEDATE >= DATEADD( day, -7, CURRENT_TIMESTAMP ) )
)"
| multireport 
[ stats count(eval(mrSTATUS="Pending__bConfirmation")) as ProdEng | eval mrSTATUS="Pending Confirmation" ]
[ stats count(eval(mrSTATUS="Closed")) as ProdEng | eval mrSTATUS="Closed" ]
[ stats count as ProdEng | eval mrSTATUS="Touched" ]
] 

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
     mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
     mrassignees LIKE '%PE5%' 
   ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_') 
   )"
   | stats count as ProdEng | eval mrSTATUS="In Progress"
]

| append 
[ 
   | dbquery footprints "SELECT mrID, mrSTATUS, mrassignees FROM footprints.dbo.MASTER49 WHERE 
   ( mrassignees LIKE '%PE1%' OR mrassignees LIKE '%Production__bEngineering%' OR 
     mrassignees LIKE '%PE2%' OR mrassignees LIKE '%PE3%' OR mrassignees LIKE '%PE4%' OR 
     mrassignees LIKE '%PE5%' 
   ) 
   AND 
   ( 
      ( mrSTATUS <> 'Closed' AND mrSTATUS <> '_DELETED_' and mrSUBMITDATE < DATEADD( day, -7, CURRENT_TIMESTAMP )) 
   )"
   | stats count as ProdEng | eval mrSTATUS="Outstanding From Prior"
]

| fields mrSTATUS, All, DBA, ProdEng

[acharlieh · Accepted solution]

This is actually one of my favorite tricks of Splunk:

... | stats first(*) as * by mrSTATUS

Since your first set of results have the fields All and mrSTATUS, your second set only have DBA and mrSTATUS, your third only have ProdEng and mrSTATUS, for each mrSTATUS, the first All result would come from the first set, the first DBA result comes from the second set, and the first ProdEng result comes from the third 🙂

(I'm basing this on your initial tables, I haven't looked at your query in depth to figure out if you have blank fields trailing around that might throw a wrench in things).