Archive

help on a jointure without join command

Contributor

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"
Tags (1)
0 Karma

Contributor

hi is anybody can help me please?

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

Contributor

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

0 Karma

Contributor

is anybody can help please??

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

hi have you an idea please??

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

Yes they are..

0 Karma