Compare two search results' differences over time

[thdose]

First timer here - hi all and thanks for this amazing ressource.

I am trying to timechart the counts for unique and shared values of 2 different lists.

My events:

"2016-08-08 10:46:09" PLAY_ID="112680324", PLAY_DATE="2015-12-01 09:00:03", ARTIST="Coldplay", TITLE="Hymn For The Weekend feat. Beyonce", STATION_ID="S1"

"2016-08-08 10:46:06" PLAY_ID="110914851", PLAY_DATE="2015-12-01 21:24:48", ARTIST="DJ Snake;Major Lazer", TITLE="Lean On", STATION_ID="S2"

I need to show the number of artists that are only played on each of the stations and the shared artists that both stations play. And how these counts evolve over time.

Inspired by this article: https://know-how.io/?p=32 I have this search:

... | PLAY_DATE="2015-12-01*" (STATION_ID="S1" OR STATION_ID="S2") | makemv delim=";" ARTIST | eval S1=if(CH_WO=="S1", ARTIST, NULL)| eval S2=if(CH_WO=="S2", KUN, NULL) | stats values(S1) as Station1, values(S2) as Station2 | mvexpand Station1 | mvexpand Station2 | where Station1==Station2 | rename Station1 as shared | table shared

The ARTIST field can hold multiple artists separated by ;

The query works, but only gives me the actual shared artists within a specified time period.

1. I'd like to include the unique artist counts for each station as well.
2. I am specifying the time period (PLAY_DATE="2015-12-01*") where I'd like to bin span time periods in order to do per week, month, year etc.
3. The search is painstakenly slow. The example search takes about 10 secs.

Ideally I'd like to end up with a graph like this:

Any help would be much welcome.

Thanks.

[mhpark]

Assuming you need to split the events not only with ARTIST, but also PLAY_DATE,

You should do the latter first since you split the ARTIST field with mvexpand.
you just need to take off the mvexpand part.

it would be like

STATION_ID="S1" OR STATION_ID="S2"
| stats values(STATION_ID) as station by ARTIST, PLAY_DATE
| eval station = if (mvcount(station)==2, "shared", station)
| bin PLAY_DATE span=1d
| stats dc(ARTIST) as play_count by station, PLAY_DATE

to merge them would be difficult, since mvexpand breaks a field.
I don't have any great ideas, so I would just call appendcols or something for now.

(query1)
| appendcols [ (query2) ]

by the way, please verify this assumption.

if one play had ARTIST A;B at station1,
and another had ARTIST B;C at station2.

the two are not shared plays, but B is a shared artist. right?

[mhpark · Accepted solution]

Give this a try.
if the bin option doesn't work on PLAY_DATE,
try to dump it to _time by

| eval _time = PLAY_DATE

Also, put your filtering queries (STATION_ID) in front as far as you can, for performance.

STATION_ID="S1" OR STATION_ID="S2"
| makemv delim=";" ARTIST
| mvexpand ARTIST
| bin PLAY_DATE span=12mon
| stats values(STATION_ID) as station by ARTIST, PLAY_DATE
| eval station = if (mvcount(station)==2, "shared", station)
| stats dc(ARTIST) as artist_count by station, PLAY_DATE

[thdose]

Perfect! I does exactly what we wanted. Thank you!!

[thdose]

If we wan't to do the same only with the total count all the plays, how would that work?

The above solution gives us the unique artist count, but we would like to see the total number of plays (events) for each station and the total number of shared artist plays....

[mhpark]

Please share a result example of what you want.

What is a "play"?
does one play refer to one event?

What is a "shared artist play"?
a play (=event) with more than one artist?

Is a play with two artists, a single play, or two plays?

Do you want to see the result appended to the query above?

More understanding of your requirements would help.

[thdose]

Sorry mhpark - was out of office for a few days.

1 event = 1 play.

Shared artist play = the total number of plays/events across with identical ARTIST values across diferent STATION ids.

"Is a play with two artists, a single play, or two plays?"
1 event = 1 play always even when multiple values in ARTIST.

Yes would be great to base it off the query above.

Thank you.