Why is my stats list() search not matching values correctly in the resulting table?

[skoelpin]

I have a web service called CreateOrder.. This has a request and response which has a unique identifier called a GUID. In the CreateOrderRequest, it has the AffiliateData which I did a field extraction for (AfData) and in the CreateOrderResponse I did another field extraction for the CreateOrderID.

I piped my search into a Transaction using the GUID so now each event has a request and response for CreateOrder. I want 2 columns, one showing the AffiliateData (AfData) and the second column showing the CreateOrderID.. The current way I have it set up, it's showing the data but when I do a search for the CreateOrderID, the wrong Affiliate Data is showing up.

I need the Affiliate Data to match the CreateOrderID. Everything is correct up until the last pipe

index=unleashed Call="<CreateOrder*"   | transaction GUID startswith="fterReceiveRequest" endswith="BeforeSendReply" | stats  list(AfData),list(CreateOrderID)

[fdi01]

try like:

index=unleashed Call="<CreateOrder*"   | transaction GUID startswith="fterReceiveRequest" endswith="BeforeSendReply" | stats  list(AfData) by CreateOrderID

[skoelpin]

Thanks for your response, but I just figured it out.

The search you posted was a similar approach to what stephanefotso originally posted, as it successfully ties them together but leaves me with a lot of blank rows

[stephanefotso · Accepted solution]

How about a simple table?

 index=unleashed Call="<CreateOrder*"   | transaction GUID startswith="fterReceiveRequest" endswith="BeforeSendReply" | table AfData CreateOrderID

[skoelpin]

This is very close to what I needed!! Now it's giving me 2 columns, one for the AfData and the second one for CreateOrderID (as expected) which are tied together. Now the only issue I have left is that all 7000+ CreateOrderID's are listed with only 200ish AfData rows. So I have a ton of blank spaces between the AfData rows. I need to remove these blank rows.

I only need the row if it has BOTH AfData AND CreateOrderID

Thanks for your help so far!

[skoelpin]

I figured it out, I just added | where isnotnull(AfData) and it worked!! Thanks for your help!

Here's my search for people needing help with a similar question in the future

index=unleashed Call="<CreateOrder*"   | transaction GUID startswith="fterReceiveRequest" endswith="BeforeSendReply" | table AfData CreateOrderID | where isnotnull(AfData)

[stephanefotso]

Here you go

 index=unleashed Call="<CreateOrder*"   | transaction GUID startswith="fterReceiveRequest" endswith="BeforeSendReply"|where AfData !=" " | table AfData CreateOrderID