Splunk Search

How to convert similar rows to adjacent column

nkumar6
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

Thanks in advance.

0 Karma
1 Solution

woodcock
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"
| 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

View solution in original post

dindu
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"
| 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"

woodcock
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"
| 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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...