How to compare savedsearch loadjob artifacts, and use in single value visual?

[mjon395]

Most of my operations are based off of saved searches and these are saved a few times weekly or monthly.

The columns available should always align.

I tried to get the base SPL down so I could have an output with a table showing one column with result from offset=0 (current iteration), and another column with results from offset=1 (1 previous iteration), but I could not get this to work.  I was expecting the below:

Available Columns	Value from Offset=0	Value from Offset=1
# of hosts	1000	955

As an example, the current query would look like this:

| loadjob artifact_offset=0 savedsearch="named_search" ```current week```

| loadjob artifact_offset=1 savedsearch="named_search" ```previous iteration```

Once the table gets figured out, I'm not sure how I could even use the data for a single value visualization, because it would need | timechart count to operate, but my "time" is the value from "artifact_offset"

So, 2 things:

1. Any help with the table to visualize differences between 2 jobs based on artifact_offset?
2. With that table, would it even be possible to use the outputs to populate the single value visual?

Any help here?  Or any other questions I need to answer?

[ITWhisperer]

artifact_offset is not returned by loadjob so you will have to create it yourself - try something like this

| loadjob artifact_offset=0 savedsearch="named_search" ```current week```
| eval artifact_offset=0
| append
  [| loadjob artifact_offset=1 savedsearch="named_search" ```previous iteration```
  | eval artifact_offset=1]
| stats dc(hosts) as hosts by artifact_offset

[mjon395]

This seems to be working, thank you!

Brings me to a new question that I thought of after seeing it working.

When I append multiple saved searches together, what would be the best approach to making the new eval field?

For example (group_name exists in the savedsearches already):

| loadjob artifact_offset=0 savedsearch="named_search_A" ```current week for A group```
| append [| loadjob artifact_offset=0 savedsearch="named_search_B"] ```current week for B group```
| eval artifact_offset=0
| append
  [| loadjob artifact_offset=1 savedsearch="named_search_A" ```previous iteration for A group```
  | eval artifact_offset=1]
| append
  [| loadjob artifact_offset=1 savedsearch="named_search_B" ```previous iteration for B group```
  | eval artifact_offset=1]
| stats dc(hosts) as hosts by artifact_offset group_name

 Is the above code the most efficient approach?  It would seem I need to add the artifact_offset eval after each "1 offset" for each group.

I can't do

[ append [ append [| loadjob artifact_offset=1 savedsearch="named_search_A"] [| loadjob artifact_offset=1 savedsearch="named_search_B"] | eval artifact_offset=1 ]

The 'append' command cannot be the first command in a search

[ITWhisperer · Accepted solution]

You could try this (although I don't know how much more efficient it would be)

| loadjob artifact_offset=0 savedsearch="named_search_A" ```current week for A group```
| append [| loadjob artifact_offset=0 savedsearch="named_search_B"] ```current week for B group```
| eval artifact_offset=0
| append
  [| loadjob artifact_offset=1 savedsearch="named_search_A" ```previous iteration for A group```]
| append
  [| loadjob artifact_offset=1 savedsearch="named_search_B" ```previous iteration for B group```]
| fillnull value=1 artifact_offset
| stats dc(hosts) as hosts by artifact_offset group_name

[mjon395]

This is more efficient (I think) because I have ~40 saved searches, but yes; same results.

Thank you for all the help.