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

[AbhinandGokul]

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

[aljohnson_splun · Accepted solution]

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]

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

[aljohnson_splun]

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

Glad to hear it is working !

[cpride_splunk]

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]

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