Hi, I am a Splunk User and been using it for a few months now, I have created a query which creates a table of count of orders of different payment types. index=idx sourcetype=order "successfully created order" "[paymentinfo]" | chart count AS PayPal by _time span=1h | join type=outer _time [search index=tomcat sourcetype=order "successfully created order" "[StoreCardPaymentInfo]" | chart count AS StoreCard by _time span=1h] | join type=outer _time [search index=tomcat sourcetype=order "successfully created order" "[GiftCard]" | chart count AS GiftCard by _time span=1h ] | join type=outer _time [search index=tomcat sourcetype=order "successfully created order" Card_type!=NULL | timechart count(Card_type) by Card_type limit=0 span=1h | rename amex as Amex, maestro as Maestro, master as Mastercard, visa as VISA, diners as Diners] | fillnull It currently lists the hourly report of the orders If I want to make that report daily instead of hourly, I added | eval Date=strftime(_time, "%d-%m-%Y") | fields- _time | table Date VISA PayPal Mastercard Maestro GiftCard StoreCard Amex and replaced time span by 1d from 1h, but the issue here is that it doesnt reflect the total count throughout the day, it only shows the total count for the first payment method i.e. PayPal which is the first in the multiple joins so I am guessing it has something to do with the joins And also the query I have written seems to work fine, is there anything I can change in it or improve? Please help! ... View more

Hi, I want to create an alert where, I want to raise an alert if the count of total number of errors is greater than 10, then i want to print a table with error count on each host. Query - index=weblogs host="nlhypsewb*.*" sourcetype=web "The timeout specified has expired" earliest=-15m | stats count(host) as COUNT | where COUNT > 4 This query gives me correct results but it only gives me a column with total count But when this query is giving some results then i want to generate an email which shows the count of errors on each host. How do I add those details in this query without using a Case if possible? I want to know if there is another option apart from using a switch Case. ... View more

Hi, I have created a query to fetch the status of some jobs in a particular format. There are different scheduled jobs which run in the environment which we want to monitor, some of these runs once daily and some of the runs every 5 or 10 or 30 mins etc. Query- index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Starting | table CronJobName _time | rename _time as time1 | eval Actual_Start_Time=strftime(time1,"%d-%m-%Y %H:%M:%S") | Join [ search index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Finished | table CronJobName _time | rename _time as time2 | eval Actual_End_Time=strftime(time2,"%d-%m-%Y %H:%M:%S") ] | table CronJobName Actual_Start_Time Actual_End_Time|dedup CronJobName | join CronJobName type=inner[|inputlookup CronJobLookup.csv] | dedup CronJobName | table CronJobName Job_Frequency_min Actual_Start_Time Expected_Start_Time Actual_End_Time Expected_End_Time | eval epoch_a=now() | eval CurrentDate=strftime(now(),"%d-%m-%Y") | eval epoch_b=strptime(CurrentDate." ".Expected_Start_Time,"%d-%m-%Y %H:%M:%S"), ExpectedStart=strftime(epoch_b,"%d-%m-%Y %H:%M:%S"), CurrentTime=strftime(epoch_a,"%d-%m-%Y %H:%M:%S") | eval epoch_c=strptime(CurrentDate." ".Expected_End_Time,"%d-%m-%Y %H:%M:%S"), ExpectedEnd=strftime(epoch_c,"%d-%m-%Y %H:%M:%S"), CurrentTime=strftime(epoch_a,"%d-%m-%Y %H:%M:%S") | table CronJobName Job_Frequency_min Actual_Start_Time ExpectedStart Actual_End_Time ExpectedEnd | eval Expected_Start=strftime(Expected_Start_Time, "%H:%M:%S") | eval Expected_End=strftime(Expected_End_Time, "%H:%M:%S") | eval Status = case( Actual_End_Time > ExpectedEnd AND isnotnull(Actual_End_Time), "Over Run", Actual_End_Time < ExpectedEnd AND isnotnull(Actual_End_Time), "OK", isnull(Actual_Start_Time), "Not Run") Issue 1: currently for the jobs with frequency as Once, i am getting the result as : CronJobName Job_Frequency_min Actual_Start_Time ExpectedStart Actual_End_Time ExpectedEnd Status staffAuditFeedJob Once 21-11-2017 06:30:01 21-11-2017 05:30:00 21-11-2017 06:30:02 21-11-2017 06:30:00 Over Run here the difference between expected and actual end time is only 2 secs yet the status says Over Run, so i want to give some lead time for 5 mins. like if the job finishes after 5 mins i should get the over run status if it finishes within 5 mins over the expectedend time then it should show as OK. Issue 2: For the jobs with frequency other than 1, say the frequency is every 10 min so the expected start and expected end times should vary for each run. Or the status should be more accurate based on some calculations of the time and frequency of the job, which I am not able to get. Can you please suggest? ... View more

Hi, I have written a search query which generates alert every 1hr and gets the information of some jobs depending on their time of last execution. Now the issue I am facing is that when my 1st alert is generated and it has the info of say jobA which has not run on its expected time, in the second alert the jobA will still be there if it still hasn't run yet. So I want to modify my search query to generate alert for 1 job only once and not include that particular job in every alert again and again if it is being returned based on the same criteria. Search QUery: index=tomcat [Job] cronjob earliest=@d| sort CronJobName | search Action="Starting" | eventstats latest(_time) as _time by CronJobName | eval Time_Diff_Min=(now()-_time)/60 | eval _time=strftime(_time,"%d-%m-%Y %H:%M")|eval Time_Diff_Min=round(Time_Diff_Min)|dedup Action CronJobName | join CronJobName type=inner[|inputlookup CronJobLookup.csv] | table Job_Frequency_min Action _time Time_Diff_Min CronJobName Expected_Start_Time | eval epoch_a=now()| eval CurrentDate=strftime(now(),"%d-%m-%Y")|eval epoch_b=strptime(CurrentDate." ".Expected_Start_Time,"%d-%m-%Y %H:%M"),ExpectedTime=strftime(epoch_b,"%d-%m-%Y %H:%M"), CurrentTime=strftime(epoch_a,"%d-%m-%Y %H:%M") |eval Time_Diff_Hour=Time_Diff_Min/60|eval Time_Diff_Hour=round(Time_Diff_Hour,1)|eval Job_Freq_Num=tonumber(Job_Frequency_min)|eval tt=if(Job_Frequency_min!="Once",Job_Freq_Num+60,0)|eval CronJobAlert=case(Job_Frequency_min="Once" AND (Time_Diff_Hour>24.5) ,1,Job_Frequency_min!="Once" AND (Job_Freq_Num+10 ... View more

Hi, i want to combine the results from my search query with a lookup table that i have uploaded. They both have 1 column in common Search Query: index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Starting | table CronJobName _time | rename _time as time1 | eval StartTime=strftime(time1,"%m/%d/%y %H:%M:%S") | Join [ search index=tomcat source ="/files0/nlhyp*" [Job] cronjob earliest=@d Action=Finished | table CronJobName _time | rename _time as time2 | eval EndTime=strftime(time2,"%m/%d/%y %H:%M:%S") ] | table CronJobName StartTime EndTime|dedup CronJobName Sample output: Lookup file: CronJobLookup.csv Sample output: i have tried both of them individually and they work perfectly fine, so there is no issue with the current query. The column which is common in both is called "CronJobName" I want to join both these and create a table which has columns- CronJobName Expected_STart_Time Expected_End_Time StartTime EndTime Job_Frequency etc.. ... View more