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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...