topic Re: chart sorting by last field in Splunk Search

chart sorting by last field

aaronkorn — Tue, 30 Jul 2013 15:50:57 GMT

Hello,

We have the following chart which displays current ticket counts over the last 7 days for different groups but need to be able to sort on the count for the last day. In this case we would need to sort on Jul 30 but it would need to be dynamic and always sort on the most recent date in the chart. Here is the search we have:

 index=reporter | dedup TKT_NUMBER | eval time=strptime(LASTOCCURRENCE, "%b %d %Y %I:%M%p") | bucket time span=1d | convert timeformat="%b %d" ctime("time") | chart count by NOTIFY_GROUP, time | rename NOTIFY_GROUP AS Group

Is this possible?

Re: chart sorting by last field

Gilberto_Castil — Tue, 30 Jul 2013 16:37:12 GMT

This is tricky because of the dynamic nature of your field. Here is one easy way to change this but there is a beautification aspect to note. Notice how you've lost the precision on your field names as those have now become part of the table itself.

Assuming that you consistently have ten (10) rows in your table and seven (7) columns, this will create the desired effect:

| transpose 10 | transpose 10 | fields - column | sort - "row 7"

This will change the format of your original table, like the one above, to something like this:

The other side effect is that by using the transpose command you've lost the ability to drill down.

[07-31-2130] Update:

While thinking about this, it occurred to me that renaming the last field is simple as long as it is understood that the dynamic "time" variable that you have is actually a list.

The key here is to perform a comparison between the value of the "time" list and the "today" list. If the comparison fits, then we should assign the value of the latest ("today") variable to the appropriate value in the "time" list.

The comparison I used was all using the ephoc date. It is just easier for me to deal with numeric values, rather than strings

| eval time=if(round((today-time)/60/60/24)=0,"Today",time)

Then we convert to the string desired:

| eval time=if(time="Today",time,strftime(time, "%b %d"))

And, finally, you can apply the chart transformation and sort:

| chart count over NOTIFY_GROUP by time 
| rename NOTIFY_GROUP AS Group
| sort - Today

This will not change your format and it will keep click precision.

Re: chart sorting by last field

aaronkorn — Mon, 28 Sep 2020 14:28:13 GMT

Thanks for the response. Couldnt we do something like this?

Re: chart sorting by last field

Gilberto_Castil — Wed, 31 Jul 2013 13:50:05 GMT

I did some testing with this and found a solution. They key is compare the dynamic "time" variable to the "today" variable.

time=if((today-time)<24hours,"Today",time)

That will assign the value of "Today" to the dynamic "time" variable and then you can perform the sort by the "Today" column. I was challenged with the string comparisons so defaulted using pure epoch times for the comparison and it worked.

| eval time=if(round((today-time)/60/60/24)=0,"Today",time)

I will post more if needed.

Re: chart sorting by last field

aaronkorn — Mon, 28 Sep 2020 14:28:41 GMT

Thanks for the detailed explanation! I tried adding it to my previous search but it still doesn't appear to be working. I wonder since LASTOCCURRENCE isnt epoch that why its throwing it off...

Re: chart sorting by last field

Gilberto_Castil — Wed, 31 Jul 2013 17:46:08 GMT

Correct. If LASTOCCURRENCE is not epoch, the calculation suggested does not work. What is the original format for LASTOCCURRENCE?

Re: chart sorting by last field

aaronkorn — Wed, 31 Jul 2013 17:53:21 GMT

Its in the following format: Jul 31 2013 1:49PM

Is there a way we can convert to epoch then eval on that?

Re: chart sorting by last field

Gilberto_Castil — Mon, 28 Sep 2020 14:28:44 GMT

I think I see the problem. You need to instantiate a value for the field "today":

| eval today=round(time())

-- Your search might work with this:

Re: chart sorting by last field

aaronkorn — Wed, 31 Jul 2013 18:39:43 GMT

Still didnt seem to do it. I think adding the new eval helped but the issue is probably still with the LASOCCURRENCE not being epoch

Re: chart sorting by last field

aaronkorn — Thu, 01 Aug 2013 12:36:24 GMT

Also, now the dates are in alphabetical order. Anyway to get around that? Thanks!

Re: chart sorting by last field

aaronkorn — Mon, 05 Aug 2013 14:09:00 GMT

I tried sending an email but it came back undeliverable