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 :).
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
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.
Try the updated query
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.
Change
| search [inputlookup xxxxx_Web_Maint_Test5.csv
TO
| search NOT [inputlookup xxxxx_Web_Maint_Test5.csv
No results found, unfortunately.