A column from mstats becomes empty after join command

[eddieddieddie]

Hi,

I'm trying to create a dashboard which shows various stats for a list of servers. It will pull it's data from several indexes as some of the data is stored as metrics and others in standard indexes. I started creating the dashboard using the metric data first - that's all fine. Now I'm trying to get some data from a different index and am using a JOIN to link it to the first. Whilst the Join works one of the fields - "Uptime_seconds" - in the initial search generates becomes empty.

My initial query is:

| mstats latest(System.System_Up_Time) as Uptime_seconds latest(Processor.%_Processor_Time) as CPUusage latest(LogicalDisk.Free_Megabytes) as FreeDisk latest(LogicalDisk.%_Free_Space) as FreePercentSpace WHERE index=em_metrics by host

This returns everything as expected.

When I add the join command - the following query:

| mstats latest(System.System_Up_Time) as Uptime_seconds latest(Processor.%_Processor_Time) as CPUusage latest(LogicalDisk.Free_Megabytes) as FreeDisk latest(LogicalDisk.%_Free_Space) as FreePercentSpace WHERE index=em_metrics by host
| join host [search index=wineventlog | stats count as EventCount by host | fields host, EventCount]

The Uptime_seconds field disappears - the column is still there in the resulting table but it's always empty.

I assume something from the Join sub search is overwriting it - hence my use of the fields command to remove everything but the fields I need, but this hasn't helped.

Is there something I don't understand about the Join command which is removing this data?

Thanks, Eddie

[eddieddieddie]

Thanks for the suggestion @bowesmana using an append to run the sub search and then using stats to merge the data together works. So instead of using a join I'm going to go with that method.

For reference here's basically the same functionality as the join method in my initial question but using append and stats functions instead:

| mstats  latest(System.System_Up_Time) as Uptime_seconds latest(Processor.%_Processor_Time) as CPUusage latest(LogicalDisk.Free_Megabytes) as FreeDisk latest(LogicalDisk.%_Free_Space) as FreePercentSpace WHERE index=em_metrics by host
| append 
    [search index=wineventlog severity!=informational tag=error | stats count as EventCount by host]
| stats list(Uptime_seconds) list(CPUusage) list(FreeDisk) list(FreePercentSpace) list(EventCount) by host

[bowesmana]

@eddieddieddie 

Glad that it worked - I am not sure why that didn't work with join - if you're interested there's a really good talk from .conf 2019, where append and join are discussed and their performance explained. Slides are here

https://conf.splunk.com/files/2019/slides/FNC2751.pdf

If you're off the type that likes to understand these things, then it might be interesting to run the mstats as the subsearch and the standard search as the outer - it would be interesting to see if one performed better than the other.

Other than by using join or append/stats, I'm not sure how one combined both index and metric data.

[bowesmana]

@eddieddieddie 

join can be finicky - it has a number of limitations and if you are joining against sizeable data sets then you may come across these.

Notably there is a 60 second timeout on the subsearch and a 50,000 result limit. Not sure if you are hitting these, but it will depend on what your search time range is.

To see if this is a factor you can always add in things like

earliest=-5m@m latest=@m

to your join subsearch so that it only searches a 'small' amount of data. 

The other suggestion is that Uptime_seconds has an underscore in the middle - whereas no other field does. I have no idea if this is an issue, but remove the _ and see if that helps - total finger in the air...

[eddieddieddie]

Thanks for the response @bowesmana. I tried your suggests and neither seemed to fix it:

I tried reducing the time the query runs and the issue still occurs (I also reviewed the job inspection report and can't see that the query is being ended early).

I also tried your second suggestion and renamed the field to remove the underscore - this made no difference. Also removing other fields didn't help - it seems something between the data in the field 'latest(System.System_Up_Time)' and the join command do not play well together and this column ends up empty once the join has run. Even trying to move the data into a new and different field with an eval didn't make any difference.

I wonder if this is a bug or limitation of Join in 8.0.6?

[bowesmana · Accepted solution]

I wondered if it was related to the data from an mstats call.

You could try using append with that subsearch to see how the data looks without trying to join. If that looks OK, the you could do stats values by host to aggregate the EventCount column with the rest of the columns. 

That at least will give you another angle to help diagnose.