topic Re: How to edit this search to remove the time for maintenance windows using a CSV file? in Splunk Search

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

banderson7 — Tue, 29 Sep 2020 11:02:55 GMT

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

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

sundareshr — Tue, 20 Sep 2016 21:53:32 GMT

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

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

banderson7 — Wed, 21 Sep 2016 01:50:37 GMT

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.

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

sundareshr — Wed, 21 Sep 2016 03:58:32 GMT

Try the updated query

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

banderson7 — Wed, 21 Sep 2016 14:29:49 GMT

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.

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

sundareshr — Wed, 21 Sep 2016 14:33:23 GMT

Change

| search [inputlookup xxxxx_Web_Maint_Test5.csv

| search NOT [inputlookup xxxxx_Web_Maint_Test5.csv

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

banderson7 — Wed, 21 Sep 2016 17:56:54 GMT

No results found, unfortunately.