How to search to track the OS migration on the host?

[srlakshm]

Hi folks,

I need your support to build a search query to track the migration activity. We have a requirement to track the host which will be migrated from windows os to linux os. The search should visualize the movement of migration activity.

I have two lookup files, one is windows os host details. Another one is linux os host . So I need to compare how many machines migrated from Windows to Linux over the time. (last 7 days).

| inputlookup windows.csv 
| fillnull value="windows" OS
| inputlookup linux.csv append=1
| fillnull value="linux" OS
| stats dc(OS) as count values(lastSeen) as LastSeen, values(FirstSeen) as Firstseen by hostname
| where count > 1
| mvexpand OS

The above query doesn't show expect the result
 I would really appreciate, if someone has any ideas or suggestions on this.   

[richgalloway]

What fields are in each lookup file?  What are the expected results?  What are the current results?  When a host is migrated to Linux are both lookup files updated?

[srlakshm]

Yes, The both lookup are updated every day at once, The lookup file contain these are the fields hostname, os, lastseen, firstseen

[gcusello]

Hi @srlakshm,

inputlookup command cannot be in the middle of a search, only at the beginning of a search or a subsearch, 

so, please try something like this:

| inputlookup windows.csv 
| fillnull value="windows" OS
| append [ | inputlookup linux.csv | fillnull value="linux" OS ]
| stats 
    dc(OS) as os_count 
    values(OS) AS OS
    values(lastSeen) as LastSeen, 
    values(FirstSeen) as Firstseen 
    BY hostname
| where os_count>1
| table hostname OS Firstseen LastSeen

Ciao.

Giuseppe

[richgalloway]

The inputlookup command can be used in the middle of a query if the append=true option is used (as the OP did).

[srlakshm]

Hi Gusello,

Thank you for the response, 

  I have tried a similar search query and got the same result. The challenge on the results are multivalue value fields with below format. 

lastSeen -                             OS                         firstSeen

26-10-2022 13:07:16      windows             26-10-2022 13:07:16 

8/29/2022 1:52                 linux                     8/29/2022 1:52 

I am expecting the below results.

hostname     os                     firstSeen          lastSeen

abcde            windows        25-08-2022     26-10-2022

abcde            Linux                26-10-2022       27-10-2022 

[gcusello]

Hi @srlakshm,

in this case, you have to add OS to the BY clause:

| inputlookup windows.csv 
| fillnull value="windows" OS
| rename Firstseen AS win_Firstseen LastSeen AS win_LastSeen 
| append [ | inputlookup linux.csv | fillnull value="linux" OS | rename Firstseen AS x_Firstseen LastSeen AS x_LastSeen ]
| stats 
    dc(OS) as os_count 
    values(OS) AS OS
    values(win_lastSeen) as win_LastSeen, 
    values(win_FirstSeen) as win_Firstseen 
    values(x_lastSeen) as x_LastSeen, 
    values(x_FirstSeen) as x_Firstseen 
    BY hostname
| where os_count>1
| mvexpand OS
| eval LastSeen=coalesce(win_LastSeen,x_LastSeen), Firstseen=coalesce(win_Firstseen,x_Firstseen)
| table hostname OS Firstseen LastSeen

Ciao.

Giuseppe

[srlakshm]

  Thanks for the query, unfortunately the coalesce is not working as expected. The LastSeen and FirstSeen value is taking the first field value win_LastSeen for both OS.

Have observed the same issue previously as well. the coalesce command not working for lookup files as expected.