Splunk Search

How to merge two queries into one from two sources?

dbcase
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

dbcase
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

dbcase
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
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...