Splunk Search

How to merge two queries into one from two sources?

Motivator

Hi,

I have two queries that I'm attempting (badly) to merge into one

The first query is below and it works (final result is a table of public IP addresses)

 index=main source=*access* rex “HTTP.\d+.\d+.\s+(?<status>\d+)”|rex "blah.com.\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|where status=400|dedup ipaddr|table ipaddr

The second query is below and it is also a table of ip addresses

index=wls "request from premise"|rex "X-Forwarded-For:\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|dedup ipaddr|rex "request from premise\s+(?<premiseid>\d+)"|stats list(premiseid) by ipaddr

My attempt at a merged query is:

index=wls  [search index=main source=*access* |rex “HTTP.\d+.\d+.\s+(?<status>\d+)”|rex "blah.com.\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|where status=400|dedup ipaddr|table ipaddr]|rex "X-Forwarded-For:\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|dedup ipaddr|rex "request from premise\s+(?<premiseid>\d+)"|stats list(premiseid) by ipaddr

The merge query is attempting to link the ip addresses (ipaddr field) from the first query and the second query and then pull out the premise id.

If I do this separately I can get it to work but the merging is messing me up

If it helps here is a sample event from the first query

"blah.com" 74.197.244.150 "74.197.244.150" - - [31/May/2018:12:14:33 -0500] "POST /rest/icontrol/sites/1001529/associateByDeviceId?deviceId=0021CC1D7C7E HTTP/1.1" 400 64 0 4277 "-" "Mozilla/5.0 (iPhone; CPU iPhone OS 11_2_6 like Mac OS X) AppleWebKit/604.5.6 (KHTML, like Gecko) Mobile/15D100" "-"  "-"

Second query event example

####<May 31, 2018 7:53:41 AM CDT> <Debug> <ucontrol> <betamax-portal2> <managedServer4> <[ACTIVE] ExecuteThread: '9' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <> <1527771221668> <BEA-000000> <fn.util.SingleDeviceDebugger  - RCVD REST request from premise 1001529: 
GET /rest/async/icontrol/sites/1001529/deltas HTTP/1.1
Host: betamax-portal2:8080
Accept: application/json, text/plain, */*
Cookie: JSESSIONID=ceO2QmZrvZLqy6gN8Zk1QxH7nAEP0Zzmht19o3FSol7qS6FWoXnk!-1739055511
User-Agent: Mozilla/5.0 (iPhone; CPU iPhone OS 11_2_6 like Mac OS X) AppleWebKit/604.5.6 (KHTML, like Gecko) Mobile/15D100
Accept-Language: en-us
Accept-Encoding: br, gzip, deflate
Via: 1.1 blah.com
X-Forwarded-For: 74.197.244.150
X-Forwarded-Host: blah.com
X-Forwarded-Server: blah.com
Connection: Keep-Alive
spsId: 41480
Tags (3)
0 Karma
1 Solution

Motivator

This seemed to work

index=wls "request from premise"|rex "X-Forwarded-For:\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|rex "request from premise\s+(?<premiseid>\d+)"|dedup ipaddr|search [search index=main source=*access* |rex “HTTP.\d+.\d+.\s+(?<status>\d+)”|rex "blah.com.\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|where status=400|dedup ipaddr|table ipaddr]|stats list(premiseid) as "Premise ID" by ipaddr

View solution in original post

0 Karma

Motivator

This seemed to work

index=wls "request from premise"|rex "X-Forwarded-For:\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|rex "request from premise\s+(?<premiseid>\d+)"|dedup ipaddr|search [search index=main source=*access* |rex “HTTP.\d+.\d+.\s+(?<status>\d+)”|rex "blah.com.\s+(?<ipaddr>\d+.\d+.\d+.\d+)"|where status=400|dedup ipaddr|table ipaddr]|stats list(premiseid) as "Premise ID" by ipaddr

View solution in original post

0 Karma