How to edit my search to use a lookup table to search events around a specific time?

[dbcase]

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>> 

[gokadroid]

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]

[somesoni2 · Accepted solution]

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 

[dbcase]

Thanks Somesoni2!! The less better way actually turned out to be the better way 🙂

[dbcase]

final query looks like this

index=cox |rex "CpeId:\s(?<cpeid>\S+)"|search  [| inputlookup CPE.csv | fields Cpe_ID | rename Cpe_ID as cpeid ]

[dbcase]

New search but sadly not improved.....

| inputlookup CPE.csv | fields Cpe_ID [search index=cox widgetdelete|rex "CpeId:\s(?<cpeid>\S+)" |table cpeid]