topic Re: Mysterious data spike in Dashboards & Visualizations

Mysterious data spike

mhtedford — Wed, 12 Jul 2017 20:19:46 GMT

I have a plethora of survey data from several thousand video conference calls.

After each call, users are asked to fill out a survey.

I have found a mysterious spike in a number of survey results during the month of April that is skewing my visualizations.

The spike can be seen here:

Here is a sample of the event data:

This makes no sense, as there was no surge in number of calls during this time. My data is corrupt, but I'm not sure how/why.

Please advise.

Re: Mysterious data spike

richgalloway — Wed, 12 Jul 2017 21:11:20 GMT

Have you ruled out a change in response rate? Perhaps the April calls encouraged more people to answer the surveys.

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 02:22:57 GMT

eyeballing timeline in the events tab, it indeed appears there is a spike in events...if u click "format timeline" and change it to full, makes it easier to analyze, it does look like there were double the events in the middle of the timeline.

My hunch would be duplicate events or perhaps timestamping challenges or line breaking or something. any reason u didnt use timechart instead? looks like you're extracting time accurately...

Try this to validate the spike in records:

index=webex_sentiment | timechart span=1h count
set your time picker to the week that shows the spike, any hints?

If the data appears clean on those days with spikes, then there simply must have been more responses.

Re: Mysterious data spike

DalJeanis — Thu, 13 Jul 2017 03:35:12 GMT

Ummm. Okay, so suppose there were no surge in calls, but the calls had more participants than usual?

That surge would be completely accurate and non-mysterious.

How can you verify that in the data you have access to?

Another way this could be investigated is by chewing it up in pieces - for instance, try this,,,

your search before the chart command
| bin _time span=1d 
| stats count as daycount by country _time
| eventstats avg(daycount) as avgcount stdev(daycount) as stdevcount by country
| where daycount> avgcount + 3*stdevcount

...which will tell you which countries spiked which days. If it's a simple subset, then it gives you information to track down.

Now, the other thing I don't understand about the data is that there doesn't seem to be an indication of which call it was on (unless the field is alphabetically after "linecount"). I guess that's okay as anonymous feedback goes, but it kind of hamstrings you as far as analysis goes. If there is such a field, then do a count by "webconferencenumber" and date, and then check for outliers in that. But, if that field existed, I would have expected you to have already done that.

Re: Mysterious data spike

anand_singh17 — Thu, 13 Jul 2017 08:18:52 GMT

did you precisely checked the peak time information. Manually checking for the results will really help. Devices work as they are made for and events generate, as we use them.

We can work together to resolve this issue..

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 16:55:59 GMT

@mmodestino

I followed your instructions and I believe that the issue is duplicate events.

Here is my query with "Format Timeline" : http://imgur.com/a/MEJJv

With this visual, you can see the truly see the data spike: http://imgur.com/a/Q4DxY

April 5 has 1,239 events

April 18 has 1,418 events
April 19 has 1,500 events
April 20 has 1,380 events

April 26 has 1,398 events
April 27 has 1,329 events
April 28 has 1,029 events

Outside of these few weeks in April, there are no days that come anywhere near to 1,000 events.

I drilled down to the individual hours per your suggestion, and found what appears to be a multitude of duplicate events: http://imgur.com/a/laaWD

What should be my next steps from here?

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 16:58:32 GMT

@anand_singh17 What do you propose? I believe the problem is duplicate events

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 17:13:38 GMT

actually it appears to be timestamp related. In other words we need to clean up your props.conf to ensure we extract the timestamp properly...for example, in your second screenshot, we can see that the timestamp in the event, is not the same as the _time field.

As you can see, the event appears to have a timestamp field of july 10th? it can be tough for splunk to determine the date, Splunk is extracting the date as july 10 (mod time?) rather than july 4 or June 28, cause the auto extract failed.

What we need to do is to help Splunk with the time format, as the auto extraction is letting you down.

Can you paste a few of these raw events from this screenshot here so I can run them through the Add data wizard for you and help you build a better timestamp extraction?

Ideally we will use timestamp_fields and a time_format like %m/%d/%y %k:%M

This demonstrates a best practice that states we should always build our props to define how to extract where to find the timestamp, how to process it's format, ,among other golden rules. The add data wiz is really great for that.

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 17:20:38 GMT

@DalJeanis

Thanks for the response.

I input your suggestion like this:

index = webex_sentiment 
| eval surveyDate = strptime(Started,"%m/%d/%Y %H:%M")
| eval YearWeek=strftime(surveyDate,"%Y-%U") 
| search YearWeek!="2016-00" 
| bin _time span=1d 
| stats count as daycount by country _time
| eventstats avg(daycount) as avgcount stdev(daycount) as stdevcount by country
| where daycount> avgcount + 3*stdevcount
| chart count(Rating) as NumberRatings by YearWeek 
| search YearWeek > 2016-12

I received these events back: http://imgur.com/a/8XYvW

However, there is no subset by country visualization: http://imgur.com/a/pNIuu

I'm unclear what exactly you mean by a field that displays which call it was on. Could you clarify?

Best,

Matthew

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 17:29:52 GMT

@mmodestino

Thank you so much!!

Here are the first eight events in list form:

7/10/17
12:40:28.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,7/4/17 0:56,7/4/17 0:56
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 7.4.17.csv sourcetype = csv

7/10/17
12:40:28.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,7/4/17 0:04,7/4/17 0:04
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 7.4.17.csv sourcetype = csv

7/10/17
12:37:39.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Philippines,6/29/17 0:32,6/29/17 0:32
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.29.17.csv sourcetype = csv

7/10/17
12:37:39.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Philippines,6/29/17 0:10,6/29/17 0:10
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.29.17.csv sourcetype = csv

7/10/17
12:37:34.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,6/28/17 0:45,6/28/17 0:45
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.28.17.csv sourcetype = csv

7/10/17
12:37:34.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Japan,6/28/17 0:36,6/28/17 0:36
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.28.17.csv sourcetype = csv

7/10/17
12:37:34.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,6/28/17 0:08,6/28/17 0:08
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.28.17.csv sourcetype = csv

7/10/17
12:37:28.000 AM 
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Taiwan,6/27/17 0:08,6/27/17 0:08
host = BDC-ESSSPLK01 source = G:\AutoIndex\webex_sentiment\WebEx Sentiment Survey2_Responses 6.27.17.csv sourcetype = csv

Here are those same eight events in raw form:

Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,7/4/17 0:56,7/4/17 0:56
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,7/4/17 0:04,7/4/17 0:04
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Philippines,6/29/17 0:32,6/29/17 0:32
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Philippines,6/29/17 0:10,6/29/17 0:10
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,6/28/17 0:45,6/28/17 0:45
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Japan,6/28/17 0:36,6/28/17 0:36
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,China,6/28/17 0:08,6/28/17 0:08
Very Satisfied,5,,,,,,,,,,,,,,,,,,,,,,Taiwan,6/27/17 0:08,6/27/17 0:08

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 17:51:29 GMT

ah also, can you provide the header values from the csv? I could just make one up, but better we get you sorted the whole way

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 17:55:46 GMT

Where do I find that?

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 17:57:43 GMT

In one of the raw csv files, or they were hardcoded in your props.conf when the data was onboarded

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 18:04:11 GMT

I must apologize for my lack of knowledge; I'm very new to Splunk.

Where can I find the raw csv files or the props.conf?

Re: Mysterious data spike

DalJeanis — Thu, 13 Jul 2017 18:21:12 GMT

Sure. People have just left a video conference call, and are being asked to fill out a survey about their experience. Isn't there a data field which identifies WHICH conference call they are responding to, so that you can associate the feedback with the actual call?

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 19:20:16 GMT

no problem! I just fudged it.

I came up with this props with your data, (i used the field_names just to fudge the headers, you likely dont need that as I bet you monitor is ingesting files with headers):

[ webex_sentiment_csv ]
CHARSET=UTF-8
INDEXED_EXTRACTIONS=csv
KV_MODE=none
SHOULD_LINEMERGE=false
category=Structured
description=Webex Sentiment Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled=false
pulldown_type=true
TIME_FORMAT=%m/%d/%y %k:%M
TIMESTAMP_FIELDS=field25
FIELD_NAMES=field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14,field15,field16,field17,field18,field19,field20,field21,field22,field23,field24,field25,field26
TZ=UTC
TRUNCATE=1000

Here is a good visualization of what went wrong:

As you can see the time with the single hour digit caused Splunk to be unable to parse the timestamp

All you need to do is work with your admin and make sure the that props is set up to read the headers, then point to the header field that had your timestamp in in and properly parse it

The add data wiz makes these issues easy to avoid and validate

good reference for props.conf:
http://docs.splunk.com/Documentation/Splunk/6.6.2/Data/Whysourcetypesmatter

Are you responsible for ingesting the data into splunk or do you have an admin?

Re: Mysterious data spike

DalJeanis — Thu, 13 Jul 2017 20:07:45 GMT

@mhtedford - thanks for the points. Has your problem been solved, was that an accident, or what?

Re: Mysterious data spike

mhtedford — Thu, 13 Jul 2017 20:52:13 GMT

@mmodestino I am so grateful for your help!

We have a Splunk application owner who manages the solution, but I do have admin capabilities.

I believe this particular data set is ingested constantly through forwarders.

Where do I need to input the props code you created?

Re: Mysterious data spike

mattymo — Thu, 13 Jul 2017 21:18:31 GMT

You will need it on the forwarders, I'd put it on the indexers too for good measure.

Don't put exactly what I provided as you likely don't need the field_names.
the main thing for you will be

 TIME_FORMAT=%m/%d/%y %k:%M
 TIMESTAMP_FIELDS=<yourTimestampField>

Ask your data owner to get you the raw file you are ingesting so you can see the headers, then I could provide the exact config

Re: Mysterious data spike

anand_singh17 — Fri, 14 Jul 2017 03:09:03 GMT

@mhtedford:

Please check one most important thing at your source, which is your inputs.conf, the very reason, the duplicacy may occur again and reaching to root is important. Two possibilities would have happened.

Inputs.conf is added with this additional configuration made to read data 'ignoreEarlierThan=xdays' and,
Splunkd service at the source would have been stopped or restarted with some reason/cause. Because of this pause of UF or HF, it will pick up data, as there is no method in Splunk yet to compare or verify, if the data is already indexed for so and so and so, matches or criterias.

As action,
1. You can verify inputs.conf
2. delete this additional record[caution], but please [imp] take help from splunk administrator, even though you have privileges. Any mistake, may lead to corrupt bucket or create issue in index.

Looking for your update,