topic Re: how to create pivot like table in Reporting

how to create pivot like table

mmdacutanan — Fri, 13 Dec 2019 00:59:18 GMT

I have an SPL query that produces a table output like this:

ExtractStart    ExtractEnd          CM  CallCount
12/12/2019 4:00 12/12/2019 4:15 CM2 55
12/12/2019 4:00 12/12/2019 4:15 CE1 0
12/12/2019 4:00 12/12/2019 4:15 CE3 28
12/12/2019 4:00 12/12/2019 4:15 CM4 32
12/12/2019 4:15 12/12/2019 4:30 CM2 192
12/12/2019 4:15 12/12/2019 4:30 CE1 0
12/12/2019 4:15 12/12/2019 4:30 CE3 95
12/12/2019 4:15 12/12/2019 4:30 CM1 157
12/12/2019 4:30 12/12/2019 4:45 CM2 197
12/12/2019 4:30 12/12/2019 4:45 CE1 0
12/12/2019 4:30 12/12/2019 4:45 CE3 92
12/12/2019 4:30 12/12/2019 4:45 CM4 106
12/12/2019 4:45 12/12/2019 5:00 CM2 208
12/12/2019 4:45 12/12/2019 5:00 CE1 0
12/12/2019 4:45 12/12/2019 5:00 CE3 112

The query that produced the table above is this:

index=extract sourcetype=jobsummaries
| rex field=jobName "(?:\w+\s)?(?<CM>\w+)"
| rex field=rangeStart "(?<TS1>\d+-\d+-\d+\s\d+:\d+:\d+)\..+"
| rex field=rangeEnd "(?<TS2>\d+-\d+-\d+\s\d+:\d+:\d+)\..+"
| eval ExtractStart = strptime(TS1, "%Y-%m-%d %H:%M")
| eval ExtractStart = strftime(ExtractStart, "%Y-%m-%d %H:%M")
| eval ExtractEnd = strptime(TS2, "%Y-%m-%d %H:%M")
| eval ExtractEnd = strftime(ExtractEnd, "%Y-%m-%d %H:%M")
| eval today = relative_time(now(),"-0d@d")
| eval today = strftime(today, "%Y-%m-%d %H:%M")
| where ExtractStart >= today
| rename processedNewCalls as CallCount
| table execId ExtractStart ExtractEnd CM CallCount
| dedup execId
| fields - execId
| sort ExtractStart

How do I modify my query so that my table looks like this?

ExtractStart    ExtractEnd         CM1  CM2 CM3 CE1  CE3
12/12/2019 4:00 12/12/2019 4:15 0     55    32    0 28
12/12/2019 4:15 12/12/2019 4:30 157 192   0    0    95
12/12/2019 4:30 12/12/2019 4:45 0     197   106  0  92
12/12/2019 4:45 12/12/2019 5:00 0     208   0     0 112

Re: how to create pivot like table

adonio — Fri, 13 Dec 2019 04:45:23 GMT

like this?

index=extract sourcetype=jobsummaries
 | rex field=jobName "(?:\w+\s)?(?<CM>\w+)"
 | rex field=rangeStart "(?<TS1>\d+-\d+-\d+\s\d+:\d+:\d+)\..+"
 | rex field=rangeEnd "(?<TS2>\d+-\d+-\d+\s\d+:\d+:\d+)\..+"
 | eval ExtractStart = strptime(TS1, "%Y-%m-%d %H:%M")
 | eval ExtractStart = strftime(ExtractStart, "%Y-%m-%d %H:%M")
 | eval ExtractEnd = strptime(TS2, "%Y-%m-%d %H:%M")
 | eval ExtractEnd = strftime(ExtractEnd, "%Y-%m-%d %H:%M")
 | eval today = relative_time(now(),"-0d@d")
 | eval today = strftime(today, "%Y-%m-%d %H:%M")
 | where ExtractStart >= today
 | rename processedNewCalls as CallCount
 | table execId ExtractStart ExtractEnd CM CallCount
 | dedup execId
 | fields - execId

| stats values(CallCount) as calls by ExtractStart ExtractEnd CM

hope it helps

Re: how to create pivot like table

woodcock — Fri, 13 Dec 2019 11:16:51 GMT

Like this:

| makeresults 
| eval _raw= "ExtractStart,ExtractEnd,CM,CallCount
12/12/2019 4:00,12/12/2019 4:15,CM2,55
12/12/2019 4:00,12/12/2019 4:15,CE1,0
12/12/2019 4:00,12/12/2019 4:15,CE3,28
12/12/2019 4:00,12/12/2019 4:15,CM4,32
12/12/2019 4:15,12/12/2019 4:30,CM2,192
12/12/2019 4:15,12/12/2019 4:30,CE1,0
12/12/2019 4:15,12/12/2019 4:30,CE3,95
12/12/2019 4:15,12/12/2019 4:30,CM1,157
12/12/2019 4:30,12/12/2019 4:45,CM2,197
12/12/2019 4:30,12/12/2019 4:45,CE1,0
12/12/2019 4:30,12/12/2019 4:45,CE3,92
12/12/2019 4:30,12/12/2019 4:45,CM4,106
12/12/2019 4:45,12/12/2019 5:00,CM2,208
12/12/2019 4:45,12/12/2019 5:00,CE1,0
12/12/2019 4:45,12/12/2019 5:00,CE3,112"
| multikv forceheader=1
| table ExtractStart,ExtractEnd,CM,CallCount

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."

| eval raw = "ExtractStart=" . ExtractStart . " ExtractEnd=" . ExtractEnd
| fields - Extract*
| xyseries raw CM CallCount
| fillnull value=0
| rename raw AS _raw
| kv
| fields - _raw
| table ExtractStart ExtractEnd *

Re: how to create pivot like table

woodcock — Fri, 13 Dec 2019 11:17:43 GMT

Never, EVER use sort without a number (e.g. sort 0 ... ).

Re: how to create pivot like table

to4kawa — Fri, 13 Dec 2019 12:09:04 GMT

| makeresults 
| eval _raw= "ExtractStart,ExtractEnd,CM,CallCount
 12/12/2019 4:00,12/12/2019 4:15,CM2,55
 12/12/2019 4:00,12/12/2019 4:15,CE1,0
 12/12/2019 4:00,12/12/2019 4:15,CE3,28
 12/12/2019 4:00,12/12/2019 4:15,CM4,32
 12/12/2019 4:15,12/12/2019 4:30,CM2,192
 12/12/2019 4:15,12/12/2019 4:30,CE1,0
 12/12/2019 4:15,12/12/2019 4:30,CE3,95
 12/12/2019 4:15,12/12/2019 4:30,CM1,157
 12/12/2019 4:30,12/12/2019 4:45,CM2,197
 12/12/2019 4:30,12/12/2019 4:45,CE1,0
 12/12/2019 4:30,12/12/2019 4:45,CE3,92
 12/12/2019 4:30,12/12/2019 4:45,CM4,106
 12/12/2019 4:45,12/12/2019 5:00,CM2,208
 12/12/2019 4:45,12/12/2019 5:00,CE1,0
 12/12/2019 4:45,12/12/2019 5:00,CE3,112" 
| multikv forceheader=1 
| table ExtractStart,ExtractEnd,CM,CallCount 
`comment("the logic is below")`
| eval time=mvzip(ExtractStart,ExtractEnd) 
| chart limit=0 sum(CallCount) over time by CM 
| rex field=time "(?<ExtractStart>[^,]+),(?<ExtractEnd>.+)" 
| table ExtractStart ExtractEnd CM* CE* 
| fillnull

There are many possible answers.

Re: how to create pivot like table

mmdacutanan — Fri, 13 Dec 2019 22:41:09 GMT

Hi @woodcock ! Thank you for taking time to look over my question. Really appreciate it! I like posting question here in Splunk Answers as I always learn neat tips and tricks that I would not have thought of!

I tried your query and the output after the kv command only produced ExtractStart that only is blank while ExtractEnd only had the date - the time portion was dropped.

After looking around in the internet, I tweaked the solution you gave me to use 'extract' command instead of KV. I also added a | as the delimiter in the concatenation so that I can split the 2 dates more easily:

| eval raw = "ExtractStart=" . ExtractStart . " | "."ExtractEnd=" . ExtractEnd
| fields - Extract*
| xyseries raw CM CallCount
| fillnull value=0
| rename raw AS _raw
| extract pairdelim="|" kvdelim="="
| fields - _raw
| table ExtractStart ExtractEnd *

Still curious to see how to make the kv thing work if you don't mind sharing. 🙂

Re: how to create pivot like table

woodcock — Fri, 13 Dec 2019 22:43:38 GMT

The docs are pretty clear (and it worked in my test example). It is the same as KV_MODE = auto.

Re: how to create pivot like table

woodcock — Fri, 13 Dec 2019 22:44:11 GMT

So the solution worked with a minor tweak? If so, be sure to click Accept to close the question.

Re: how to create pivot like table

mmdacutanan — Fri, 13 Dec 2019 23:20:53 GMT

hi @to4kawa !! Thanks for taking time to look at my question! Your solution also works. Like you said, there are several possible answers. woodcock also had a suggestion that is somewhat similar to your but he used xyseries and kv.

Learned a few things in this post which I am really thankful for! 🙂

Re: how to create pivot like table

to4kawa — Fri, 13 Dec 2019 23:30:54 GMT

Thanks, @mmdacutanan
It ’s easy to understand this query.

kv and xyseries are good commands, let's use them. 🙂

Re: how to create pivot like table

woodcock — Fri, 13 Dec 2019 23:44:30 GMT

Be sure to UpVote around!

Re: how to create pivot like table

mmdacutanan — Sat, 14 Dec 2019 00:02:03 GMT

hi @woodcock ! I am embarrassed to ask but what is UpVote and how do I do it? 🙂

Re: how to create pivot like table

woodcock — Sat, 14 Dec 2019 00:08:15 GMT

I just UpVoted your comment. Just click on the up-arrows. It is free.