How to create Dynamic Date selection to compare report results for the First day of month and last day of month

[puntershot]

Hi Everyone,
I am trying to compare viewers for first day of the month and Last day of month . Here in this below report, I am entering dates manually every time while running a report to compare viewer results. I want to avoid typing /choosing the dates every time to generate results, report has to take automatically or dynamically dates ( choosing First date of month and last date of month) and compare the results . In this way, I will schedule my report . Please help me out, how could I modify below report to schedule my report PS : "Viewers_Today" field has calendar days

index=* Client="HelloPunter" AND "Viewers_Today"="2019-04-30" OR Viewers_Today="2019-04-01" | stats values(Total_PCs) AS "Total PCs", values(Total_iPADs) AS "Total iPADs" values(Total_MACs) AS "Total MACs" by Viewers_Today | transpose header_field=Viewers_Today | rename column AS Category, 2019-04-01 AS "First_Day_Month", 2019-04-30 AS "Last_Day_Month"

Thank you,
Punter

[niketn]

@puntershot

• Is this for Dashboard or Report?
• Also do you want First Day of Previous month vs Last Day of Previous Month comparison?
• Or is it First Day of Previous month vs First Day of Current Month comparison?
• Or is it First Day of Previous month vs Current Day of Current Month comparison?
• Or is it Present Day of Previous month vs Present Day of Current Month comparison?

The same is confusing based on the the description in your question and the details provided in your comments as well as screenshot.

Following is a query based on your question which generates First Day of Previous Month and Last Day of Previous Month, using makeresults and eval timeEarliest and timeLatest. The relative_time() time evaluation function is used to compute first day of previous month and last day of previous month. Also, strftime() eval function is chained to convert epoch time to string time of format YYYY-MM-DD as expected by your search field Viewers_Today. These values are passed to a map command (if this is for report, for dashboard map command will not be required as tokens can be passed directly via run anywhere search.)

| makeresults 
| eval timeEarliest=strftime(relative_time(now(),"-1mon@mon"),"%Y-%m-%d"), 
       timeLatest=strftime(relative_time(now(),"-0mon@mon-1d"),"%Y-%m-%d") 
| map search="search index=* Client="HelloPunter" AND Viewers_Today=\"$timeEarliest$\" OR Viewers_Today=\"$timeLatest$\"
| eval DayOfPreviousMonth=case(Viewers_Today==\"$timeEarliest$\",\"First Day (Previous Month)\",Viewers_Today==\"$timeLatest$\",\"Last Day (Previous Month)\")
| stats values(Total_PCs) AS \"Total PCs\", values(Total_iPADs) AS \"Total iPADs\" values(Total_MACs) AS \"Total MACs\" by DayOfPreviousMonth
| transpose header_field=\"DayOfPreviousMonth\" column_name=\"Categories\""

Please try out and confirm! You can change the relative_time() snap-to as per your use case to return any other dates that you are interested in.

[puntershot]

Hi Sorry for the confusion.

This is a report to be scheduled on every month end date by comparing "Last Day of Previous month" vs Last "Day of current Month"

[Sukisen1981 · Accepted solution]

I am not very clear about the requirement but if what I think is correct -
Firstly this how the gentimes will work pseudo code

<your index> [| gentimes start=-1 | addinfo | eval earliest=relative_time(info_max_time,"@mon-1mon")   | eval latest=relative_time(info_max_time,"@mon-1d") | table earliest latest] |<rest of your search query>

Now , in your case it will be something like this, I am just going for the first 2 fields, you can add the rest if the query works

<your index> [| gentimes start=-1 | addinfo | eval earliest=relative_time(info_max_time,"@mon-1mon")   | eval latest=relative_time(info_max_time,"@mon-1d") | table earliest latest] Client="HelloPunter"| eventstats max(Viewers_Today) as max_t, min(Viewers_Today) as min_t| where Viewers_Today=max_t OR Viewers_Today=min_t|stats values(Total_PCs) AS "Total PCs", values(Total_iPADs)|<rest of your transpose code>

I am not clear about why you are doing a transpose and how you want your output , and hence I asked for the screen grab.
But this will enable you to solve the rest on your own as long you got the gentimes code and its usage from the above example.
Take care of quotation marks etc. For instance, why do you have "Viewers_Today" and not Viewers_Today?

[puntershot]

Hi Sukisen,

Everything works awesome as per your query (My requirement is to run this report on the end of month by showing results as shown below 2nd image link). Please find the link https://ibb.co/XJHbYT3 for the earliest date and latest date. I do use Transpose because I want PCs , MACs, IPADs should come under single column by field "Category" and show count of iPADs,MACs, PCs results like this > https://ibb.co/wpbp5BG. Last question for you how to rename earliest and latest fields as "previous month" and "current month"?

Million Thanks

[Sukisen1981]

Well, I am a bit more confused now than before 🙂 but more on that later. I saw your second screenshot. if your need is to rename the second column to previous month and the third column to current month then you can use something like this:
after your stats ends and BEFORE you apply the transpose, use this
|eval Viewers_Today=if(Viewers_Today=max_t,"Current Month","Previous Month")

Now, when you transpose, the larger date will be current month and the smaller one will be previous month.
if you run the report on the last day of the month, won't both dates be in the same(current month?), so if you run it on may 31st the second column will be 1st may and the third column will be 31 may...so won't they both belong to the same month?

Let me know how it goes after applying the eval and then transposing.
Please accept the answer if it resolved your issue, or helped significantly help resolving your issue 🙂

[puntershot]

yea you are right ,I am getting only one column as a Previous month if I apply " |eval Viewers_Today=if(Viewers_Today=max_t,"Current Month","Previous Month")"

Lets change my requirement to Last date of previous month and Last date of current month and running and scheduling the report at end of every month . How our entire below query will be modified accordingly ? . Please confirm. Thank you

[| gentimes start=-1 | addinfo | eval earliest=relative_time(info_max_time,"@mon-1mon") | eval latest=relative_time(info_max_time,"@mon-1d") | table earliest latest] Client="HelloPunter"| eventstats max(Viewers_Today) as max_t, min(Viewers_Today) as min_t| where Viewers_Today=max_t OR Viewers_Today=min_t|stats values(Total_PCs) AS "Total PCs", values(Total_iPADs) |eval Viewers_Today=if(Viewers_Today=max_t,"Current Month","Previous Month")| |

[Sukisen1981]

index=* Client="HelloPunter" |   stats values(Total_PCs) AS Total_PCs, values(Total_iPADs) AS Total_iPADs  by Viewers_Today| eval last_t=strftime(now(),"%Y-%m-%d"),
        first_t=strftime(relative_time(now(),"-0mon@mon-1d"),"%Y-%m-%d")| where (Viewers_Today=last_t OR Viewers_Today=first_t) | eval Viewers_Today=if(Viewers_Today=last_t,Current_mnth,Prev_mnth) |<your transpose>

You should not have to use gentimes or map now.
Run this and it should give you only 2 dates for Viewers_Today - today's date and last date of last month. So, when you run it on the last day of May , for example , it will give you May 31 as last_t and April 30 as first_t

[puntershot]

Sukisen,

Thank you very much it is working (See attached screenshot) as my required first date of the April and last date of April ( I wanted like this only). How to compare the data by earliest and latest? Can you give me query to compare results on that?

[Sukisen1981]

can you post the image as a link , for some reason I am not able to view your screen shot

[Sukisen1981]

Could you please clarify a bit? when do you propose to run the report?
If you run today for example 4th of May, are you actually seeking last months data ? April1-April30 range?

Run this as is snippet and check the dates returned by the output, you could then use the earliest and latest fields for further processing

| gentimes start=-1 | addinfo | eval earliest=relative_time(info_max_time,"@mon-1mon")   | eval latest=relative_time(info_max_time,"@mon-1d") | table earliest latest
| eval latest=strftime(latest,"%Y-%m-%d")
| eval earliest=strftime(earliest,"%Y-%m-%d")