Need help in creating a query to get the result from one sourcetype and get other field values based on the output from the first sourcetype. For. e.g I am having the below output which shows the transaction_id as a result but the username corresponding to that transaction ID should be fetched from another sourcetype. Result of query should be -  _time hostname transaction_id username city 1:30AM server1 TEST cron_user US 1:31AM server2 TEST1 cron2_user CA   In above transaction_id is the field present in both sourcetype hostname and transaction_id is coming from one sourcetype. matching with specfific transaction_id , username and city should be fetched from sourcetype2   ... View more

Need help in creating splunk query to show value of fields as Zero having null values and for numeric it should show exact count.   For example -  I want to search for all the events if all fields having specific keywords I am searching. And for the others, if that keyword is not available in that field value , then it should as 0 count ... View more

Hi Team, I am trying to fetch the count and percentage of hosts having success and failures along with failure percentage. host = Server1 Server2 Server3 Server4 Server5 But if count of spcecific host is having no event, I want to show that as well even with result =0. I am running below query but it is missing some servers because there are no events on specific sevrers form last 2 hours. index=server_list host IN (Server1,Server2,Server3,Server4,Server5) events_status = "*" | eval pass=if(like(event_status,"20%"),1,0) | eval fail=if(!like(event_status,"20%"),1,0) | stats count as Overall_Volume,sum(pass) as Passed, sum(fail) as Failed by host | eval Failure_Rate=round(Failed_Requests/(Passed_Requests+Failed_Requests)*100,2) | fillnull value=0 Below result I am getting, because Server4 and Server5 dont have any traffic from last 2 Hours - host          Overall_Volume Passed Failed Failure_Rate Server1       2                              1                1            50 Server2      10                             6               4            40 Server3       1                               0               1            100   Can anyone help in query so that I should get all Servers with values as 0 if no traffic.       ... View more

Hi Team, I have data with me as below.   2021-08-31 00:05:28|Test|Event|[c.f.d.aop.sql.database ] 2ms :testing8 2021-08-31 00:05:30|Test|Event|[c.f.d.aop.sql.database ] 1ms :testing1 2021-08-31 00:05:32|Test|Event|[c.f.d.aop.sql.database ] 12ms :testing3 2021-08-31 00:05:35|Test|Event|[c.f.d.aop.sql.database ] 20ms :testing5 2021-08-31 00:05:36|Test|Event|[c.f.d.aop.sql.database ] 102ms :testing9   I want to extract "ms" values from and based on these values want to create a timechart. Can anyone assist. ... View more

Hi, Can anyone help, As I want to get an alert if : The volume gets drop or if processing time gets increased of a specific server when being compared with last 5 minutes - The query should use volume and average response of current 5 minutes and last 5 minutes. and then if there is difference in volume < 50% or processing time > 60% then alert. ... View more

Hi , Can anyone help me- how to get average of the all the columns at the bottom. The output should be like -  ctime  totalaccesses: https://www.google.com minrespond: https://www.google.com averrespond: https://www.google.com totalaccesses: https://www.listofurls.com 7/31/2020 0:00 1500 665 56.22727273 18177 7/31/2020 1:00 500 779 47.76659039 692 7/31/2020 2:00 400 774 44.44514768 912 7/31/2020 3:00 9 648 50.61764706 722 7/31/2020 4:00 700 715 47.76427256 721 7/31/2020 5:00 560 1469 50.43421053 821 7/31/2020 6:00 620 1179 48.08455882 741 7/31/2020 7:00 740 688 61.37606838 790 7/31/2020 8:00 235 1322 45.85294118 1388 7/31/2020 9:00 789 559 47.125 327 Average should show average of above should show average of above should show average of above should show average of above I am using below query but it is not giving correct output (doubt if I am using correct query)- index=servers host="Datacenter01" websites IN ("https://www.google.com","https://www.listofurls.com","https://www.fishbucks.com") |bucket _time span=1h | convert timeformat=" %m/%d/%y %H:%M:%S" ctime(_time) AS ctime | stats count as totalaccesses min(responsd_time) as minrespond max(responsd_time) as maxrespond avg(responsd_time) as averrespond by ctime over websites | foreach *minrespond *maxrespond *averrespond [eval sum_<<FIELD>> = 0, count_<<FIELD>> = 0] | foreach *minrespond [eval sum_A_Field = sum_A_Field + '<<FIELD>>', count_A_Field = count_A_Field + 1] | foreach *minrespond *maxrespond *averrespond [eval avg_<<FIELD>> = sum_<<FIELD>> / count_<<FIELD>>]   ... View more

Hi Team,   Can you help as I want only selected url's to display in my query output. index=dev_env sourcetype="urldata" URL ="*" LoadTime="*" | timechart span=1m eval(round(avg(LoadTime),0)) as TimeUsedtoload by URL | fields + _time "https://www.pingtest.com/Logins/Login.aspx?testid=1578&actid=21047" https://www.pingtest.com/*/testing.aspx"   "https://www.othertest.com/Logins/*.aspx" The output includes all the URL's like - _time     https://www.servermonitor/server.aspx?filetype_id=474&mode=new       https://www.pingtest.com/Testdata.aspx     https://www.pingtest.com/Logins/Login.aspx?testid=1578&actid=21047     and_other_multipleurls I want to display only URL's which are like " https://www.pingtest.com/Logins/Login.aspx?testid=1578&actid=21047" " https://www.pingtest.com/Logins/Login.aspx" " https://www.othertest.com/Logins/Login.aspx?testid=1578&" and from above which are having not null values.     ... View more

Hi, I am facing some difficulty in achieving below. Can anyone help. I am getting 0 in the columns only and no other data index=dev_env sourcetype="urldata" URL ="*" LoadTime="*" | eval url_name= case(URL like "https://www.pingtest.com/server/server.aspx%" , "ServerLogin", URL like "https://www.servermonitor/clients/hostname/server.aspx?filetype_id=474&mode=new%","Servers", URL like "https://www.pingtest.com/clients/User/Testdata.aspx%" ,"ServersPing" URLlike "https://www.pingtest.com/mobileusers/Logins/Login.aspx?testid=1578&actid=21047%","MobilePing",URL like "https://www.pingtest.com/User/newuser.aspx?%","NewUserPing",1==1,0) | timechart span=1m  eval(round(avg(LoadTime),0)) as TimeUsedtoload by url_name  ... View more

Hi Team, Can anyone help me on this - I want to Get columns that have non-zero values over time (using timechart). _time Column1 Column2 Column3 Column4 Column5 Column N 2/14/2020 2:11 0 0 0 0 0 0 2/14/2020 2:12 0 0 0 0 0 0 2/14/2020 2:13 1 0 0 0 0 0 2/14/2020 2:14 0 0 1 0 0 0 2/14/2020 2:15 0 0 0 5 0 0 2/14/2020 2:16 0 0 0 0 0 0 2/14/2020 2:17 0 0 0 0 0 0 2/14/2020 2:18 0 0 0 0 0 0 The query I am using (But I am not able to remove zero value columns ) index=servers sourcetype=server_list Columns ="*" | timechart span=1m count as Total by Columns | where Columns > 0 ... View more

Hi, I have two fields with different values and I want count on both basis. These are events and hosts occured in log files. Example - I have fields like host and event. Host values are like A , B, C,D. Events are like reboot , running, shutdown. And the results should show like - _time A:reboot A:shutdown B:reboot C:running 00:01 1 2 1 4 00:02 2 4 3 1 00:03 0 1 4 5 00:04 1 2 0 1 ... View more

How to show count of events by host as well as total count (both per minute in same search) ... View more

Can anyone please help what could be the equivalent to SQL's "percent_Rank" command in splunk select host, count(*) "pings", TRUNC(PERCENT_RANK(200) WITHIN GROUP (ORDER BY proc_time) * 100, 2) || '%' "PingTime (% < 50 mSec)" from servers GROUP BY host ORDER BY 2 DESC ... View more

Hi Guys, Can anyone please help me in the below search. I want the name of all logfiles with details of keywords from each sourcetype. If there is a keyword present in the specific log file then the last time when that keyword was there in the log file. Log_Name | Updatedago | Login | Logout | Expire Server.log | Last Login event at "TimeFrame"| Last LogOut event at "TimeFrame"| Last Expire event at "TimeFrame" Server1.log | Last Login event at "TimeFrame"| Last LogOut event at "TimeFrame"| Last Expire event at "TimeFrame" The Login , Logout, Expire are the keywords available in _raw field. The timestamp field specifies the logs are updated how many minutes/hours ago(last updated time of log file) I am using below search but no results and not sure how to get last updated thing: index=serverlogs source=server*.log | eval status=if(_raw LIKE "*Login*" ,Login, _raw LIKE "*LogOut*","Logout", _raw LIKE "*Expire*","Expire",0 ) ... View more

Can anyone please help? I want to display the total count of events occurred in a week (but excluding specific day/time i.e. exclude 04-06 PM for Monday and 06-08 PM for Wednesday) I am running below search, but it doesn't seem to be excluding the counts for specific time because if I am trying to remove the excluding condition, I am still getting the same results. sourcetype=web_server events IN ("GET", "Delete") | eval myHour=strftime(_time, "%H") | eval myMinute=strftime(_time, "%M") | eval day_of_week = strftime(_time,"%A") | where NOT ( (day_of_week = "Monday" AND myHour= 4 AND myMinute>=0) OR (day_of_week = "Monday" AND myHour=5) OR (day_of_week = "Monday" AND myHour=6 AND myMinute<=59) OR (day_of_week = "Wednesday" AND myHour= 6 AND myMinute>=0) OR (day_of_week = "Wednesday" AND myHour=7) OR (day_of_week = "Wednesday" AND myHour=8 AND myMinute<=59)) | stats count as Total_events avg(duration) as Duration_of_events by events ... View more

Hi Experts, Can anyone help me in converting below sql query to splunk query.I really dont have any clue for percentile_cont thing. SELECT class_id,round(avg(percent/100,2) as percentage, round(percentile_cont(0.05) within group (order by percent/100),3) as AmarksPerntl, round(percentile_cont(0.09) within group (order by percent/100),3) as BmarksPerntl from myTable group by class_id order by 2 desc ... View more

Hi Team, Need help in creating a query. I want to display 0 when no data/events found. But I am getting "No results found. Try expanding the time range." either by using "fillnull value =0" or "eval Data=if(isnull(Data),0,Data)". but no result. I am using query as : sourcetype=systems earliest=-15m | timechart span=1m count as Data | eval Data=if(isnull(Data),0,Data) OR sourcetype=systems earliest=-15m | timechart span=1m count as Data | fillnull value=0 Data ... View more