Splunk Search

How do I edit my search to filter results using a CSV file?

raby1996
Path Finder

Hi all,

So I'm having trouble combining my search data and CSV data so that "Bundle" has to match "Bundle Version" in order for it to be included in my results, but the results do not filter. When I just try to match the fields, I get no results when I filter the "Bundle Version" field by value in the "Release" field (I don't want any results where Release = INTERNAL ). Thank you and here is my code with a few substitutes:

 index=* sourcetype=mysourcetype "search"   
| rex "(?<Bundle>\s+\d+\.\d+\.\d+\.\d+)"
| rex "(?m)Package:\s+SEA.ha(?:\n|.)*?Package Level:\s+(?<LIC>\(REX))" 
| rex "(?m)Package:\s+SEA.ha(?:\n|.)*?MM:(?<MM>\(REX))" 
| rex "(?m)Current Bundle.*?(?:\n|.)*?Date:\s+(?<Load_Time>\(REX))" 
| eval it = strptime(Load_Time, "%Y/%m/%d") 
| eval ot = strptime(Time, "%Y/%m/%d %I:%M:%S") 
| eval it2 = strftime(it, "%Y/%m/%d") 
| eval ot2 = strftime(ot, "%Y/%m/%d")
| eval it3 = strptime(it2, "%Y/%m/%d")  
| eval ot3 = strptime(ot2, "%Y/%m/%d")
| eval nowstring=strftime(now(), "%Y-%m-%d")
| eval nowstring2=strptime(nowstring, "%Y-%m-%d")
| eval TD= (nowstring2- it3)/86400
| dedup MM, it2 
| sort -Load_Time 
| stats list(LIC) as LIC count list(MM) AS MM , list(it2) AS Current_Bundle_Date , list(nowstring) AS Search_Date(Today) , list(TD) AS Difference , sum(TD) AS Sum by Bundle
| eval Ma_Months= Sum/30.4
| sort -Bundle
| lookup Report_lookup.csv "Bundle Version" as "Bundle" | where Release != "INTERNAL"
Tags (4)
0 Karma

lguinn2
Legend

Try this

 index=* sourcetype=mysourcetype "search"   
 | rex "(?<Bundle>\s+\d+\.\d+\.\d+\.\d+)"
 | rex "(?m)Package:\s+SEA.ha(?:\n|.)*?Package Level:\s+(?<LIC>\(REX))" 
 | rex "(?m)Package:\s+SEA.ha(?:\n|.)*?MM:(?<MM>\(REX))" 
 | rex "(?m)Current Bundle.*?(?:\n|.)*?Date:\s+(?<Load_Time>\(REX))" 
 | bin span=d Load_Time as it
 | eval it_fmt = strftime(it, "%Y/%m/%d") 
 | eval now_fmt=strftime(now(), "%Y-%m-%d")
 | eval TD= (relative_time(now(),"@d")- it)/86400
 | dedup MM, it
 | sort -Load_Time 
 | stats list(LIC) as LIC count list(MM) AS MM , list(it_fmt) AS Current_Bundle_Date , list(now_fmt) AS "Search_Date(Today)" , list(TD) AS Difference , sum(TD) AS Sum by Bundle
 | eval Ma_Months= Sum/30.4
 | lookup Report_lookup.csv "Bundle Version" as "Bundle" 
 | fillnull value="Unknown" Release
 | where Release != "INTERNAL"

In tying to understand the search, I simplified it somewhat by eliminating unneeded steps. But the significant difference is the addition of the fillnull command: this will make sure that every result has a value for the Release field. This may give a very different result and help you see what is going on here.

0 Karma

woodcock
Esteemed Legend

Looks fine to me; it should work. The key is the last clause: | where Release != "INTERNAL". This should drop all events that do not have a field named Release (which would correspond to those events whose Bundle value does not exist with a matching value as Bundle Version inside the CSV) and also those that have a value of "INTERNAL".

0 Karma

woodcock
Esteemed Legend

What is the first line in your CSV file (headers)?

0 Karma

raby1996
Path Finder

The headers are as follows ( in order)

Build, Bundle Version, Created, Description , Group , LastUpdate, Release

where Bundle Version is the one i need to act as a filter for the values of Bundle in my search,

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