Splunk Search

how to change date_wday for different countries

deepthi5
Path Finder

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

Tags (2)
0 Karma
1 Solution

deepthi5
Path Finder

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

View solution in original post

0 Karma

deepthi5
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

deepthi5
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

deepthi5
Path Finder

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

0 Karma

deepthi5
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...