topic Re: How do I merge data with the same values in a table entry? in Splunk Search

How do I merge data with the same values in a table entry?

voninski — Tue, 29 Sep 2020 10:09:47 GMT

See the attached picture:

I am looking at a count of data for deliveries from 2 months ago and the previous months. By themselves the queries work perfectly. I am trying to build a barchart where i can show the data together. And am currently using the 'append' command. I have tried appendpipe and appendcols and neither is giving me the right data. This is as close as I have gotten. Unfortunately though i can have multiple 'Times' that are the same for example in the picture you can see that i have entries for both the previous month and 2 months ago for 11:00AM and 11:15AM etc. I would like to have only 1 entry for 11:00AM 11:15AM etc and then have the appropriate count for the '2 months ago' and 'previous month'

Re: How do I merge data with the same values in a table entry?

voninski — Sat, 09 Jul 2016 23:10:17 GMT

I think I just figured this out. I dropped the fields portion and added the following

table Time, "Previous Month" "2 Months Ago" | stats values(*) as * by Time

If anyone has a better idea let me know.

Re: How do I merge data with the same values in a table entry?

sundareshr — Sun, 10 Jul 2016 00:00:33 GMT

Try this

index=security "Mailbox to On" earliest=-2mon@mon latest=@mon | eval when=if(_time>relative_time(now(), "-1mon@mon"), "Previous Month", "2 Months Ago") | timechart span=15m count by when

Re: How do I merge data with the same values in a table entry?

woodcock — Sun, 10 Jul 2016 13:12:20 GMT

You need the timewrap command/app:

https://splunkbase.splunk.com/app/1645/

Re: How do I merge data with the same values in a table entry?

voninski — Sun, 10 Jul 2016 16:34:49 GMT

TY this looks awesome and i am planning on testing it out. I always forget to check Splunkbase. Always amazed at the functionality out there.

Re: How do I merge data with the same values in a table entry?

voninski — Tue, 29 Sep 2020 10:09:52 GMT

Thank you. This is a cleaner search. But all I care to know is within the timeframe what is the hour&min that was the delivery time and divide them out. I think I can rebuild this with your query to parse out the months. But will still need the basic structure to a) divide things out into the 15 min buckets b) discard everything but the first time the mailbox is opened (the initial delivery) and the stats values(*) as * by Time command which merges all my results together by Time..

My final query is :

Re: How do I merge data with the same values in a table entry?

voninski — Sun, 10 Jul 2016 16:47:09 GMT

wow - astericks dont show up here --- stats values(asterick) as asterick by Time

Re: How do I merge data with the same values in a table entry?

sundareshr — Sun, 10 Jul 2016 23:08:30 GMT

That should do it as well, since you are using sub searches, you need to aware of the limitations. You can also achieve the 4 slices by using a case statement, instead of if. Like this

... | eval when=case(_time>relative_time(now(), "-1mon@mon"), "Previous Month", _time<relative_time(now(), "-2mon@mon") AND _time>relative_time(now(), "-1mon@mon"), "2 Months Ago", _time<relative_time(now(), "-3mon@mon") AND _time>relative_time(now(), "-2mon@mon"), "3 Months Ago", )  | ...

Re: How do I merge data with the same values in a table entry?

woodcock — Mon, 11 Jul 2016 00:48:39 GMT

Indent your code 4 spaces and lead the code with a blank line.

Re: How do I merge data with the same values in a table entry?

rvoninski_splun — Mon, 11 Jul 2016 15:34:14 GMT

This is an outstanding idea that is more efficient and helps me to avoid the subsearch limitations. I haven't implemented it yet. But this is definitely a much better way to go. Thank you very much for all of your help.