Splunk Search

How to edit this search to remove the time for maintenance windows using a CSV file?

banderson7
Communicator

We've got a search that displays our web monitor logs, and would like to add a function that allows us to remove time for maintenance windows via a CSV file. I ran across this question/answer that talked about what I'm trying to do, but I haven't made it work yet:
https://answers.splunk.com/answers/129068/using-lookup-tables-to-exclude-timerange-from-search.html

The search to display the monitoring results is:

(source=/home/splunk/scripts/rigor/18326_xxxxx_kansasuptime-NG5.xml) index="webmonitor" sourcetype="_xml"     
| xmlkv    
| rename average-response-time AS AvgResponseTime , error-count AS Errors, percentage-uptime AS Uptime, maximum-response-time AS MaxResponse, minimum-response-time AS MinResponse , run-count AS RunCount    
|bucket _time span=1w     
|eval Max=round(MaxResponse/1000,2)       
|eval Min=round(MinResponse/1000,2)       
|eval AvgResponse=round(AvgResponseTime/1000,2)    
|eval Avg_Uptime=round(Uptime,2)    
|eval Time=strftime(_time,"%m/%d/%y    %H")    
|rex field=source "/home/splunk/scripts/rigor/(?<Rigor_Feed>.+)"    
|eventstats sum(RunCount) as TotalRunCount by source  
|stats avg(Uptime) AS Availability last(Avg_Uptime) AS "Last_Avg_Uptime" sum(RunCount) as RunCount last(TotalRunCount) as TotalRunCount sum(Errors) as Errors last(AvgResponse) AS Performance  max(Max) as WorstResponse min(Min) as BestResponse avg(Max) as AvgMax avg(Min) as AvgMin  by Rigor_Feed, Time  
| rename Rigor_Feed AS Measurement 
| eval Measurement=case(Measurement="18324_xxxxxxxxxxxuptime-NG5.xml","xxxxxxxxxxx",Measurement="18325_xxxxx-xxxxx,"Ally",Measurement="19345_xxxxx_xxxxxx_xxxxxx_Performance_5.xml","Common Ground",Measurement="23229_xxxxxo_tufts_uptime_5.xml","xxxxx",Measurement="18066_xxxxx_xxxxxxxx-uptime-NG5.xml","Ome Resa",Measurement="18326_xxxxx_kansasuptime-NG5.xml","Kansas",Measurement="19027_xxxxxx-SC-Prod.xml","Topaz xxxxxx-SC",Measurement="15165_Topaz_xxxxx-Prod.xml","Topaz xxxxxx",Measurement="19029_xxxxx_AZ_Nextgen9.xml","Arizona 9",Measurement="8145_Topaz-xxxxx-Prod.xml","Topaz PCMS",1=1,"Unknown") 
| fields Measurement Availability RunCount Errors

Which displays:

Measurement Availability    RunCount    Errors
xxxxxx       99.183007     1777     15
xxxxxx       100.000000   173        0

When I add the search from that linked answer, no results appear. This is what I added to the above search:

| search [inputlookup xxxxx_Web_Maint_Test5.csv
| eval start=strptime(Date." ".Starttime,"%m/%d/%Y %H:%M %p")
| eval end=strptime(Date." ".Endtime,"%m/%d/%Y %H:%M %p")
| eval search = "(_time "+end+")"  
| fields search 
| mvcombine search
| eval search= "(" + mvjoin(search, " ") + ")"]

Contents of xxxxx_Web_Maint_Test5.csv:

Check                        Column4   Column5   Column6   Endtime            Starttime
18326_xxxxx_kansasuptime-NG5                             09/19/2016 5:00 AM   09/19/2016 3:00 AM

Boy I hope that was enough information :). Can someone tell me what I'm doing wrong, and if this is the wrong way to do what I want to do? Thanks for sticking through this :).

0 Karma

sundareshr
Legend

Try this

source=/home/splunk/scripts/rigor/18326_xxxxx_kansasuptime-NG5.xml) index="webmonitor" sourcetype="_xml"     
 | search [inputlookup xxxxx_Web_Maint_Test5.csv
 | eval start=strptime(Starttime,"%m/%d/%Y %H:%M %p")
 | eval end=strptime(Endtime,"%m/%d/%Y %H:%M %p")
 | eval search = "(_time>"+start+" AND _time<"+end+")"  
 | fields search 
 | mvcombine search
 | eval search= "(" + mvjoin(search, " ") + ")"]
 | xmlkv    
 | rename average-response-time AS AvgResponseTime , error-count AS Errors, percentage-uptime AS Uptime, maximum-response-time AS MaxResponse, minimum-response-time AS MinResponse , run-count AS RunCount    
 |bucket _time span=1w     
 |eval Max=round(MaxResponse/1000,2)       
 |eval Min=round(MinResponse/1000,2)       
 |eval AvgResponse=round(AvgResponseTime/1000,2)    
 |eval Avg_Uptime=round(Uptime,2)    
 |eval Time=strftime(_time,"%m/%d/%y    %H")    
 |rex field=source "/home/splunk/scripts/rigor/(?<Rigor_Feed>.+)"    
 |eventstats sum(RunCount) as TotalRunCount by source  
 |stats avg(Uptime) AS Availability last(Avg_Uptime) AS "Last_Avg_Uptime" sum(RunCount) as RunCount last(TotalRunCount) as TotalRunCount sum(Errors) as Errors last(AvgResponse) AS Performance  max(Max) as WorstResponse min(Min) as BestResponse avg(Max) as AvgMax avg(Min) as AvgMin  by Rigor_Feed, Time  
 | rename Rigor_Feed AS Measurement 
 | eval Measurement=case(Measurement="18324_xxxxxxxxxxxuptime-NG5.xml","xxxxxxxxxxx",Measurement="18325_xxxxx-xxxxx,"Ally",Measurement="19345_xxxxx_xxxxxx_xxxxxx_Performance_5.xml","Common Ground",Measurement="23229_xxxxxo_tufts_uptime_5.xml","xxxxx",Measurement="18066_xxxxx_xxxxxxxx-uptime-NG5.xml","Ome Resa",Measurement="18326_xxxxx_kansasuptime-NG5.xml","Kansas",Measurement="19027_xxxxxx-SC-Prod.xml","Topaz xxxxxx-SC",Measurement="15165_Topaz_xxxxx-Prod.xml","Topaz xxxxxx",Measurement="19029_xxxxx_AZ_Nextgen9.xml","Arizona 9",Measurement="8145_Topaz-xxxxx-Prod.xml","Topaz PCMS",1=1,"Unknown") 
 | fields Measurement Availability RunCount Errors
0 Karma

banderson7
Communicator

I get an unbalanced quotes using this search, and when I remove the " after line 5: | eval search = "(_time>"+start+" AND _time<"+end)", I get Error in 'eval' command: The expression is malformed. Thanks for looking and suggestions.

0 Karma

sundareshr
Legend

Try the updated query

0 Karma

banderson7
Communicator

After I pulled out the ) from the first line, it's a lot happier. However, it's giving me funny results. When the search is run querying the csv I get 23 checks total. Looks like it's only showing the number of checks in the time period specified in the csv. What I'd like to do is to remove those checks and their associated data from the search results altogether.

0 Karma

sundareshr
Legend

Change

| search [inputlookup xxxxx_Web_Maint_Test5.csv

TO

| search NOT [inputlookup xxxxx_Web_Maint_Test5.csv
0 Karma

banderson7
Communicator

No results found, unfortunately.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...