Hi,
I have a lookup table that has 1 field (Cpe_ID). I need to use the lookup table to search the events around a specific time.
I have this search to start but its not working
index=cox widgetdelete [| inputlookup CPE.csv | fields Cpe_ID]|rex "CpeId:\s(?<cpeid>\S+)"|where Cpe_ID=cpeid|table cpeid
The data looks like this..... Need to match on Cpe_ID
###<Nov 10, 2016 9:10:56 AM EST> <Debug> <ucontrol> <ccivirpxa0702> <managedServer03> <client-9> <<anonymous>> <> <> <1478787056690> <BEA-000000> <fn.util.SingleDeviceDebugger - CpeId: 9c972684258e :: RCVD SMAP packet
<iq id="294217951" from="38705@xmpp/9c972684258e" uri="/cpe/widgetDelete" type="result">
<smap xmlns="http://ucontrol.com/smap/v2" revision="1">
<requestResponse>
<code>17010</code>
<message>App operations are not allowed unless the system is disarmed</message>
</requestResponse>
</smap>
</iq>>
You were in right direction. Only thing was the field CpeId was not extracted in the base search, so the subsearch filter would not work.
What you can do is use lookup to do a text based search (instead of field based), like this (better way)
index=cox widgetdelete [| inputlookup CPE.csv | eval search=Cpe_ID | table search ]|rex "CpeId:\s(?<cpeid>\S+)"|table cpeid
OR First extract field and then apply filter (less better way)
index=cox widgetdelete|rex "CpeId:\s(?<cpeid>\S+)"|search [| inputlookup CPE.csv | fields Cpe_ID | rename Cpe_ID as cpeid ]|table cpeid
If CPE_ID value in lookup exists exactly the way it exists in events then you might not need rex, as the strings which come back from lookup will be searched in the events automatically as OR separated strings. See this similar answer: which should work:
https://answers.splunk.com/answers/469921/how-to-search-a-proxy-log-index-to-get-a-list-of-u.html
Somehting like:
index=cox widgetdelete [| inputlookup CPE.csv | fields Cpe_ID]
You were in right direction. Only thing was the field CpeId was not extracted in the base search, so the subsearch filter would not work.
What you can do is use lookup to do a text based search (instead of field based), like this (better way)
index=cox widgetdelete [| inputlookup CPE.csv | eval search=Cpe_ID | table search ]|rex "CpeId:\s(?<cpeid>\S+)"|table cpeid
OR First extract field and then apply filter (less better way)
index=cox widgetdelete|rex "CpeId:\s(?<cpeid>\S+)"|search [| inputlookup CPE.csv | fields Cpe_ID | rename Cpe_ID as cpeid ]|table cpeid
Thanks Somesoni2!! The less better way actually turned out to be the better way 🙂
final query looks like this
index=cox |rex "CpeId:\s(?<cpeid>\S+)"|search [| inputlookup CPE.csv | fields Cpe_ID | rename Cpe_ID as cpeid ]
New search but sadly not improved.....
| inputlookup CPE.csv | fields Cpe_ID [search index=cox widgetdelete|rex "CpeId:\s(?<cpeid>\S+)" |table cpeid]