topic Re: Why are my timechart results getting skewed? in Splunk Search

Why are my timechart results getting skewed?

bstreber — Fri, 01 Mar 2019 16:34:00 GMT

I have come across an issue with my timecharts.

When I do a search for all day on Feb 26th and check 9AM, I see 127 results. However, when I run a search for Feb 26th 8-10AM, the 9AM spot has over 2400 results.

I have confirmed that the value changes depending on the timeslot selected. For example, if I search for 00-10AM, I get a different result than the previous two. I have confirmed that the jobs are completing successfully and that multiple different searches are doing the same thing.

Re: Why are my timechart results getting skewed?

somesoni2 — Fri, 01 Mar 2019 19:43:38 GMT

What's the search you're using in each case (where you see different result)? Also share the selected time range for each search.

Re: Why are my timechart results getting skewed?

bstreber — Fri, 01 Mar 2019 19:49:04 GMT

Search is

index="ngv"
|fields device.accountSourceId, device.ecmMacAddress, device.firmwareVersion, tuneStatus, device.deviceType assetClass device.deviceId
|search device.firmwareVersion="*" tuneStatus=FAILURE device.accountSourceId!=NULL assetClass!=DVR assetClass!=IPDVR |dedup device.accountSourceId
|search |dedup device.deviceId
|rename device.ecmMacAddress as DEVICE_MAC
| lookup xb6_accountid_mac.csv device.accountSourceId OUTPUT XB6_MAC 
| replace 00:00:00:00:00:00 with NULL 
| eval DEVICE_MAC=coalesce(XB6_MAC, BLUESKY_MAC, DEVICE_MAC)
| lookup FDB_BlueSky_Device_Details.csv DEVICE_MAC OUTPUT CITY, CMTS_DEVICE, LOCATION_ID, MANUFACTURER, MODEL, OPTICAL_RECEIVER_NAME 
| lookup CityToDACRegionMapping.csv CITY OUTPUT DAC_REGION
| search DAC_REGION=*
| timechart usenull=f useother=f span=10min count by DAC_REGION

Time pick

Feb 26th to Feb 26th (at 9 AM 30 results) and then Feb 26th 08:00:00 to Feb 26th 10:00:00 (at 9 AM 43 results)

Re: Why are my timechart results getting skewed?

efavreau — Fri, 01 Mar 2019 19:50:32 GMT

The span interval matters. Are you specifying the same span for each of these searches? If not, refer to the documentation on default time spans:
https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Timechart#Default_time_spans

Try putting span=15m after your timechart commands in each of your searches. For example:

| timechart span=15m

Re: Why are my timechart results getting skewed?

bstreber — Fri, 01 Mar 2019 20:29:22 GMT

Hey I have set the span to 10 minutes. See the search attached below

index="ngv"
|fields device.accountSourceId, device.ecmMacAddress, device.firmwareVersion, tuneStatus, device.deviceType assetClass device.deviceId
|search device.firmwareVersion="*" tuneStatus=FAILURE device.accountSourceId!=NULL assetClass!=DVR assetClass!=IPDVR |dedup device.accountSourceId
|search |dedup device.deviceId
|rename device.ecmMacAddress as DEVICE_MAC
| lookup xb6_accountid_mac.csv device.accountSourceId OUTPUT XB6_MAC 
| replace 00:00:00:00:00:00 with NULL 
| eval DEVICE_MAC=coalesce(XB6_MAC, BLUESKY_MAC, DEVICE_MAC)
| lookup FDB_BlueSky_Device_Details.csv DEVICE_MAC OUTPUT CITY, CMTS_DEVICE, LOCATION_ID, MANUFACTURER, MODEL, OPTICAL_RECEIVER_NAME 
| lookup CityToDACRegionMapping.csv CITY OUTPUT DAC_REGION
| search DAC_REGION=*
| timechart usenull=f useother=f span=10min count by DAC_REGION

Re: Why are my timechart results getting skewed?

efavreau — Fri, 01 Mar 2019 22:09:45 GMT

To confirm, the same search is being used both times, you have accounted for the span in the search, and all you are doing is changing the time in the GUI time picker? If so, then let's rule out the time picker.

Calculate time ranges that mean something to you. The documentation for that is here:
https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/SearchTimeModifiers

Example to isolate all of Feb 26:

index="ngv" earliest="02/26/2019:00:00:00" latest="02/27/2019:00:00:00"

Re: Why are my timechart results getting skewed?

bstreber — Fri, 01 Mar 2019 22:23:55 GMT

So for 8-10AM on the 26th I get 43 results at 9AM

index="ngv" earliest="02/26/2019:08:00:00" latest="02/26/2019:10:00:00"
|fields device.accountSourceId, device.ecmMacAddress, device.firmwareVersion, tuneStatus, device.deviceType assetClass device.deviceId
|search device.firmwareVersion="*" tuneStatus=FAILURE device.accountSourceId!=NULL assetClass!=DVR assetClass!=IPDVR |dedup device.accountSourceId
|search |dedup device.deviceId
|rename device.ecmMacAddress as DEVICE_MAC
| lookup xb6_accountid_mac.csv device.accountSourceId OUTPUT XB6_MAC 
| replace 00:00:00:00:00:00 with NULL 
| eval DEVICE_MAC=coalesce(XB6_MAC, BLUESKY_MAC, DEVICE_MAC)
| lookup FDB_BlueSky_Device_Details.csv DEVICE_MAC OUTPUT CITY, CMTS_DEVICE, LOCATION_ID, MANUFACTURER, MODEL, OPTICAL_RECEIVER_NAME 
| lookup CityToDACRegionMapping.csv CITY OUTPUT DAC_REGION
| search DAC_REGION=*
| timechart usenull=f useother=f span=10min count by DAC_REGION

And with all day I get 30 at 9AM

index="ngv" earliest="02/26/2019:00:00:00" latest="02/27/2019:00:00:00"
|fields device.accountSourceId, device.ecmMacAddress, device.firmwareVersion, tuneStatus, device.deviceType assetClass device.deviceId
|search device.firmwareVersion="*" tuneStatus=FAILURE device.accountSourceId!=NULL assetClass!=DVR assetClass!=IPDVR |dedup device.accountSourceId
|search |dedup device.deviceId
|rename device.ecmMacAddress as DEVICE_MAC
| lookup xb6_accountid_mac.csv device.accountSourceId OUTPUT XB6_MAC 
| replace 00:00:00:00:00:00 with NULL 
| eval DEVICE_MAC=coalesce(XB6_MAC, BLUESKY_MAC, DEVICE_MAC)
| lookup FDB_BlueSky_Device_Details.csv DEVICE_MAC OUTPUT CITY, CMTS_DEVICE, LOCATION_ID, MANUFACTURER, MODEL, OPTICAL_RECEIVER_NAME 
| lookup CityToDACRegionMapping.csv CITY OUTPUT DAC_REGION
| search DAC_REGION=*
| timechart usenull=f useother=f span=10min count by DAC_REGION

Re: Why are my timechart results getting skewed?

woodcock — Sun, 03 Mar 2019 07:54:38 GMT

Try this:

index="ngv" device.firmwareVersion="*" tuneStatus=FAILURE device.accountSourceId!=NULL assetClass!=DVR assetClass!=IPDVR
|dedup device.accountSourceId device.deviceId
|rename device.ecmMacAddress as DEVICE_MAC
| lookup xb6_accountid_mac.csv device.accountSourceId OUTPUT XB6_MAC 
| replace 00:00:00:00:00:00 with NULL 
| eval DEVICE_MAC=coalesce(XB6_MAC, BLUESKY_MAC, DEVICE_MAC)
| lookup FDB_BlueSky_Device_Details.csv DEVICE_MAC OUTPUT CITY, CMTS_DEVICE, LOCATION_ID, MANUFACTURER, MODEL, OPTICAL_RECEIVER_NAME 
| lookup CityToDACRegionMapping.csv CITY OUTPUT DAC_REGION
| search DAC_REGION=*
| timechart usenull=f useother=f span=10min count by DAC_REGION

But really, the problem is surely that you are getting a red exclamation-point triangle icon that warns you that your results are truncated because you have too many elements to plot. That means you need ti increase your span=10min to something higher/longer.

Re: Why are my timechart results getting skewed?

bstreber — Sun, 03 Mar 2019 19:37:54 GMT

Thanks for the reply,
I have confirmed that my searches are not getting truncated due to a high number of results. The only notifications I receive is about the implied implicit lookup of the tables I am using.

Re: Why are my timechart results getting skewed?

woodcock — Sun, 03 Mar 2019 22:06:10 GMT

OK, so what do those messages say?

Re: Why are my timechart results getting skewed?

bstreber — Tue, 29 Sep 2020 23:29:13 GMT

Assuming implicit lookup table with filename 'CityToDACRegionMapping.csv'.

Assuming implicit lookup table with filename 'FDB_BlueSky_Device_Details.csv'.

Assuming implicit lookup table with filename 'xb6_accountid_mac.csv'.

Nothing out of the ordinary.

Re: Why are my timechart results getting skewed?

woodcock — Wed, 06 Mar 2019 07:06:40 GMT

Yes, those are warnings for violations of best-practices but the configurations do work.

Re: Why are my timechart results getting skewed?

bstreber — Thu, 14 Mar 2019 18:36:49 GMT

I have solved the issue as it was caused by the dedups in my search. Since Splunk does not take time into consideration when removing duplicate results. Once I removed the dedups in my search it was working correctly.

Re: Why are my timechart results getting skewed?

woodcock — Fri, 15 Mar 2019 13:58:14 GMT

The dedup command will work with time if you add _time to the list of fields in your dedup arguments.

Re: Why are my timechart results getting skewed?

bstreber — Fri, 15 Mar 2019 16:52:27 GMT

This is amazing, thank you!!!

Re: Why are my timechart results getting skewed?

woodcock — Fri, 15 Mar 2019 18:15:37 GMT

Don't forget to UpVote!