Splunk Search

I want to know whether Bob had se

Explorer

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

These data How can you join?

0 Karma

Legend

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

Explorer

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.

0 Karma

Legend

Please share your query

0 Karma

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

0 Karma

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

0 Karma

Legend

Try the updated query

0 Karma

Explorer

Thank you.
i will try

0 Karma

Explorer

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

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

0 Karma

Super Champion
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?

0 Karma

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!