how to avoid searching with source file name

deepthi5 — Tue, 29 Sep 2020 07:32:29 GMT

Hi Experts,

I need your help in the following scenario

1.I have 200 routers configured to feed splunk daily for generating network usage dashboards

Scenario:
1.Downloading all 200 excels as splunk do not accept excel format converting it to csv and feeding splunk with the daily data
2.Generated a look up table
SNO country start_hour end_hour receivebandwidth transmitbandwidth router sitename tier threshold start_wday end_wday
1 C:\xxx\rdatsalzbu010-3-1.xxx.com.csv 8 17 40 40 atsalzbu010-3-1 salzbu tier1 70% 1 7
2 C:\xxx\rdatsalzbu010-3-2.xxx.com.csv 8 17 40 40 atsalzbu010-3-2 salzbu tier1 70% 1 7
3 C:\xxx\rdgbmother010-1-1.xxx.com.csv 0 24 10 10 gbmother010-1-1 mother tier1 70% 1 7
4 C:\xxx\rdgbmother010-1-2.xxx.com.csv 0 24 10 10 gbmother010-1-2 mother tier1 70% 1 7
8 C:\xxx\rdilraanan010-4-2.xxx.com.csv 8 19 80 80 ilraanan010-4-2 raanan tier1 70% 7 4
9 C:\xxx\rdinchenna010-1-1.xxx.com.csv 9 19 10 10 inchenna010-1-1 chennai tier1 70% 1 5
10 C:\xxx\rdinchenna010-1-2.xxx.com.csv 9 19 10 10 inchenna010-1-2 chennai tier1 70% 1 5
11 C:\xxx\rdinmumbai010-7-1.xxx.com.csv 0 24 50 50 inmumbai010-7-1 mumbai tier1 70% 1 7
12 C:\xxx\rdinmumbai010-7-1.xxx.com.csv 0 24 45 45 inmumbai010-7-1 mumbai tier1 70% 1 7
13 C:\xxx\rdinmumbai010-7-2.xxx.com.csv 0 24 40 40 inmumbai010-7-2 mumbai tier1 70% 1 7
14 C:\xxx\rdinmumbai010-7-2.xxx.com.csv 0 24 50 50 inmumbai010-7-2 mumbai tier1 70% 1 7

and the dashboard query

source="C:\xxx\rus-dayha-5.xxx.com.csv" OR source="C:\xxx\rus-dayha-5.xxx.com.csv" OR source="C:\xxx\rus-daywtc-2.xxx.com.csv" OR source="C:\xxx\rus-daywtc-5.xxx.com.csv" OR source="C:\xxx\rus-daywtc-5.xxx.com.csv" OR source="C:\xxx\rus-daywtc-5.xxx.com.csv" OR source="C:\xxx\rusxwalmartedc.csv" OR source="C:\xxx\rusxwalmartNDC.csv" OR source="C:\xxx\rdarbaires010-14-1.xxx.com.csv" OR source="C:\xxx\rdsariyadh010-1-1.xxx.com.csv" OR source="C:\xxx\rdthbangko010-5-1.xxx.com.csv" OR source="C:\xxx\rdtristanb020-1-1.xxx.com.csv" OR source="C:\xxx\rdtwkaohsi010-18-1.xxx.com.csv" OR source="C:\xxx\rdtwtaipei010-15-1.xxx.com.csv" OR source="C:\xxx\rdusaviejo010-1-1.xxx.com.csv" OR source="C:\xxx\rdusconcor010-2-1.xxx.com.csv" OR source="C:\xxx\rdusfbranc010-1-1.xxx.com.csv" OR source="C:\xxx\rdusflaude010-3-1.xxx.com.csv" OR source="C:\xxx\rdusomaha010-2-1.xxx.com.csv" OR source="C:\xxx\rdusreno010-1-1.xxx.com.csv" OR source="C:\xxx\rdustucson010-1-1.xxx.com.csv" OR source="C:\xxx\rduswarren010-1-1.corp.xxx.com.csv" OR source="C:\xxx\rdcnshangh030-5-1.csv" OR source="C:\xxx\rdcnxian010-1-1.xxx.com.csv" OR source="C:\xxx\rddehannov010-5-1.xxx.com.csv" OR source="C:\xxx\rdusnyork020-4-1.xxx.com.csv" OR source="C:\xxx\rdinchenna020-1-1.xxx.com.csv" OR source="C:\xxx\rdinndelhi010-18-1.xxx.com.csv" OR source="C:\xxx\rdinsecund010-5-2.xxx.com.csv" OR source="C:\xxx\rdmyklumpu010-19-1.xxx.com.csv" OR source="C:\xxx\rdmyklumpu010-19-1.xxx.com.csv" host="SEZ00VVM-153" index="xxx" sourcetype="csv" |dedup _raw| rex field=source "(?<country>.*?)$"|lookup siteinfo.csv country OUTPUT start_hour end_hour receivebandwidth sitename tier router start_wday end_wday|eval date_wday=strftime(_time,"%u")|search tier=tier1|where date_hour>=start_hour AND date_hour<= end_hour AND date_wday>=start_wday AND date_wday<=end_wday|eval Intraffic=In/1048576|eval Outtraffic=Out/1048576|eval result=(Intraffic)+(Outtraffic)|bin _time span=1d| stats values(receivebandwidth) as maxin ,perc95(result) AS Percentile by router _time |eval total=Percentile/maxin*100|timechart limit=100 span=1d avg(total) As siteTotalPct by router

so based on the source path name i am getting all the sitename tiers etc but my query has become so big adding all the 200 source paths can some one help me how to get rid of this

Re: how to avoid searching with source file name

piUek — Fri, 09 Oct 2015 14:31:42 GMT

Can't you put them in the separate index and search without using source?
Or maybe there is something they have in common and You could use wildcards?

Re: how to avoid searching with source file name

Yasaswy — Tue, 29 Sep 2020 07:32:31 GMT

Hi deepthi,
There are multiple ways you can handle this. A few here...

If you have the option to reorganize the input, You can use a separate "unique" sourcetype and you can just search by sourcetype="XXXX" or if it makes sense in your env... even get this data in a separate index as being suggested by piUek

You can maintain "tags" and just use the tag instead of source. Eg: Create a tag "myrtrs" and maintain all your sources there.
Tag Name: myrtrs -- source=abc1,source=abc2... etc

You can name your sources logically. Eg: If you prefeix your csv files with something unique like myrtrs_yourcurrentname you can just use a wild card
source=myrtrs_* ....

topic how to avoid searching with source file name in Getting Data In

how to avoid searching with source file name

Re: how to avoid searching with source file name

Re: how to avoid searching with source file name