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