Reporting

how to create pivot like table

mmdacutanan
Explorer

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
Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
| 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.

View solution in original post

0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma

mmdacutanan
Explorer

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! 🙂

woodcock
Esteemed Legend

Be sure to UpVote around!

0 Karma

mmdacutanan
Explorer

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

woodcock
Esteemed Legend

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

0 Karma

to4kawa
Ultra Champion

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

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

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 *

mmdacutanan
Explorer

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. 🙂

0 Karma

woodcock
Esteemed Legend

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

adonio
Ultra Champion

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

0 Karma
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 ...