Splunk Search
Highlighted

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

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
Highlighted

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

Esteemed Legend

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

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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