I have a data model where the object is generated by a search which doesn't permit the DM to be accelerated which means no tstats. I am wanting to do a appendcols to get a delta between averages for two 30 day time ranges. The search I am trying to get to work is:
| datamodel TEST One search
| drop_dm_object_name("One")
| dedup host-ip plugin_id
| where severity > 0
| where earliest=-30d@d latest=+0s
| stats dc(plugin_id) AS signature_count by host-ip
| stats avg(signature_count) as current_avg
| appendcols [| datamodel TEST One search | drop_dm_object_name("One")
| dedup host-ip plugin_id | where severity > 0 | where earliest=--60d@d latest=-30d@d | stats dc(plugin_id) AS signature_count by host-ip | stats avg(signature_count) as historical_avg]
| get_delta(current_avg,historical_avg)
I know the bold items are incorrect, but I am using them as place holders to show the time ranges that are broken out.
So what the heck am I doing wrong? Thanks in advance for any help.
How about this (assuming your data model has time field)
| datamodel TEST One search | where _time>relative_time(now(),"-60d@d")
| eval period=if(_time>relative_time(now(),"-30d@d"),"current_avg","historical_avg")
| drop_dm_object_name("One")
| dedup period host-ip plugin_id
| where severity > 0
| stats dc(plugin_id) AS signature_count by period host-ip
| stats avg(signature_count) as avg by period | eval temp=1
| xyseries temp period avg | fields - temp
| get_delta(current_avg,historical_avg)
How about this (assuming your data model has time field)
| datamodel TEST One search | where _time>relative_time(now(),"-60d@d")
| eval period=if(_time>relative_time(now(),"-30d@d"),"current_avg","historical_avg")
| drop_dm_object_name("One")
| dedup period host-ip plugin_id
| where severity > 0
| stats dc(plugin_id) AS signature_count by period host-ip
| stats avg(signature_count) as avg by period | eval temp=1
| xyseries temp period avg | fields - temp
| get_delta(current_avg,historical_avg)
This works perfectly. Thank you for the help.
When I use _time
it still respects the time filter from the dialog box, which I want to avoid. Both conditions are checked, while only the one in query should be considered.