topic Re: Compare two search results' differences over time in Splunk Search

Compare two search results' differences over time

thdose — Fri, 26 Aug 2016 07:50:25 GMT

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:

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.

I'd like to include the unique artist counts for each station as well.
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.
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.

Re: Compare two search results' differences over time

mhpark — Fri, 26 Aug 2016 14:05:25 GMT

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

Re: Compare two search results' differences over time

thdose — Fri, 26 Aug 2016 18:49:05 GMT

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

Re: Compare two search results' differences over time

thdose — Thu, 08 Sep 2016 16:18:27 GMT

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

Re: Compare two search results' differences over time

mhpark — Fri, 09 Sep 2016 13:26:34 GMT

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.

Re: Compare two search results' differences over time

thdose — Tue, 13 Sep 2016 14:19:45 GMT

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.

Re: Compare two search results' differences over time

mhpark — Thu, 15 Sep 2016 13:30:49 GMT

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?