Splunk Search

Formatting a multi column report.

tyronetv
Communicator

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
0 Karma
1 Solution

acharlieh
Influencer

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

View solution in original post

acharlieh
Influencer

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

Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...