topic Re: how to change date_wday for different countries in Splunk Search

how to change date_wday for different countries

deepthi5 — Tue, 29 Sep 2020 06:59:32 GMT

Hi Team,

I have got a search query running the same query on different source files

ource="C:\Budapest Router1full.csv" OR source="C:\Adelaide Router1full.csv" OR source="C:\Network Analysis\tier1\rusxwalmartedc S0-0-0.csv" OR source="C:\Network Analysis\tier1\rdinmumbai010-7-1 S2-0.csv" OR source="C:\Network Analysis\tier2\rdingurgao010-5-1 f0-1.csv" OR source="C:\Network Analysis\tier3\rdinsecund010-5-2 Gig0-0.csv" OR source="C:\Network Analysis\tier2\rdphcebu010-5-1 f0-2-0.csv"host="SEZ00VVM-153" sourcetype="csv" | rex field=source "(?<country>.*?)$" | lookup datacentre.csv country OUTPUT start_hour end_hour receivebandwidth sitename tier | search sitename="cebu" tier=tier2 | where date_hour>=start_hour AND date_hour<= end_hour |eval Intraffic=IN/1048576 | bin _time span=1h | stats values(receivebandwidth) as maxin ,perc95(Intraffic) AS Percentile by _time | eval seventyperc= maxin*0.7 | eval eightyperc=maxin*0.8 | eval nightyperc=maxin*0.9

I have different sites working for different business days can i get this done using look up
example:Cebu :Monday-Friday
Mumbai-Sunday to saturday (24*7)
Us-monday -thursday

How can i do that using the search and declare in lookup file

Please Help me

Thanks and Regards,
Deepthi

Re: how to change date_wday for different countries

woodcock — Fri, 14 Aug 2015 15:59:19 GMT

You can have a lookup file like this with awork_wdays field and a work_wdays_strlen on the end that is the length (number of characters) in the work_wdays field:

 Country, ..... ,work_wdays,work_wdays_len
 Cebu, ..... ,Mon:Tue:Wed:Thu:Fri,19
 Mumbai, ..... ,Sun:Mon:Tue:Wed:Thu:Fri:Sat,27
 Us, ..... ,Mon:Tue:Wed:Thu,15

Then your search changes to this:

 source="C:Budapest Router1full.csv" OR source="C:Adelaide Router1full.csv" OR source="C:Network Analysistier1rusxwalmartedc S0-0-0.csv" OR source="C:Network Analysistier1rdinmumbai010-7-1 S2-0.csv" OR source="C:Network Analysistier2rdingurgao010-5-1 f0-1.csv" OR source="C:Network Analysistier3rdinsecund010-5-2 Gig0-0.csv" OR source="C:Network Analysistier2rdphcebu010-5-1 f0-2-0.csv"host="SEZ00VVM-153" sourcetype="csv" | rex field=source "(?<country>.*?)$" | lookup datacentre.csv country OUTPUT start_hour end_hour receivebandwidth sitename tier start_wday end_wday| eval date_wday=strftime(_time,"%a") | search sitename="cebu" tier=tier2 | where date_hour>=start_hour AND date_hour<= end_hour AND (len(trim(work_wdays,date_wday)) < work_wdays_len) |eval Intraffic=IN/1048576 | bin _time span=1h | stats values(receivebandwidth) as maxin ,perc95(Intraffic) AS Percentile by _time | eval seventyperc= maxin*0.7 | eval eightyperc=maxin*0.8 | eval nightyperc=maxin*0.9

Re: how to change date_wday for different countries

deepthi5 — Wed, 26 Aug 2015 07:40:58 GMT

Hi Woodcock,

This did not work for me may due to len function
For sun to saturday sites it worked fine But from Mon to friday sites Using this query it picked up only Monday samples and friday samples skipping all the data in between can you please help me

Re: how to change date_wday for different countries

deepthi5 — Wed, 26 Aug 2015 07:46:08 GMT

one more comment for monday to thursday it picked up monday tuesday and thursday skipping wednesay data

Re: how to change date_wday for different countries

woodcock — Wed, 26 Aug 2015 14:24:36 GMT

You data file must use only the following values when constructing date_wday:

Sun, Mon, Tue, Wed, Thu, Fri, Sat

If you are using any other capitalization or spelling, it will fail. I can only assume that your work_wdays strings are built such that the trim command will not find a match (see previous comment) or that your work_wdays_len calculation is wrong for some of them. Show me your datacentre.csv file's contents; that is probably where the problem exists.

Re: how to change date_wday for different countries

deepthi5 — Tue, 29 Sep 2020 07:04:44 GMT

sno,country,start_hour,end_hour,receivebandwidth,transmitbandwidth,sitename,tier,threshold,work_wdays,work_wdays_len
1,C:\Network Analysis\tier1\rusxwalmartedc S0-0-0.csv,0,24,1,1,Walmart,tier1,70%,Mon:Tue:Wed:Thu,15
2,C:\Network Analysis\tier1\rdinmumbai010-7-1 S2-0.csv,0,24,45,45,Mumbai,tier1,70%,Sun:Mon:Tue:Wed:Thu:Fri:Sat,27
3,C:\Network Analysis\tier2\rdingurgao010-5-1 f0-1.csv,0,24,20,20,Gurgaon,tier2,80%,Mon:Tue:Wed:Thu:Fri,19
4,C:\Network Analysis\tier3\rdinsecund010-5-2 Gig0-0.csv,0,24,20,20,Secunderabad,tier3,90%,Mon:Tue:Wed:Thu:Fri,19
5,C:\Network Analysis\tier2\rdphcebu010-5-1 f0-2-0.csv,0,24,40,40,Cebu,tier2,80%,Sun:Mon:Tue:Wed:Thu:Fri:Sat,27
6,C:\Network Analysis\tier1\rdgbreddit010-1-2 Gig0-2.csv,0,24,100,100,Redditch,tier1,70%,Sun:Mon:Tue:Wed:Thu:Fri:Sat,27

Re: how to change date_wday for different countries

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

Hi ,

That still did not work for me i have got an other solution for this

sourcetype="csv" |rex field=source "(?.*?)$"| lookup datacentre.csv country OUTPUT start_hour end_hour receivebandwidth sitename tier start_wday end_wday|eval date_wday=strftime(_time,"%u")|search tier=tier2|where date_hour>=start_hour AND date_hour<= end_hour AND date_wday>=start_wday AND date_wday<=end_wday|

start_wday end-wday can be given in look up table starting with 1 for Monday to 7 for Sunday