topic Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval? in Splunk Search

Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

responsys_cm — Tue, 25 Sep 2018 21:08:30 GMT

I'm trying to work around the limitations of data model root searches not supporting pipes.

Is there any way to do see if fieldX=fieldY at the root search level or does Splunk always treat the "fieldY" as a string?

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

zonistj — Tue, 25 Sep 2018 22:09:10 GMT

Data models can be interacted with in multiple ways. Can you provide the search that you're working on? Also, is the data model accelerated?

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

responsys_cm — Tue, 29 Sep 2020 21:26:10 GMT

The search needs to use something like eventstats to find the most recent timestamp for the events, in this case the last time a destination IP was scanned by a vulnerability scanner. But searches with pipes aren't supported by data models.

So, I could put the most recent scan time for each IP in a lookup table and create an automatic lookup for it. That way I could have a top level search that says _time=last_scan_time if I can get the last_scan_time to be treated as a field like with WHERE rather than a string...

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

zonistj — Tue, 25 Sep 2018 23:29:15 GMT

I'd like to help you, but I need more information. There are ways to search data models using pipes.

For example, we can use tstats to search the authentication data model and use trailing pipe commands:

| tstats `summariesonly` values(Authentication.app) AS app count from datamodel=Authentication.Authentication WHERE Authentication.user!="unknown" by Authentication.action,Authentication.user 
| `drop_dm_object_name("Authentication")`
| eval success=if(action="success",count,0),failure=if(action="failure",count,0)
| stats values(app) as app,sum(failure) as failure,sum(success) as success by user
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium
| sort - failure
| eval failure = tostring('failure',"commas"), success = tostring('success',"commas")

We can also use the "|from datamodel" syntax:

| from datamodel:"Authentication"."Authentication" 
| search user="malicious_user" errorCode="AccessDenied" 
| stats count by app

Can you provide some additional info about what data model you're working with, and maybe a sample of the search you have so far?

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

responsys_cm — Tue, 29 Sep 2020 21:26:13 GMT

I'm not trying to search the data model, I am trying to feed data into it. I want to run something like this:

sourcetype=qualys earliest=-30d@d | eventstats max(_time) AS last_scan by dest | where _time=last_scan

That will give me the most recent scan of all hosts over the last 30 days. I want that in a data model since tscollect and namespaces aren't supported on search head clusters.

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

DalJeanis — Thu, 27 Sep 2018 01:55:15 GMT

See if you can manage that with a tstats command.

Something like...

  | tstats max(_time) where index=foo sourcetype=qualys by dest

I doubt if dest is an index time field, so it might not be available to tstats.

If not, then if you just want the most recent _time, then try dedup

  index=foo sourcetype=qualys earliest=-30d@d | dedup dest

With regard to your underlying question,

1) search treats the right side of an = as a literal, whereas where evaluates both sides.

2) There is no way to compare two fields before the first pipe.

Re: Is it possible to compare equality of two fields at the root search without using | search, | where, or | eval?

zonistj — Thu, 27 Sep 2018 15:58:26 GMT

I understand now. Thank you for the clarification.

I think your best bet is to feed events into the data model and then create a separate search or report that pulls out the latest scan events. I'll try a few things to see if I can get it to work and let you know.