help on a jointure without join command

[jip31]

hi

I use the complex search below
As you can see, there i a subsearch linked with a join command
I find a way to do the same search but without the join command
I started to write this search (see below) but I have an issue because the field "host" in wireis called "USERNAME"
So I need to do | rename USERNAME as host but it doesnt works and as a consequence I am unable to do a "stats by" after
Is anybody can help me??

`wire` earliest=-30d latest=now 
    | fields USERNAME NAME Building AP_NAME 
    | rename USERNAME as host 
    | eval host=upper(host) 
    | lookup toto.csv NAME as AP_NAME OUTPUT Building 
    | eval Building=upper(Building) 
    | stats last(AP_NAME) as "AP", last(Building) as "Geol" by host 
    **| join host type=outer** 
        [| search `LastLogonBoot` earliest=-30d latest=now 
        | fields host SystemTime EventCode 
        | eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'") 
        | stats latest(SystemTime) as SystemTime by host EventCode 
        | xyseries host EventCode SystemTime 
        | rename "6005" as LastLogon "6006" as LastReboot 
        | eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0) 
        | eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M") 
        | lookup tutu.csv HOSTNAME as host output SITE BUILDING_CODE DESCRIPTION_MODEL ROOM STATUS 
        | stats last(LastReboot) as "Last reboot date", last(NbDaysReboot) as "Days without reboot", last(DESCRIPTION_MODEL) as Model, last(SITE) as Site, last(AP_NAME) as AP, last(BUILDING_CODE) as Building, last(ROOM) as Room, last(STATUS) as Status by host ] 
    | search "Days without reboot" > 5 
    | search Site = *
    | rename host as Hostname 
    | table Hostname Model Status "Days without reboot" "Last reboot date" Site Building Room AP Geol
    | sort -"Days without reboot"




 [| inputlookup host.csv 
        | table host 
            ] (`LastLogonBoot`) OR (`wire`) earliest=-24h latest=now 
    | fields host SystemTime EventCode USERNAME NAME AP_NAME 
**| rename USERNAME as host**
    | lookup tutu.csv NAME as AP_NAME OUTPUT Building 
    | eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'") 
    | stats latest(SystemTime) as SystemTime by host EventCode 
    | xyseries host EventCode SystemTime 
    | rename "6005" as LastLogon "6006" as LastReboot 
    | eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0) 
    | eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M") 
    | lookup toto.csv HOSTNAME as host output SITE BUILDING_CODE DESCRIPTION_MODEL ROOM STATUS 
    | stats last(LastReboot) as "Last reboot date", last(NbDaysReboot) as "Days without reboot", last(DESCRIPTION_MODEL) as Model, last(SITE) as Site, last(BUILDING_CODE) as Building, last(ROOM) as Room, last(STATUS) as Status by host 
    | sort -"Days without reboot"

[jip31]

hi is anybody can help me please?

[gcusello]

Hi @jip31,
use your subsearch as the main search and join the lookup using the lookup command that's similar to the left join command.
I cannot test it, but thge approach is something like this:

(first search) OR (second search)
| rename fields_of_first_search AS fields_of_the_second_search
| stats values(field1) AS field1 values(field2) AS field2 values(field) AS field3 BY host
| table intersting_fields

Ciao.
Giuseppe

[gcusello]

Hi @jip31,
at first see if without the last filter ( | search "Days without reboot" > 5 ) you have events.

Then see if after the xyseries you still have host and EventCode fields (to do this delete all the rows until xyseries command).

Ciao.
Giuseppe

[gcusello]

Hi @jip31,
haven't you results after xyseries or haven't you the presence of host and EventCode fields?
in first case analyze fields before xyseries command, in the second try the way to not use xyseries command.

Ciao.
Giuseppe

[jip31]

i have deleted | search "Days without reboot" > 5 for being sure it's not the problem
and effectively i have no results after xyseries but i dont know why??

[jip31]

I have identified the issue
when i execute the code below, I have results

[| inputlookup host.csv 
    | table host ] (`LastLogonBoot`) OR (`wire`) earliest=-24h latest=now 
| fields host SystemTime EventCode USERNAME NAME AP_NAME 
| lookup toto.csv NAME as AP_NAME OUTPUT Building 
| eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'") 
| stats latest(SystemTime) as SystemTime by host EventCode 
| xyseries host EventCode SystemTime 
| rename "6005" as LastLogon "6006" as LastReboot 
| eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0) 
| eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M") 
| lookup tutu.csv HOSTNAME as host output SITE BUILDING_CODE DESCRIPTION_MODEL ROOM STATUS  
| stats last(LastReboot) as "Last reboot date", last(NbDaysReboot) as "Days without reboot", last(DESCRIPTION_MODEL) as Model, last(SITE) as Site, last(BUILDING_CODE) as Building, last(ROOM) as Room, last(STATUS) as Status by host 
| sort -"Days without reboot"

But like in wire the field "host" is called "USERNAME", I need to add a | rename USERNAME as host
But when I am doing this, I have no results and I dont understand why

[jip31]

is anybody can help please??

[gcusello]

Hi @jip31,
USERNAME is present only in wire or also in LastLogonBoot?
try to add, instead of | rename USERNAME as host,

| eval host=if(index=wire_index, USERNAME,host)

I don't know your data, identify a specific field that's only present in wire and not in LastLogonBoot (e.g. index or sourcetype) and use it to assign the correct value to host.

Ciao.
Giuseppe

[jip31]

hi have you an idea please??

[jip31]

Hi
USERNAME is only present in wire
Concerning the eval, it doesnt works (Error in 'eval' command: The expression is malformed. Expected )

[gcusello]

Try
| eval host=if(index="wire_index", USERNAME,host)
or search another balue field to identify wire from LastLogonBoot (e.g. index="wire_index").

Ciao.
Giuseppe

[jip31]

I dont know why you use index in the eval
wire is a macro which index + sourcetype
and I tried many combinaisons with your eval and it doesnt works
finally, there is no other field for matching wire and LastLogonBoot
If I am doing :
| inputlookup host.csv
| table host
| rename host as USERNAME
earliest=-24h latest=now
| rename USERNAME as host
| eval host=upper(host)

USERNAME is well renamed by host

[jip31]

hi
I done this but no results
(wire) OR (LastLogonBoot)
| fields USERNAME NAME Building AP_NAME host SystemTime EventCode
| rename USERNAME as host
| eval host=upper(host)
| lookup toto.csv NAME as AP_NAME OUTPUT Building
| eval Building=upper(Building)
| eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")
| xyseries host EventCode SystemTime
| rename "6005" as LastLogon "6006" as LastReboot
| eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 0)
| eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M")
| lookup tutu.csv HOSTNAME as host output SITE BUILDING_CODE DESCRIPTION_MODEL ROOM STATUS
| stats latest(SystemTime) as SystemTime last(AP_NAME) as "Access point", last(Building) as "Geolocation building", last(LastReboot) as "Last reboot date", last(NbDaysReboot) as "Days without reboot", last(DESCRIPTION_MODEL) as Model, last(SITE) as Site, last(BUILDING_CODE) as Building, last(ROOM) as Room, last(STATUS) as Status by host EventCode
| search "Days without reboot" > 5

[gcusello]

Hi @jip31,
check if the fields you used in xyseriers and stats commands are present in all the events, probably it isn't correct some rename.

Ciao.
Giuseppe

[jip31]

Yes they are..