How do I obtain data from a search based on matching field values from multiple source types?

[nixlim]

Dear All,

I have just started using Splunk and I have a question:

I have one index and two source types. The structure is as follows:

index: servers

sourcetypes: server and gateway

The record structure (fields) is as follows:

sourcetype=gateway
|
->OriginID: (this is an alphanumeric ID - eg 12345)
->ClientType: (can be either BACKEND or FRONTEND)

sourcetype=server
|
->TransactionID: (this is an alphanumeric ID - eg 12345)

I was trying to build a query that would give me a number of all BACKEND(or FRONTEND) transactions where OriginID equals TransactionID. I basically need to know how many are hitting the server from BACKEND and how many from FRONTEND.

So, I tried this:

index=servers sourcetype=gateway AND sourcetype=server| search *| where TransactionID==OriginID| stats by count

This does not work. Could I please ask what would be the best way to get the details that I want to see?

Thank you.

[richgalloway · Accepted solution]

There are a few reasons why your search didn't work.

index=servers sourcetype=gateway AND sourcetype=server| search *

An event can have only one sourcetype, which will have only one value so it's not possible for sourcetype to be 'gateway' and 'server' at the same time. The search command is not needed since that is implied.

where TransactionID==OriginID

Again, each event will have either OriginID or TransactionID, but not both so it's impossible to compare these fields to each other.

stats by count

There is no field called 'count' so one cannot group by that field. You probably meant stats count by, but then you'd need a field by which to group the counts.

To find the number of backend and frontend transactions, try this query.

index=servers (sourcetype=gateway OR sourcetype=server) | stats count by ClientType