Splunk Search

## How to convert similar rows to adjacent column

Explorer
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

Tags (4)
1 Solution
Esteemed Legend

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"

| 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
Contributor

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"
| 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"
Esteemed Legend

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"