How to generate a search that will display values in my sample data in a table?

[prashanthberam]

Hi I have events coming from the servers. here we have some sample data.

2017-03-29 13:57:09.892 [WMQJCAResourceAdapter : 1424] [INFO ] [DCN 0201708802519120C] SplunkLog - DCN=0201708802519120C, CorrelationID=000970348003699784622353, TransactionTimestamp=2017-03-29 13:57:08.135, GroupNumber =00064939G, ServiceLinecount=3, SectionNumber=0009, CorporateEntityCode=TX1, ClaimType=0, VendorName=VERSCEND, VendorCode=CVP, TransactionCode=RSPUtilizationAmount=0, Department=213, CVPOutcomeCode=G, CVPClaimStatusCode=VA


2017-03-29 13:57:09.285 [WMQJCAResourceAdapter : 2216] [INFO ] [DCN 0201708802519120C] SplunkLog - DCN=0201708802519120C, CorrelationID=000970348003699784622353, TransactionTimestamp=2017-03-29 13:57:09.285, GroupNumber =00064939G, ServiceLinecount=3, SectionNumber=0009, CorporateEntityCode=TX1, ClaimType=0, VendorName=VERSCEND, VendorCode=CVP, TransactionCode=ACK, OutCome=C, Messagetext=ACCEPTED, CVPOutcomeCode=O, Department=213,


2017-03-29 13:57:07.379 [WMQJCAResourceAdapter : 2229] [INFO ] [DCN 0201708802519120C] SplunkLog - DCN=0201708802519120C, CorrelationID=000970348003699784622353, TransactionTimestamp=2017-03-29 13:57:07.379, GroupNumber =00064939G, ServiceLinecount=3, SectionNumber=0009, CorporateEntityCode=TX1, ClaimType=0, VendorName=VERSCEND, VendorCode=CVP, TransactionCode=REQ, Department=213, CVPOutcomeCode=O, CVPClaimStatusCode=VA,

here correlationId is unique key here
I want to display these values in a single table but here cvpoutcomecode and cvpclaimstatuscode will vary in these messages but sometimes not. I want to display those values in the table from which message is coming from either Req,ack,rsp.

Please need help ....thanks in advance

[somesoni2 · Accepted solution]

Give this a try

your base search
| eval CVPOutcomeCode_REQ=if(TransactionCode="REQ",CVPOutcomeCode,null())
| eval CVPClaimStatusCode_REQ=if(TransactionCode="REQ",CVPClaimStatusCode,null())
| eval CVPOutcomeCode_ACK=if(TransactionCode="ACK",CVPOutcomeCode,null())
| eval CVPClaimStatusCode_ACK=if(TransactionCode="ACK",CVPClaimStatusCode,null())
| eval CVPOutcomeCode_RSP=if(TransactionCode="RSP",CVPOutcomeCode,null())
| eval CVPClaimStatusCode_RSP=if(TransactionCode="RSP",CVPClaimStatusCode,null())
| stats min(_time) as _time values(*_REQ) as *_REQ values(*_ACK) as *_ACK values(*_RSP) as *_RSP values(TransactionCode) as TransactionCode 
...add other fields here similar to TransactionCode...
by CorrelationID

[somesoni2]

Can you provide a mock table with output that you want (use this sample data as reference)?

[prashanthberam]

timestamp dcn Groupnumber CorrelationID sectionnum Corporateentity transactioncode cvpclaimstatuscode_req cvpclaimstatuscode_rsp cvpoutcomecode_req cvpoutcomecode_ack cvpoutcomecode_rsp department vendorname vendorcode

because am getting diffrent values cvpoutcomecode i need to know when it's coming

[woodcock]

Try this:

... | list(TransactionCode) AS TransactionCode list(CVPOutcomeCode) AS CVPOutcomeCode list(CVPClaimStatusCode) AS CVPClaimStatusCode BY CorrelationID