Help on inputlookup with subsearch

[jip31]

Hello

Here is the beginning of my search

As you can see, I cross the USERNAME there is in my inputlookup with `wire` macro

It works

But I woul be able to delete the last line | lookup lookup_fo HOSTNAME as USERNAME output SITE ROOM COUNTRY because normaly all this fields are in my inputlookup?

But when I am doing this, I lose this fields 

Do I am obliged to use this last line or how to do for being able to cross this ield with `wire` macro?

[| inputlookup lookup_fo where TYPE="WW" (DOMAIN=A OR DOMAIN=BOR) (CATEGORY = U OR CATEGORY =W) (STATUS = P) 
    | table HOSTNAME 
    | rename HOSTNAME as USERNAME] `wire` 
| fields AP USERNAME SEEN 
| eval USERNAME=upper(USERNAME) 
| eval LASTSEEN=strptime(SEEN, "%Y-%m-%d %H:%M:%S.%1N")  
| lookup lookup_fo HOSTNAME as USERNAME output SITE ROOM COUNTRY 

Thanks in advance

[richgalloway]

The table HOSTNAME command discards all other fields so the last lookup is needed to retrieve them again.

Similarly, the fields command also discards all fields except AP, USERNAME, and SEEN so the final lookup is needed.

Why is the query starting with a subsearch?  A subsearch adds nothing in this case since it would execute first anyway.

What does the wire macro do?

See if this makes any difference

| inputlookup lookup_fo where TYPE="WW" (DOMAIN=A OR DOMAIN=BOR) (CATEGORY = U OR CATEGORY =W) (STATUS = P) 
| rename HOSTNAME as USERNAME
`wire` 
| fields AP USERNAME SEEN SITE ROOM COUNTRY
| eval USERNAME=upper(USERNAME) 
| eval LASTSEEN=strptime(SEEN, "%Y-%m-%d %H:%M:%S.%1N")  

[jip31]

Yes I know that | table HOSTNAME discards all other fields

And I would like to know if the final lookup was mandatory or not

If not, I need to find a way to retrieve this fields, reason why I have put this question

The macro is doing a matching between the USERNAME of the lookup and the USERNAME that exists in the macro index....

I dont know what you mean by "Why is the query starting with a subsearch?"

I need to cross the fields in the subsearch in order to be able to stats after....

And concerning your code, of course it cant help because there is no matching between the macro and the lookup!

So my question is : is there a solution to retrieve the fields there is my lookup command ( SITE COUNTRY ROOM..) without using the lookup because thèse fields already exists in the inputlookup...

But if I am going | table HOSNAME SITE COUNTRY ROOM I can retrieve them in my stats command

[richgalloway · Accepted solution]

As I said in different words, the final lookup is required because the table command discarded the same fields that were returned by the first lookup.  If you eliminate the table and fields commands then the last lookup should not be necessary.

When SPL is enclosed within square brackets ([ ]) it is called a subsearch.  Subsearches execute first and their results become part of the main search.  Having a subsearch as the beginning of a query makes little sense because there's no change in execution order and there's no main search to which to add the subsearch's results.

I don't know what you mean by "cross the fields".

I didn't know what the macro does so I couldn't be sure if my suggested query would work with it, but the idea was to offer a different approach to problem.