Splunk Search

How to correlate fields from two different sourcetypes spawning across multiple hosts?

AbhinandGokul
New Member

Hello Guys,

I have a problem in correlating fields spawning across multiple hosts and different sourcetypes.

Here is how our setup looks like:

Apache access Log

CLIENTIP - - [27/Jan/2015:01:49:35 -0500] "POST /context1 HTTP/1.1" 200 295 "-" "unknown" "JSESSIONID" "-"
CLIENTIP - - [27/Jan/2015:01:49:35 -0500] "POST /context2 HTTP/1.1" 200 1896 "-" "unknown" "JSESSIONID" "-"

App Server Logs

01:49:35,580 INFO  [CLASS_NAME] [IP_ADDRESS] [1422341129579] [Company_ID,COMPANY_NAME,OTHER_DETAIL1,OTHER_DETAIL2,OTHER_DETAIL3,OTHER_DETAIL4,OTHER_DETAIL5] Time to generate SQL: 0.503659ms
01:45:35,580 INFO  [CLASS_NAME] [IP_ADDRESS] [1422341129579] [Company_ID,COMPANY_NAME,OTHER_DETAIL1,OTHER_DETAIL2,OTHER_DETAIL3,OTHER_DETAIL4,OTHER_DETAIL5] 

Requirement:

I want to be able to search for all CLIENT IPs in my Apache's access logs which have the context 'context1', take those CLIENT IPs and search them in my app server logs (the field name will be different here) and then get out the company name from my app server logs.

Basically I want to be able to create a report of how many requests per company has come in for a given context.

Could somebody please help me with this?
Regards,
Abhi

Tags (2)
0 Karma
1 Solution

aljohnson_splun
Splunk Employee
Splunk Employee

You could do something like this maybe ?

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path
| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]
| eval ip=coalesce(CLIENTIP, IP_ADDRESS)
| stats count  by context, ip, company 

Break down:
Select relevant fields

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path

Append a search of the second sourcetype:

| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]

Coalesce the IP fields:

| eval ip=coalesce(CLIENTIP, IP_ADDRESS)

Lastly, count the contexts

| stats count by context, ip, company 

View solution in original post

aljohnson_splun
Splunk Employee
Splunk Employee

You could do something like this maybe ?

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path
| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]
| eval ip=coalesce(CLIENTIP, IP_ADDRESS)
| stats count  by context, ip, company 

Break down:
Select relevant fields

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path

Append a search of the second sourcetype:

| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]

Coalesce the IP fields:

| eval ip=coalesce(CLIENTIP, IP_ADDRESS)

Lastly, count the contexts

| stats count by context, ip, company 

AbhinandGokul
New Member

Unfortunately, the way SPLUNK has been set up IP_ADDRESS in my app server logs is not a field but it is just a log entry. This would not work 😞

Regards,
Abhi

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

Sorry I just saw your comment now or else I would've responded.

Glad to hear it is working !

0 Karma

cpride_splunk
Splunk Employee
Splunk Employee

I think this is closer to working than you might think. Even if you don't want to add a field extraction for IP_ADDRESS you could easily pull it out with a "rex" command.

The only other comment is I might try to do this with just an OR clause in the base search to get events from both logs.

AbhinandGokul
New Member

Thanks! After extracting it out as a field it started working! 🙂

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...