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

[voninski]

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'

The query I am currently using is :
index=security "Mailbox to On" earliest=-2mon@mon latest=-1mon@mon | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "2 Months Ago" |append [search index=security "Mailbox to On" earliest=-1mon@mon latest=@mon | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "Previous Month"] | rename rounded_time_hour_min AS Time | sort Time | fields Time "2 Months Ago" "Previous Month"

[woodcock]

You need the timewrap command/app:

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

[voninski]

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.

[sundareshr]

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

[voninski]

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 :

index=security "Mailbox to On" earliest=-2mon@mon latest=-1mon@mon | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "2 Months Ago" |append [search index=security "Mailbox to On" earliest=-1mon@mon latest=@mon | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "Previous Month"] | append [search index=security earliest=@mon latest=now "Mailbox to On" | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "Current Month"] | append [search index=security earliest=-3mon@mon latest=-2mon@mon "Mailbox to On" | eval date=strftime(_time,"%x") | sort _time | dedup date | bin span=15min _time AS rounded_time | eval rounded_time_hour_min=strftime(rounded_time,"%H:%M") | stats count BY rounded_time_hour_min | rename count AS "3 Months Ago"]| rename rounded_time_hour_min AS Time | sort Time |table Time, "Current Month" "Previous Month" "2 Months Ago" "3 Months Ago" | stats values(*) as * by Time | fields Time, "Current Month" "Previous Month" "2 Months Ago" "3 Months Ago"

[voninski]

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

[woodcock]

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

[sundareshr]

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", )  | ...

[rvoninski_splun]

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.

[voninski]

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.