Splunk Search
Highlighted

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?

Contributor

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
Highlighted

Re: 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?

Community Manager
Community Manager
0 Karma
Highlighted

Re: 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?

Contributor

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

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: 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?

Contributor

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?

0 Karma
Highlighted

Re: 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?

Contributor

index="testsi" searchname=menu environment=$env$
| eval latest="$latest$"

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

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

| eval dateepoch=strftime(
time,"%Y%m%d") | where dateepoch=epochdayend | eval DATE=DATETIME
| eval conc = coalesce(Menu
Id,"").coalesce(APPLICATION,"").coalesce(COCODE,"")
| table Menu
Id DESCRIPTION APPLICATION COCODE DATE conc | dedup MenuId APPLICATION
| join type=outer conc [ search index="testsi" searchname=menu environment=$env$
| eval begindate="$earliest$"

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

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

0 Karma
Highlighted

Re: 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?

Contributor

is there any alternative to join in this case?

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

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

Highlighted

Re: 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?

Contributor

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 MenuId DESCRIPTION APPLICATION COCODE DATE conc1 | eval yes = if(match(mvjoin(conc, " "), "concday1value (concday2value){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.

0 Karma