How do I retrieve the first and last date from each month?

[udaypulipaka]

Hi,

I have two dropdowns (namely month and year). My query is to display results month wise. If I select January and 2018, then 1st to 31 jan 2018 data should be displayed. I am passing month and year tokens in query, but how do I retrieve the last date of each month?

Please help.

[vishaltaneja070 · Accepted solution]

Hello @udaypulipaka,

This query can help you

    |makeresults | eval time1="01-"."Jan"."-"."2019" | eval time1=strptime(time1,"%d-%b-%Y") | eval time2 = relative_time(time1, "+1mon") | eval time2 = relative_time(time2, "-1d") | eval time1=strftime(time1, "%d-%m-%Y") | eval time2=strftime(time2, "%d-%m-%Y")

[udaypulipaka]

Thank u so much.It's working fine

[vishaltaneja070]

Great. your welcome 🙂

[vishaltaneja070]

Updated the correct answer here as well:

 <form>
       <label>month&amp;year</label>
       <fieldset submitButton="false">
         <input type="dropdown" token="month_tok">
           <label>Month</label>
           <choice value="Jan">Jan</choice>
           <choice value="Feb">Feb</choice>
           <choice value="Mar">March</choice>
         </input>
         <input type="dropdown" token="year_tok">
           <label>Year</label>
           <choice value="2017">2017</choice>
           <choice value="2018">2018</choice>
           <choice value="2019">2019</choice>
         </input>
       </fieldset>
       <row>
         <panel>
           <title>$month_tok$     $year_tok$</title>
           <table>
             <search>
               <query>index="os" sourcetype="Ser" CaseNumber=* status=* assignment_group=*  [|makeresults | eval time1="01-".$month_tok|s$."-".$year_tok|s$ | eval time1=strptime(time1,"%d-%b-%Y") | eval time2 = relative_time(time1, "+1mon") | eval latest = relative_time(time2, "-1d") | eval earliest=time1 | return earliest, latest] |dedup _time,CaseNumber,assignment_group| streamstats current=f last(assignment_group) as lg, last(active) as Active by CaseNumber | eval ss=case(assignment_group!=lg AND assignment_group="Sus","Escalated",assignment_group="Sus" AND status="Complete" AND (isnull(Active) OR Active="true"),"Resolved") |stats count by ss</query>
               <earliest>-24h@h</earliest>
               <latest>now</latest>
             </search>
             <option name="drilldown">none</option>
             <option name="refresh.display">progressbar</option>
           </table>
         </panel>
       </row>
     </form>