topic Re: I want to know whether Bob had se in Splunk Search

I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 05:42:06 GMT

Please tell me the way of the binding of data.
I want to combine the two data.

These data How can you join?

Re: I want to know whether Bob had se

cmerriman — Mon, 10 Oct 2016 13:20:38 GMT

index=tv_viewing_log |join  channel date [search index=tv_program_data] |eval watched=if(startWatch>=startAir AND startWatch<=endAir,1,0)|search watched=1

where startWatch is 0800 is Time began to see, startAir is 0730 is Broadcast start time and endAir is 0900 is Broadcast end time in your example

maybe something like this?

Re: I want to know whether Bob had se

sundareshr — Mon, 10 Oct 2016 13:26:26 GMT

Assuming your data is already in splunk in two separate indexes, you could try something like this

base search to return all viewing data | eval viewstart=strptime(date."#".starttime, "%Y/%m/%d#%H%M") | eval viewend=strptime(date."#".endtime, "%Y/%m/%d#%H%M") | eval viewrange=mvrange(viewstart, viewend, "30m")  | mvexpand viewrange | fields name viewrange ch viewend | append [search base search to return all programming data | eval viewstart=strptime(date."#".starttime, "%Y/%m/%d#%H%M") | eval viewend=strptime(date."#".endtime, "%Y/%m/%d#%H%M") | eval viewrange=mvrange(viewstart, viewend, "30m")  | mvexpand viewrange | fields viewrange ch prog  viewend ] | stats values(prog) as program values(viewend) as viewend  values(name) as name by ch viewrange | dedup name viewend | rename viewrange as start viewend as end | eval date=strftime(start, "%Y/%m/%d") | eval start=strftime(start, "%H:%M") | eval end=strftime(end, "%H:%M") | table name date start end program

*OR

 index=tv 
 | rename setai_no as name rt_code as ch
 | eval viewstart=strptime(date."#".start_time, "%Y-%m-%d#%H%M") 
 | eval viewend=strptime(date."#".end_time, "%Y-%m-%d#%H%M") 
 | eval viewrange=mvrange(viewstart, viewend, "30m")  
 | mvexpand viewrange
 | fields name viewrange ch viewend
 | append [search index=tv_program 
 | rename BROADCAST_DATE as date START_TIME as starttime END_TIME  as endtime PROGRAM_NAME as prog STATION_CODE as ch
 | eval viewstart=strptime(date."#".starttime, "%Y/%m/%d#%H%M") 
 | eval viewend=strptime(date."#".endtime, "%Y/%m/%d#%H%M") 
 | eval viewrange=mvrange(viewstart, viewend, "30m")  
 | mvexpand viewrange 
 | fields viewrange ch prog viewend ]
 | stats values(prog) as program values(viewend) as viewend values(name) as name by ch viewrange 
 | dedup name viewend 
 | rename viewrange as start viewend as end 
 | eval date=strftime(start, "%Y/%m/%d") 
 | eval start=strftime(start, "%H:%M") 
 | eval end=strftime(end, "%H:%M") 
 | table name date start end program

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 13:55:49 GMT

Thank you for answering.
I tried it but it did not work.

Have been combined in time and channel, data corresponding to the part of 「eval watched = ~~ 」is not.
Perhaps, because the date and channel is a plurality of the same data set.

Example)
1.TV viewing log
Example ) Bob , 2016/10/10 , 0800 , 0830 , 6ch

2.TV Prgram Data
Example )
2016/10/10,0500,0600,6ch,24 1st season ←←This data set is joined
2016/10/10,0730,0900,6ch,24 2nd season
2016/10/10,0930,1000,6ch,24 3rd season

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 14:40:46 GMT

Thank you for answering.
I tried, but it did not work.

Until the 「append ~~」 was operating properly.
I do not understand did not work the part.

One by one to confirm.

Re: I want to know whether Bob had se

sundareshr — Mon, 10 Oct 2016 14:52:19 GMT

Please share your query

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 21:01:53 GMT

index=tv 
| rename setai_no as name rt_code as ch
| eval viewstart=strptime(date."#".start_time, "%Y-%m-%d#%H%M") 
| eval viewend=strptime(date."#".end_time, "%Y-%m-%d#%H%M") 
| eval viewrange=mvrange(viewstart, viewend, "30m")  
| mvexpand viewrange
| fields name viewrange ch viewend
| append [search index=tv_program 
| rename BROADCAST_DATE as date START_TIME as starttime END_TIME  as endtime PROGRAM_NAME as prog STATION_CODE as ch
| eval viewstart=strptime(date."#".starttime, "%Y/%m/%d#%H%M") 
| eval viewend=strptime(date."#".endtime, "%Y/%m/%d#%H%M") 
| eval viewrange=mvrange(viewstart, viewend, "30m")  
| mvexpand viewrange 
| fields viewrange ch prog viewend ]
| stats values(prog) as program values(viewend) as viewend by name ch viewrange 
| dedup name viewend 
| rename viewrange as start viewend as end 
| eval date=strftime(start, "%Y/%m/%d") 
| eval start=strftime(start, "%H:%M") 
| eval end=strftime(end, "%H:%M") 
| table name date start end program

This is.

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 21:23:55 GMT

| stats values(prog) as program values(viewend) as viewend by name ch viewrange

This search content did not have the results.
「Viewrange」 it might cause.

Re: I want to know whether Bob had se

sundareshr — Mon, 10 Oct 2016 21:52:06 GMT

Try the updated query

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 21:57:16 GMT

Thank you.
i will try

Re: I want to know whether Bob had se

kawashita_t — Mon, 10 Oct 2016 22:04:14 GMT

Results come out but, "program" is not output.

Output Example:
Bob , 2016/10/10 , 0830 , 0930 ,