topic Re: Get Latest event per month? in Splunk Search

Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 13:23:23 GMT

I am trying to show a graph of the latest events per month. My search query is:

| timechart span=1month latest(availability) by application

I thought this would grab the latest availability of the applications per month but its not grabbing the latest event for each month. The events are produced every day to get up to date information on availabilities so I want to show the latest to graph per month ( I want to graph it having the x-axis be the month and the lines being each application, showing the latest availability per month so its up to date). Is there a way to dedup per month? Thanks

Re: Get Latest event per month?

elliotproebstel — Fri, 06 Apr 2018 13:44:42 GMT

I'm not sure I understand your end goal entirely, but if you want a stats table with the latest value for the field availability by application from each month, this should do it:

| stats latest(availability) AS availability BY application, date_month

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 13:48:07 GMT

my end goal it to show a graph that spans per month and shows the applications availability per month. so i want to do a timechart function

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 14:30:55 GMT

Those stats give me the correct numbers but I want to graph it having the x-axis be the month and the lines being each application

Re: Get Latest event per month?

elliotproebstel — Fri, 06 Apr 2018 14:32:53 GMT

Is availability a number?

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 14:36:22 GMT

yes it is a number

Re: Get Latest event per month?

TISKAR — Fri, 06 Apr 2018 14:42:36 GMT

Hello,

You can Try this,

| stats latest(availability) AS availability BY application, date_month
| xyseries date_month application availability

Re: Get Latest event per month?

elliotproebstel — Fri, 06 Apr 2018 14:47:28 GMT

Great, then the answer @TISKAR provided below should work!

Re: Get Latest event per month?

DalJeanis — Fri, 06 Apr 2018 14:50:46 GMT

By "latest event per month" do you mean the one that application last ran before the end of the month? Because that's what your code is trying to do.

Honestly, I don't think you are presenting any useful information. An application that was unavailable on every day of March up until the 30th, but was available on March 31 would show availability on the 31st.

So what? It was down most of the month.

You probably want to show how MANY days the application was available, possibly as a percentage.

That would go something like this:

your base search
| rename COMMENT as "always limit the search to the fields you need.  _time is internal, so it doesn't need to be listed."
| fields application
| bin _time span=1d

| rename COMMENT as "first we count up the number of records for each application for each day"
| stats count as mycount by _time application

| rename COMMENT as "now we add one zero record for each application for each day"
| appendpipe 
   [| stats min(_time) as mintime max(_time) as maxtime by application
    | eventstats min(mintime) as mintime max(maxtime) as maxtime
    | eval maxtime=maxtime+1
    | eval mycount=0      
    | eval myfan=mvrange(mintime,maxtime,86400)
    | mvexpand myfan
    | rename myfan as _time
    | table _time application mycount
    ]

| rename COMMENT as "now we stats again so that there is only one record for each application for each day"
| stats sum(mycount) as mycount by _time application

| rename COMMENT as "now we are ready to count monthly percentages.  We will present in whole numbers."
| rename COMMENT as "so we set the daily value to zero or 100, then round the result."
| eval mycount=if(mycount>0,100,0)
| bin _time span=1mon
| stats avg(mycount) as Monthly_Uptime_Percent by _time application
| eval Monthly_Uptime_Percent=round(Monthly_Uptime_Percent,0)

| rename COMMENT as "for presentation, you could use a timechart with a line for each application"
| timechart span=1mon max(Monthly_Uptime_Percent) by application

If you wanted to present the user with a stacked chart of the number of uptime days versus downtime days, you could do something like this...

| rename COMMENT as "now we are ready to count monthly up or down days.  We will set available based on whether there were any records found."
| eval available=if(mycount>0,"up","down")
| bin _time span=1mon
| stats count as Monthly_Uptime_Count by _time application available

| rename COMMENT as "I would tend to show this using trellis, one panel per application, with a stacked bar chart"

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 16:22:45 GMT

sorry i shouldve been more clear, availability is a percentage. its not a number that means up or down. its a percentage that is calculated every day to determine that app's availability 0-100%. so the numbers change every day based on if it was up or down. starts at 100 then decreases if there was an outage. so an application will always have an availability percentage because it resets to 100 every day to calculate the availability for today then we can calculate for the month. so the way the query is set up is its showing the availability total for the month based on the past however days the month has had and i want to graph by the month so i wanted to grab the latest calculation because that is the final availability calculation per app per month if that makes sense

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 17:49:56 GMT

For some reason when I do this the months are not in normal order. They're all out of order for some reason. Do you know why this is? thanks!!!

Re: Get Latest event per month?

kdimaria — Fri, 06 Apr 2018 17:57:43 GMT

Theres a query im running in the background to calculate the availability for the current month based on the previous days. so the availability for today is from April 1-6th and its a percentage. So im trying to grab the latest for that month so that on the graph for april, itll show whatever the latest is so the availability that was calculated for today (april 6th) and the previous months should be the last day of the moth but, its not grabbing the latest. it looks like its grabbing the first of each month

Re: Get Latest event per month?

elliotproebstel — Fri, 06 Apr 2018 19:17:56 GMT

They are probably being sorted alphabetically.

Re: Get Latest event per month?

TISKAR — Mon, 09 Apr 2018 09:16:49 GMT

Hello,

Yes you can use this:

    | eval month=strftime(_time,"%m")
    | eval date_month=month."-".date_month
    | stats latest(availability) AS availability BY application, date_month
    | xyseries date_month application availability  
    | rex field=date_month mode=sed "s/(\d+-)//g"

Re: Get Latest event per month?

FrankVl — Mon, 09 Apr 2018 10:17:32 GMT

Rather than concatenating and then stripping again, a more elegant solution would be to use the fieldformat command, to show the text value, while keeping the numerical month value under the hood for the sorting.

So:

| eval month=strftime(_time,"%m-%B")
| fieldformat month=substr(month,4)
| stats latest(availability) AS availability BY application, month
| xyseries month application availability

Re: Get Latest event per month?

kdimaria — Mon, 09 Apr 2018 14:43:29 GMT

@FrankVI , you never use the month variable in the stats? I assumed it was supposed to be month instead of date_month because that query didnt work. Using the month it puts it from January-December but I want the latest month on the right to be the current month. Sorry please let me know if you have any suggestions.

Re: Get Latest event per month?

FrankVl — Mon, 09 Apr 2018 14:58:12 GMT

Good catch, I only tested the first part of my suggestion and then combined it with what TISKAR had, it should indeed be stats by month, not date_month in my suggestion. I've updated the code in my post.

Re: Get Latest event per month?

kdimaria — Mon, 09 Apr 2018 15:42:03 GMT

@FrankVI ok but im still running into the issue that its displaying January-December when I would like it to display the current month last. So have the graph show May-April

Re: Get Latest event per month?

FrankVl — Mon, 09 Apr 2018 15:50:34 GMT

Right, that should be a matter of adding the year into the month field (and then stripping that off again in the fieldformat). Eg:

 | eval month=strftime(_time,"%Y%m-%B")
 | fieldformat month=substr(month,8)
 | stats latest(availability) AS availability BY application, month
 | xyseries month application availability

Re: Get Latest event per month?

kdimaria — Mon, 09 Apr 2018 17:49:06 GMT

Thanks so much! this worked! if you want to put this as an answer so I can accept it thatd be great!