Hi,
I wonder if anyone can help with this please.
I have a search (that runs as part of the PCI compliance app) that when ran as two separate searches work fine, but joined together, the fields time & uptime are in the resultant table but empty. (within the inner search those fields are there and populated just fine).
This is the overall search (That nulls fields uptime and time) - Although asset_id is reported just fine.
| assets
| join type=outer asset_id [| tstats summariesonly
count,latest(All_Performance.OS.Uptime.uptime) as uptime,max(_time) as time from datamodel=Performance.All_Performance where nodename=All_Performance.OS.Uptime by All_Performance.dest | drop_dm_object_name(All_Performance)
| get_asset(dest)
| rename dest_asset_id as asset_id | unprepend_assets(dest)
| search (get_category(pci)
) () | fields time uptime asset_id]
If I simply run the inner search (This works perfectly - reporting time, uptime and asset_id)
| tstats summariesonly
count,latest(All_Performance.OS.Uptime.uptime) as uptime,max(_time) as time from datamodel=Performance.All_Performance where nodename=All_Performance.OS.Uptime by All_Performance.dest | drop_dm_object_name(All_Performance)
| get_asset(dest)
| rename dest_asset_id as asset_id | unprepend_assets(dest)
| search (get_category(pci)
) () | fields time uptime asset_id
Any thoughts - appreciated.
Thanks
Derek
You need an outer join or a comparison that only keeps the members of each set that are not common to both sets. First of all, avoid using join
whenever possible. Try this:
| assets | eval DATASET="assets" | appendpipe [| tstats summariesonly count,latest(All_Performance.OS.Uptime.uptime) as uptime,max(_time) as time from datamodel=Performance.All_Performance where nodename=All_Performance.OS.Uptime by All_Performance.dest | drop_dm_object_name(All_Performance) | get_asset(dest) | rename dest_asset_id as asset_id | unprepend_assets(dest) | search (`get_category(pci)`) () | fields time uptime asset_id | eval DATASET="tstats" ]
| stats dc(DATASET) as countDATASET values(*) AS * by asset_id
| search countDATASET=1
You need an outer join or a comparison that only keeps the members of each set that are not common to both sets. First of all, avoid using join
whenever possible. Try this:
| assets | eval DATASET="assets" | appendpipe [| tstats summariesonly count,latest(All_Performance.OS.Uptime.uptime) as uptime,max(_time) as time from datamodel=Performance.All_Performance where nodename=All_Performance.OS.Uptime by All_Performance.dest | drop_dm_object_name(All_Performance) | get_asset(dest) | rename dest_asset_id as asset_id | unprepend_assets(dest) | search (`get_category(pci)`) () | fields time uptime asset_id | eval DATASET="tstats" ]
| stats dc(DATASET) as countDATASET values(*) AS * by asset_id
| search countDATASET=1
Thanks woodcock, something you said made me realise that the assets were not marked in the asset lookup. I agree on the usage of join. Your approach is also a valid one.
Appreciate the help.