How to connect consumers and providers based on ID between logs and display their server names too?

[ana]

I am hoping you could help me out with this query, as I am quite stuck.

I want to be able to retrieve the name of the server that acts as a provider and the name of the server that acts as a consumer.  The way you could check this is a log has a ConsumerId that equals the ID of the other server. For instance, here are two logs:

ServerName="Server1", ID="1", IDConsumer=null

ServerName="Server2", ID="2" , IDConsumer="1"

 And what I want to retrieve is a table like this:

To              From         IDConsumer   IDProvider

Server1  Server2    1                          2

Appreciate a lot!

[ITWhisperer · Accepted solution]

| eval To=if(isnull(IDConsumer),ServerName,null())
| eval From=if(isnull(IDConsumer),null(),ServerName)
| eval Consumes=if(isnull(IDConsumer),ID,IDConsumer)
| eval IDProvider=if(isnull(IDConsumer),null(),ID)
| stats values(To) as To values(From) as From values(IDConsumer) as IDConsumer values(IDProvider) as IDProvider by Consumes

[ana]

Thank you!