Splunk Search

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

dbcase
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
1 Solution

somesoni2
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

gokadroid
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

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

dbcase
Motivator

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

0 Karma

dbcase
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

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...