topic Re: How to convert similar rows to adjacent column in Splunk Search

How to convert similar rows to adjacent column

nkumar6 — Wed, 04 Dec 2019 15:32:20 GMT

index=  abc source=xyz|table JOBS,DAY,COUNT,START,END

This is my current search which returns me result as:

JOBS DAY COUNT START END
abc.123a TODAY 22 2019/11/04 02:04:05 2019/11/04 02:05:05
axy.143b TODAY 15 2019/11/04 04:05:05 2019/11/04 04:12:05
abc.144a TODAY 23 2019/11/04 12:04:05 2019/11/04 12:14:05
abc.123a LASTWEEK 12 2019/10/27 02:13:05 2019/10/27 02:15:05
axy.143b LASTWEEK 53 2019/10/27 04:04:05 2019/10/27 04:05:05

I want to make LASTWEEK data along with TODAY data, so that further I can calculate the difference in COUNT , START and END. AND for the null data either in TODAY or LASTWEEK it may be considered zero

Thanks in advance.

Re: How to convert similar rows to adjacent column

woodcock — Wed, 04 Dec 2019 17:34:14 GMT

Like this:

|makeresults 
| eval _raw="JOBS,DAY,COUNT,START,END
abc.123a,TODAY,22,2019/11/04T02:04:05,2019/11/04T02:05:05
axy.143b,TODAY,15,2019/11/04T04:05:05,2019/11/04T04:12:05
abc.144a,TODAY,23,2019/11/04T12:04:05,2019/11/04T12:14:05
abc.123a,LASTWEEK,12,2019/10/27T02:13:05,2019/10/27T02:15:05
axy.143b,LASTWEEK,53,2019/10/27T04:04:05,2019/10/27T04:05:05"
| multikv forceheader=1

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| eval {DAY} = COUNT
| eventstats first(TODAY) AS TODAY first(LASTWEEK) AS LASTWEEK BY JOBS
| fillnull value=0 TODAY LASTWEEK

Re: How to convert similar rows to adjacent column

dindu — Wed, 04 Dec 2019 21:13:33 GMT

hi nkumar,

Please try the below solution.You could use the solution from "sort" in your actual query.
If you want to compare start and end time between weeks - Add a step to increment the previous week's time to one week ahead.
Please try and let us know.

 |makeresults 
 | eval _raw="JOBS,DAY,COUNT,START,END
 abc.123a,TODAY,22,2019/11/04T02:04:05,2019/11/04T02:05:05
 axy.143b,TODAY,15,2019/11/04T04:05:05,2019/11/04T04:12:05
abc.144a,TODAY,23,2019/11/04T12:04:05,2019/11/04T12:14:05
abc.123a,LASTWEEK,12,2019/10/27T02:13:05,2019/10/27T02:15:05
axy.143b,LASTWEEK,53,2019/10/27T04:04:05,2019/10/27T04:05:05"
| multikv forceheader=1
| sort JOBS,DAY
|stats  list(COUNT) as TOTAL_COUNT,list(START) as START,list(END) as END BY JOBS
|where mvcount(TOTAL_COUNT)>1
|eval START_TIME=strptime(START,"%Y/%m/%dT%H:%M:%S"),END_TIME=strptime(END,"%Y/%m/%dT%H:%M:%S")
|eval TOTAL_COUNT_DIFF=abs(tonumber(mvindex(TOTAL_COUNT,0))-tonumber(mvindex(TOTAL_COUNT,1)))
|eval "START_TIME_DIFF in Minutes"=(tonumber(mvindex(START_TIME,1))-tonumber(mvindex(START_TIME,0)))/3600,"END_TIME_DIFF in Minutes"=(tonumber(mvindex(END_TIME,1))-tonumber(mvindex(END_TIME,0)))/3600
|table JOBS,TOTAL_COUNT_DIFF,"START_TIME_DIFF in Minutes","END_TIME_DIFF in Minutes"