Splunk Search
Highlighted

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

Motivator

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>> 
0 Karma
Highlighted

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

Motivator

New search but sadly not improved.....

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

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

SplunkTrust
SplunkTrust

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 

View solution in original post

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Motivator

final query looks like this

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

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

Motivator

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]
0 Karma