Is it possible to sort field values present in one column by row values which have timestamps as the field title

[ak9092]

Hi Guys,

So basically i am trying to create a line-chart where in I am trying to plot fields status and time on it.

Status can be anything like created, in-progress, repair, maintenance ,complete..etc..etc  and whenever the status is changed I am capturing its time in time filed.

All this results are group by unique ID's, so one ID can go through multiple statuses before it's complete.

I am trying to capture all this details on line chart .(I am not using time-chart as some ID's may also get completed within a second and some might take hours too, so i am not able to plot the time precisely in that case).

As of now, on Line-chart I am able to get the status fields on X-axis but the issue is its not coming in a proper status flow.

Following are the example of current results which I am getting from my search query -

|chart count by status time

Status    05:32:11.711    05:32:18.896    05:33:15.531    05:35:39.722    05:36:28.321  

Complete         0                              0                             0                               0                          1

Created            1                               0                             0                               0                          0

In-Progress     0                               1                             0                               0                          0

Post                   0                                0                            0                                1                         0

Repair               0                                0                             1                              0                          0

so the way it is getting plotted on the line-chart on x-axis is like -

Complete.. Created.. In-Progress.. Post.. Repair

which is not the right flow and it should be like

Created.. In-Progress.. Repair.. Post.. Complete

So if somehow i can sort the status column values, by the timestamp in the rows like below then i guess i will be able to achieve this.

Status    05:32:11.711    05:32:18.896    05:33:15.531    05:35:39.722    05:36:28.321  

Created               1                              0                             0                               0                          0

In-Progress        0                              1                             0                               0                          0

Repair                  0                              0                             1                               0                          0

Post                      0                              0                              0                              1                          0

Complete           0                              0                              0                              0                         1

I have tried multiple ways but no luck .

Can someone please help me figure this out .

Also if there is some other way to achieve this then i am willing try that too.

Thank you.

[to4kawa · Accepted solution]

sample:

| makeresults count=5
| streamstats count
| eval status=case(count=1,"Created",count=2,"In-Progress",count=3,"Repair",count=4,"POST",count=5,"Complete")
| eval timeplus=random() % 10 + 1
| accum timeplus
| eval time=strftime(_time+timeplus,"%T")
| table time status
| streamstats count as sort
| eval status=sort."_".status
| chart count by status time
| eval status=mvindex(split(status,"_"),1)

recommend:

your search
| reverse
| streamstats count(status) as count
| eval status=count."_".status
| chart count by status time
| eval status=mvindex(split(status,"_"),1)

By default, the search results are in descending order of time, so they are reversed once.

[ak9092]

Thank You @to4kawa , this seems to be working fine.

I was wondering, if is there a way to dedup just one value from the status field ?

For e.g. - status like Repair, POST can be repeated multiple time and that is fine, as i want that repetition to be shown in timeline but in some case 'Complete' can also be updated multiple times as it sends complete messages through different strings, but i don't want this repetition.

So can i dedup the status with just one value and not the entire field ?

[to4kawa]

I see what you want.

but I can't make query because there is not sample logs.
please provide sample and results.

[ak9092]

Hi to4kawa,

It will be difficult for me to provide you the sample logs but below query might give you an idea of how the logs are -

Basically there are four searches which i am using to formulate this results

|index=foo "ID created"
| eval time=_time
| eval status1="CREATED"
| table ID status1 time

|append
[search index=foo "Change ID Status into"
|eval time=_time
| convert ctime(time)
|table ID1 status time]

| append
[search index=foo UpdateMsgStatus COMPLETE
| eval time=_time
| table ID statusc time
| rename time as Completed_time
| convert ctime(Completed_time)]

| append
[search index=foo ID = '*', MSG_STATUS = 'COMPLETE',
| eval time=_time
| table id msg_status time
| rename time as complete_time
| convert ctime(complete_time)]

| eval time=coalesce(time,complete_time,Completed_time)
| eval ID=coalesce(ID,ID1,id)
| eval status=coalesce(status,status1,statusc,msg_status)
| table ID status time

So in this, 2nd search will have all the statuses except the ID created one, but the Complete status might be logged in any of the last 3 searches and in some cases it is logged in all three with different timestamps or may be two of them ,so I want it to be logged only once in the results  irrespective of its repetition i.e the first entry which is found for status Complete for that particular ID

[to4kawa]

I see your query.
sample logs please.

There are many append, but splunk has searchmatch()

| stats values(eval(if(searchmatch("COMPLETE"),_time,NULL))) as complete_time ....

this query doesn't need append

[ak9092]

Thank You for the suggestion @to4kawa but it wont work in my case as the index names are different in some cases.

[ak9092]

@to4kawa  if possible , can you also please take a look at  https://community.splunk.com/t5/Splunk-Search/How-to-compare-and-match-timestamp-of-one-field-with-t... 

Thanks in advance.

[richgalloway]

Try adding a field to your data, sorting on that field, then removing the field from the results.

... | chart count by status time
| eval sorter = case(status="Created", 1, status="In-Progress", 2, status="Repair", 3, status="Post", 4, status="Complete", 5, 1==1, 99)
| sort + sorter
| fields - sorter