Substituting host and source data

[jason_hotchkiss]

Hello - I am using the following two searches:

The first search is creating a table consisting of _time, idx, and b.  There are two other fields available, s for source and h for host.  However, we squash this information for performance reasons.

index=_internal sourcetype=splunkd type=Usage source=*license_usage.log
| table _time idx b
| rename idx as index, b as bytes

I have been trying to figure out a way to substitute the s & h data in the events by using a join, append, or appendcols using:

| tstats count WHERE index=* sourcetype=* source=* unit_id=* by index, sourcetype, source, host, dept
| table index, sourcetype, source,  host, dept

Join Example:

| tstats count WHERE sourcetype=* source=* host=* unit_id=* by index sourcetype source host dept
| table index sourcetype source host dept
| join type=inner index
[ search index=_internal sourcetype=splunkd type=Usage source="/opt/splunk/var/log/splunk/license_usage.log"
| table _time idx b
| rename idx as index, b as bytes]

Append Example:

| tstats count WHERE sourcetype=* source=* host=* unit_id=* by index sourcetype source host dept
| table index sourcetype source host dept
| append
[ search index=_internal sourcetype=splunkd type=Usage source="/opt/splunk/var/log/splunk/license_usage.log"
| table _time idx b
| rename idx as index, b as bytes]


AppendCols Example:

| tstats count WHERE sourcetype=* source=* host=* unit_id=* by index sourcetype source host dept
| table index sourcetype source host dept
| appendcols
[ search index=_internal sourcetype=splunkd type=Usage source="/opt/splunk/var/log/splunk/license_usage.log"
| table _time idx b
| rename idx as index, b as bytes]


Results:

join: just fails with no data
append: the _time and bytes fields are blank
appendcols: leaves out the _time field - which I need to create timecharts with.

The end result should look like this:

_time  index   sourcetype   source   host   dept   bytes

where _time, index, bytes comes from the _internal logs
where index, sourcetype, source, host, dept comes from the | tstats logs

Any help is greatly appreciated.  Thank you.