Subsearch NOT with dbxquery

[teknet9]

Hello Team,

I do have dbquery from mysql:
|dbxquery query="SELECT mac FROM pc.pc" connection=MYSQL shortnames=true | fields - _*

This one displayes all the mac addresses from mysql.

I also have:
host="10.62.140.64" MACAddress
This one is displaying syslogs with all MACAddress values like MACAddress=00:01:02:03:04:05

I wanted to combine both and have a search which displays only syslogs with MACAddresses which are not in mysql database.
I have tried:
host="10.62.140.64" MACAddress NOT [dbxquery query="SELECT mac FROM pc.pc" connection=MYSQL shortnames=true | fields - _*]

But still all MACAddresses are being displayed. It looks like i can not correlate MACAddress with mac from subsearch. Could you help ?

Thanks,
Michal

[renjith_nair]

Try this

host="10.62.140.64" MACAddress NOT [|dbxquery query="SELECT mac as MACAddress  FROM pc.pc" connection=MYSQL shortnames=true | fields MACAddress ]

[teknet9]

Hi Renjith.Nair

I can not since then mysql column name is different, so my real query has to remane this field:
host="10.62.140.64" MACAddress NOT [dbxquery query="SELECT mac FROM pc.pc" connection=MYSQL shortnames=true | fields mac | rename mac as MACAddress ]

But what i find interesting is that it looks like dbxquery is searching only for 100 000 entries ? (while i have 171K) ? But when i try to:
add "search MACAddress=00:50:B6:11:EA:CE" to my dbxquery that entry is being returned correctly.

Let me paste dbquery results:

We can see that mac, while the combined search still returns that record while it should not:

So maybe it is the issue with max 100k records ? Can i change it somehow ?

Thanks,
Michal

[renjith_nair]

I thought you could rename in the select itself ,like mac as MACAddress in SQL. Anyway if you are able to change it somehow it's fine. Subquery limit is 10k and you can change it in http://docs.splunk.com/Documentation/Splunk/latest/Admin/Limitsconf (be careful).
Also try adding format at the end of your subquery ie dbxquery query="SELECT mac FROM pc.pc" connection=MYSQL shortnames=true | fields mac | rename mac as MACAddress|format