topic Re: help on a jointure without join command in Splunk Search

help on a jointure without join command

jip31 — Wed, 04 Mar 2020 14:02:53 GMT

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"

Re: help on a jointure without join command

gcusello — Wed, 04 Mar 2020 14:48:44 GMT

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

Re: help on a jointure without join command

jip31 — Wed, 30 Sep 2020 04:29:13 GMT

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

Re: help on a jointure without join command

gcusello — Wed, 04 Mar 2020 15:41:00 GMT

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

Re: help on a jointure without join command

jip31 — Wed, 04 Mar 2020 15:48:21 GMT

Yes they are..

Re: help on a jointure without join command

gcusello — Wed, 04 Mar 2020 15:54:03 GMT

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

Re: help on a jointure without join command

gcusello — Wed, 04 Mar 2020 16:29:43 GMT

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

Re: help on a jointure without join command

jip31 — Thu, 05 Mar 2020 06:23:15 GMT

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??

Re: help on a jointure without join command

jip31 — Thu, 05 Mar 2020 07:22:24 GMT

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

Re: help on a jointure without join command

jip31 — Thu, 05 Mar 2020 15:14:20 GMT

is anybody can help please??

Re: help on a jointure without join command

gcusello — Thu, 05 Mar 2020 15:21:15 GMT

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

Re: help on a jointure without join command

jip31 — Fri, 06 Mar 2020 09:07:15 GMT

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

Re: help on a jointure without join command

jip31 — Fri, 06 Mar 2020 17:30:21 GMT

hi have you an idea please??

Re: help on a jointure without join command

jip31 — Mon, 09 Mar 2020 07:02:13 GMT

hi is anybody can help me please?

Re: help on a jointure without join command

gcusello — Wed, 30 Sep 2020 04:31:43 GMT

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

Re: help on a jointure without join command

jip31 — Mon, 09 Mar 2020 11:11:53 GMT

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