How to compare data from the first day last month with the first day of the current month and show results if there is a change in particular field without using join?

[srinathd]

how to compare last month firstday data with current month firstday data and give the results if there is a change in particular field without using join? I am using the timerangepicker to select the date range in the dashboard. I am considering the earliest date as last month first day and latesttime as current month first day

index="test_si" search_name=menu environment=$env$ 
| eval latest="$latest$"  
| eval enddate=if(latest=="now","@d",latest)  
| eval epochdayend=if(isnum(enddate), enddate, relative_time(now(), enddate))  
| eval ldayend= strftime(epochdayend,"%Y%m%d")  
| lookup HolidayList Date as ldayend OUTPUT PreviousWDay as epochday  
| eval epochdayend=if(isnull(epochday),ldayend,epochday)  
| eval dateepoch=strftime(_time,"%Y%m%d")  | where dateepoch=epochdayend | eval DATE=DATE_TIME 
| eval conc = coalesce(Menu_Id,"").coalesce(APPLICATION,"").coalesce(CO_CODE,"") 
| table Menu_Id DESCRIPTION APPLICATION CO_CODE DATE conc | dedup Menu_Id APPLICATION
| join type=outer conc  [ search index="test_si" search_name=menu environment=$env$
| eval begindate="$earliest$"  
| eval epochdaystart=if(isnum(begindate), begindate, relative_time(now(), begindate))  
| eval ldaystart= strftime(epochdaystart,"%Y%m%d")  
| lookup HolidayList Date as ldaystart OUTPUT NextWDay as epochday 
| eval epochdaystart=if(isnull(epochday),ldaystart,epochday)  
| eval dateepoch=strftime(_time,"%Y%m%d")   | where dateepoch=epochdaystart | eval DATE=DATE_TIME
| eval conc = coalesce(Menu_Id,"").coalesce(APPLICATION,"").coalesce(CO_CODE,"") 
| eval conc1 = conc | table Menu_Id DESCRIPTION APPLICATION CO_CODE DATE conc conc1 | dedup Menu_Id APPLICATION] 
| where NOT conc = conc1  | sort 0 Menu_Id 
| fieldformat DATE=strftime(strptime(DATE,"%y%m%d%H%M"),"%d-%b-%Y") 
| fields - conc conc1 begindate dateepoch epochdaystart epochdayend enddate latest epochday ldayend ldaystart

[dolivasoh]

Try loading the values for the field you're comparing into a multi-valued list. Then you can compare the adjacent value without a join.

stats list(conc) as conc by Menu_Id DESCRIPTION APPLICATION CO_CODE DATE conc1 | eval yes = if(match(mvjoin(conc, " "), "conc_day1_value (conc_day2_value){1,2}?"), "True", "False") | search yes=True

What this does is reads the list and looks for the change in value you specify (in bold) between 1-2 times and tags the event with a field "yes" with a value "True" or "False". You'll probably need to change the fields here a bit but the logic should render what you're looking for.

[ppablo]

Hi @srinathd

Here's a post that might be helpful as reference:
http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi...

[srinathd]

I have to compare single sourcetype data. i have used join to get the data but it is taking much time. I need an alternative for join to get the results

[somesoni2]

Can you explain your query little bit? What is the use of HolidayList lookup?

[srinathd]

Hi.. I have used Holiday lookup to get the previous working day/next working day as the firstday of the month, only If the first day of the month is a holiday. I have removed that part from the query. I have to get the results based on the change in concatenated value (conc). I tried with join to get the results. it is working fine but taking much time. What is the alternative for join in this case?

[srinathd]

index="test_si" search_name=menu environment=$env$
| eval latest="$latest$"

| eval enddate=if(latest=="now","@d",latest)

| eval epochdayend=if(isnum(enddate), enddate, relative_time(now(), enddate))

| eval dateepoch=strftime(_time,"%Y%m%d") | where dateepoch=epochdayend | eval DATE=DATE_TIME
| eval conc = coalesce(Menu_Id,"").coalesce(APPLICATION,"").coalesce(CO_CODE,"")
| table Menu_Id DESCRIPTION APPLICATION CO_CODE DATE conc | dedup Menu_Id APPLICATION
| join type=outer conc [ search index="test_si" search_name=menu environment=$env$
| eval begindate="$earliest$"

| eval epochdaystart=if(isnum(begindate), begindate, relative_time(now(), begindate))

| eval dateepoch=strftime(_time,"%Y%m%d") | where dateepoch=epochdaystart | eval DATE=DATE_TIME
| eval conc = coalesce(Menu_Id,"").coalesce(APPLICATION,"").coalesce(CO_CODE,"")
| eval conc1 = conc | table Menu_Id DESCRIPTION APPLICATION CO_CODE DATE conc conc1 | dedup Menu_Id APPLICATION]
| where NOT conc = conc1 | sort 0 Menu_Id
| fieldformat DATE=strftime(strptime(DATE,"%y%m%d%H%M"),"%d-%b-%Y")

[srinathd]

is there any alternative to join in this case?

[MuS]

Sure there is, join should be considered as last resort not as first choice. Take this run everywhere example which compares the first day of last week with the first day two weeks ago:

index=_internal earliest=-2w@w sourcetype=splunkd date_mday=1 OR date_mday=8 
| bucket _time span=1d 
| stats last(_time) AS last_time count AS per_day_count by _time, host, sourcetype 
| eval weeks = if(last_time > exact(relative_time(now(),"-2w@w")) AND last_time <= exact(relative_time(now(),"-1w@w")) , per_day_count ,"0")
| eval week = if(last_time > exact(relative_time(now(),"-1w@w")) AND last_time <= exact(relative_time(now(),"-0w@w")) , per_day_count ,"0")
| where NOT weeks = week
| stats max(last_time) AS _time, values(sourcetype) AS sourcetype, max(week) AS 1w_ago, max(weeks) AS 2w_ago

I had to build this on weeks because of the 30 days retention time on index=_internal so adapt it to your needs...

cheers, MuS