topic Re: summarizing table from multiple records in Splunk Search

How to summarize table from multiple records?

csahoo — Tue, 31 May 2022 05:06:23 GMT

index="np-dockerlogs*" source="*gps-request-processor-dev*" sourcetype= "*eu-central-1*" event="*Request"
| fields event category labelType documentType regenerate businessKey businessValue sourceNodeType sourceNodeCode geoCode jobId status sourcetype source traceID processingTime _time
| eval LabelType=coalesce(labelType, documentType)
| sort _time
| table event LabelType sourceNodeCode geoCode status traceID processingTime

Above query provide three record for each traceid which indicate for the respective traceid
request was received
request was success/failed
total time taken by the request

now from this data i want to produce below type of table

geoCode sourceNodeCode LabelType event totalreqreceived successrate avgProcessingTime
EMEA 1067 Blindilpn synclabelrequest 1 100% 450

taskstart synclabelrequest 5 98% 1500

1069 ilpn synclabelrequest 1 100% 420

NA 1068 NIKE synclabelrequest 1 100% 500
cgrade synclabelrequest 4 95% 2000
NIKE asynclabelrequest 1 100% 350

This table shows the
'total no of request received' , 'there success percentage' and 'average processingtime'
for each 'event (either synclabelrequest or asynclabelrequest)' from a list of 'labelType'
belongs to a specific sourceNodeCode and geocode

Re: summarizing table from multiple records

ITWhisperer — Sun, 29 May 2022 10:48:01 GMT

Try something like this

| eventstats values(LabelType) as LabelType values(processingTime) as processingTime by traceID | stats count(eval(status="Received")) as received count(eval(status="Success")) as success values(event) as event values(processingTime) as processingTime by traceID sourceNodeCode geoCode LabelType | stats sum(received) as received sum(success) as success avg(processingTime) as avgProcessingTime by geoCode sourceNodeCode LabelType event | eval success=round(100*success/received,2)."%"

Re: summarizing table from multiple records

PickleRick — Sun, 29 May 2022 13:33:19 GMT

Remember that you don't have a "joined field" in splunk - I mean a field overlapping several rows. It's not an Excel, you know? 😉

So you can either have three separate rows of results with the same value in the beginning or just one row, possibly containing multivalued results.

Re: summarizing table from multiple records

csahoo — Tue, 31 May 2022 05:14:59 GMT

Thank you @ITWhisperer it's working