Splunk Search

How can I combine the results from my two searches?

pavanae
Builder

I have 2 Splunk searches as below:

search 1:

My Search | stats earliest(date_hour) as FirstHour latest(date_hour) as LastHour by user|eval accessTime=FirstHour+"--"+LastHour|fields - FirstHour - LastHour

Result:

user     accessTime
A123     22--24   
B456     02--08

Search 2:

 My Search | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time  |eventstats range(time) as duration by user| stats avg(duration) as avgDurationPeruser by user | eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration") | table user Total_time_spent(out_of_collage) 

Result:

user     Total_time_spent(out_of_collage)
A123     19:39:35
b456     20:19:31

Now, how can I combine search 1 and search 2 and display the result below:

user     accessTime     Total_time_spent(out_of_collage)
A123     22--24         19:39:35
B456     02--08         20:19:31
0 Karma
1 Solution

gcusello
Legend

Hi pavanae,
you should use appendcols (http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Appendcols) or appendpipe (http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Appendpipe) command to merge the two searches, something like this:

My Search | stats earliest(date_hour) as FirstHour latest(date_hour) as LastHour by user|eval accessTime=FirstHour+"--"+LastHour | appendcols [search My Search | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time  |eventstats range(time) as duration by user| stats avg(duration) as avgDurationPeruser by user | eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration")] | table user accessTime Total_time_spent(out_of_collage) 

Bye.
Giuseppe

View solution in original post

0 Karma

gcusello
Legend

Hi pavanae,
you should use appendcols (http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Appendcols) or appendpipe (http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Appendpipe) command to merge the two searches, something like this:

My Search | stats earliest(date_hour) as FirstHour latest(date_hour) as LastHour by user|eval accessTime=FirstHour+"--"+LastHour | appendcols [search My Search | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time  |eventstats range(time) as duration by user| stats avg(duration) as avgDurationPeruser by user | eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration")] | table user accessTime Total_time_spent(out_of_collage) 

Bye.
Giuseppe

View solution in original post

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!