Why is _time is NULL when using JOIN?

[malmiran]

I have this search query:

| inputlookup "asset-list" 
| SEARCH PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" 
| rex field=NAME "(?<NAME>[^\\\]+)" 
| JOIN type=inner NAME [SEARCH sourcetype="alerts-list" 
| RENAME Hostname AS NAME] 
| table AlertName, _time, AlertDescription, ServerName, ServerRole

It's working except the _time values are NULL (empty). The _time is taken from "alert-list".

if I use this form, it works (the _time values appear).

sourcetype="alerts-list"
| eval NAME = Hostname
| lookup "asset-list" NAME OUTPUTNEW ServerName ServerRole
| search PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" 
| table AlertName, _time, AlertDescription, ServerName, ServerRole

However, I can't use the query above (i.e. using lookup instead of join) because I need to do a REX against the NAME field in the asset-list that I use as the join/lookup key (NAME=Hostname), and I can't figure out how to do that with something other than using a JOIN.

Any ideas on what I"m missing; any other approaches I can try? Thx!

[malmiran]

I resorted to using an extracted field (for the alert time) with global perms, and I am now able to get the result I need, still using the JOIN command.

 | inputlookup "asset-list" 
 | SEARCH PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" 
 | rex field=NAME "(?<NAME>[^\\\]+)" 
 | JOIN type=inner NAME [SEARCH sourcetype="alerts-list"  | RENAME Hostname AS NAME] 
 | table AlertName, AlertTime, AlertDescription, ServerName, ServerRole

Where "AlertTime" is an extracted field in "alert-list".

It feels like a bit of a hack but at this stage, unless folks here tell me there's a major caveat I haven't considered, I'll run with this for now. 🙂

[knielsen]

So you are using the search on "asset-list" to get a list of hosts to use as filter for event in sourcetype="alert-lists"?

Then you can do it like this:

sourcetype="alerts-list" [ | inputlookup "asset-list" | SEARCH PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" | rex field=NAME "(?<Hostname>[^\\\]+)" | fields Hostname ] | table AlertName, _time, AlertDescription, ServerName, ServerRole

That is, if you have less than 50,000 hosts as result from your inputlookup search.

[malmiran]

Hi @knielsen, not exactly. If I understand it correctly, a subsearch is used to limit the results of your main search using data obtained from a secondary search (your subsearch). However, what I'm trying to do is to literally join 2 distinct tables (sources) - (1) alert-list (my main event data) and (2) asset-list (my source data that contains meta-data about servers (e.g. server impact, role, organization, etc.), so I can report on alerts and the assets metadata. The only commonality between these two sources is the server name field ( in alerts-list and NAME in asset-list) However, there are two caveats I'm struggling with:

1) The values of the Hostname and NAME may not always be the same. The only way i can force the equation is to use my REX extraction above. Because of this necessity, I'm unable to use a simple Lookup command to join the two searches, which is, from my readings, should be the most suitable method given the source of my secondary data (asset-list) is virtually static.

2) The asset-list is actually a Lookup table, so I'm forced to use | inputlookup in my searches, which I find limiting when trying to do some form of joins.

[johnnyfrx]

Would append work?

[malmiran]

Hi @johnnyfrx .

Maybe I just don't know the right way but I can't seem to make it work with append. Also, would append work if my intention is to join the two tables vertically, so I can pull up some of the columns from the secondary table?