how to change date_wday for different countries

[deepthi5]

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

[deepthi5 · Accepted solution]

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

[woodcock]

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

[deepthi5]

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

[woodcock]

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.

[deepthi5]

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

[deepthi5]

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