Splunk Search

Why is the sum of all events on transaction giving the wrong value?

abhishek0agarwa
New Member

I'm calculating sum of all the events in the transaction but the sum displayed is different from actual sum. What am i doing wrong?

index = app host = cheipaypoc* ActivityStep=rs  ActivityName!=endpoint* ActivityGUID = "b90746bb-ff1a-4b62-aa55-76b9eec533a4" (ActivityName=databaseCall OR @ResponsePayload) OperationName!=FileBackedSQLQueryRepository* |rename Duration as dd| transaction ActivityGUID ActivityStep | stats sum(dd) as EXTime | top EXTime

EXTime = 517
Event -

2018-05-28 23:39:19,510 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,510 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,513 Timestamp=2018-05-28 23:39:19,510 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanDaoImpl.PayOrch.queryPaymentPlansByIDs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,510 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,510 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,535 Timestamp=2018-05-28 23:39:19,510 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanRepository.PayOrch.retrievePaymentPlansByPaymentPlanIDs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=25 
2018-05-28 23:39:19,513 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,513 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,517 Timestamp=2018-05-28 23:39:19,513 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanTrackingInfoDaoImpl.PayOrch.queryTrackingInfoAttributesByPaymentPlanID TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=4 
2018-05-28 23:39:19,517 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,517 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,520 Timestamp=2018-05-28 23:39:19,517 ActivityName=databaseCall ActivityStep=rs OperationName=TaggingDataDAOImpl.PayOrch.queryTaggingData TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,520 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,520 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,523 Timestamp=2018-05-28 23:39:19,520 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemsByPaymentPlanId TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,523 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,523 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,526 Timestamp=2018-05-28 23:39:19,523 ActivityName=databaseCall ActivityStep=rs OperationName=TaggingDataDAOImpl.PayOrch.queryTaggingData TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,526 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,526 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,529 Timestamp=2018-05-28 23:39:19,526 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentInstrumentSummaryDaoImpl.PayOrch.retrievePaymentInstrumentSummary TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,529 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,529 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,532 Timestamp=2018-05-28 23:39:19,529 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemLocators TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:19,532 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,532 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,535 Timestamp=2018-05-28 23:39:19,532 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemLocators TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3 
2018-05-28 23:39:20,021 ProductName=PaymentOrchestratorService ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 ActivityName=PaymentExecutorImpl ActivityStep=rs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 TimestampStart=2018-05-28 23:39:19,536 Timestamp=2018-05-28 23:39:20,021 StatusCode=0 OperationName=verifyRefundPayment TimestampEnd=2018-05-28 23:39:20,021 Duration=485 DurationN=485 
@RequestPayload=9679a097-0c48-48b2-ae35-250256ba7fbacanbeanythingPayOrchaba47498-30b2-48ba-98b5-931aae6ea6c9:100000b53b6855-ae52-4b7f-a7b3-479f2f8c2435b0d66d04-8880-g32b-bffe-0d95f7f8a4b9USD-354.90ern:pay:ptx:rch::784593a7-99a6-17b2-01d6-dbf73768d9e9
@ResponsePayload=Successcom.expedia.e3.es.payment.common.errorhandling.CommonStatusReportingTemplates0Successfully processed!px-rch-c1-01.maui.us-west-2.paylabpci.comPayOrchaba47498-30b2-48ba-98b5-931aae6ea6c9:100000b0d66d04-8880-g32b-bffe-0d95f7f8a4b972aa2001-9062-4551-8364-ed2dab1b189bSuccesscom.expedia.e3.es.payment.common.errorhandling.CommonStatusReportingTemplates0Successfully processed!

But if I'm summing all the events in the transaction that is 535.

0 Karma

niketn
Legend

@abhishek0agarwal, Instead of transaction you should use stats

 index = app host = cheipaypoc* ActivityStep=rs  ActivityName!=endpoint* ActivityGUID = "b90746bb-ff1a-4b62-aa55-76b9eec533a4" (ActivityName=databaseCall OR @ResponsePayload) OperationName!=FileBackedSQLQueryRepository*
| stats sum(Duration) as EXTime by ActivityGUID ActivityStep
| sort - EXTime
| head 10

Also top command will give the top count of occurrence of a field value in your data. After performing stats the value of EXTime is already sum so you don't need top command. You can sort descending and get head 10 to get top 10 EXTime values. Refer to this documentation for choosing right correlation command for event correlation based on use cases: https://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation.

Please let us know purpose of transaction if above result is not what you are interested in. Following is a run anywhere search based on sample data provided!

|  makeresults
|  eval data="2018-05-28 23:39:19,510 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,510 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,513 Timestamp=2018-05-28 23:39:19,510 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanDaoImpl.PayOrch.queryPaymentPlansByIDs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3| 
2018-05-28 23:39:19,510 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,510 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,535 Timestamp=2018-05-28 23:39:19,510 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanRepository.PayOrch.retrievePaymentPlansByPaymentPlanIDs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=25| 
2018-05-28 23:39:19,513 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,513 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,517 Timestamp=2018-05-28 23:39:19,513 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentPlanTrackingInfoDaoImpl.PayOrch.queryTrackingInfoAttributesByPaymentPlanID TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=4|
2018-05-28 23:39:19,517 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,517 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,520 Timestamp=2018-05-28 23:39:19,517 ActivityName=databaseCall ActivityStep=rs OperationName=TaggingDataDAOImpl.PayOrch.queryTaggingData TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:19,520 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,520 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,523 Timestamp=2018-05-28 23:39:19,520 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemsByPaymentPlanId TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:19,523 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,523 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,526 Timestamp=2018-05-28 23:39:19,523 ActivityName=databaseCall ActivityStep=rs OperationName=TaggingDataDAOImpl.PayOrch.queryTaggingData TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:19,526 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,526 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,529 Timestamp=2018-05-28 23:39:19,526 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentInstrumentSummaryDaoImpl.PayOrch.retrievePaymentInstrumentSummary TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:19,529 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,529 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,532 Timestamp=2018-05-28 23:39:19,529 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemLocators TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:19,532 ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 TimestampStart=2018-05-28 23:39:19,532 ProductName=PaymentOrchestrator TimestampEnd=2018-05-28 23:39:19,535 Timestamp=2018-05-28 23:39:19,532 ActivityName=databaseCall ActivityStep=rs OperationName=PaymentItemMySQLDAO.PayOrch.queryPaymentItemLocators TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 Result=Success Duration=3|
2018-05-28 23:39:20,021 ProductName=PaymentOrchestratorService ActivityGUID=b90746bb-ff1a-4b62-aa55-76b9eec533a4 ActivityName=PaymentExecutorImpl ActivityStep=rs TransactionGUID=b53b6855-ae52-4b7f-a7b3-479f2f8c2435 TimestampStart=2018-05-28 23:39:19,536 Timestamp=2018-05-28 23:39:20,021 StatusCode=0 OperationName=verifyRefundPayment TimestampEnd=2018-05-28 23:39:20,021 Duration=485 DurationN=485|"
| makemv data delim="|"
| mvexpand data
| rename data as _raw
| KV
| stats sum(Duration) as EXTime by ActivityGUID ActivityStep
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

xpac
SplunkTrust
SplunkTrust

Did you try it like this?

index = app host = cheipaypoc* ActivityStep=rs ActivityName!=endpoint* ActivityGUID = "b90746bb-ff1a-4b62-aa55-76b9eec533a4" (ActivityName=databaseCall OR @ResponsePayload) OperationName!=FileBackedSQLQueryRepository* 
| rename Duration as dd 
| stats sum(dd) AS EXTime by ActivityGUID ActivityStep 
| sort 10 -EXTime

It has the additional benefit of |stats being way more performant than |transaction.

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

niketn
Legend

@xpac you beat me to it again. The top command after calculating ExTime is not required as explained in my answer. It will give top values, if same Duration repeats multiple times.

I think | sort - ExTime| head 10 is more appropriate if that is what is required!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

xpac
SplunkTrust
SplunkTrust

hehe, always glad to serve 😉
You're right, it's redundant. Maybe he/she can also just use sort 10 - ExTime?
(Updated it in my answer)

0 Karma

adonio
Ultra Champion

hello thre,

without getting to deep into it, thetransaction command creates a field called duration with lower d
try and use that field, so something like that:
... your search ... | stats sum(duration) as EXTime | top EXTime

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