topic Re: Splunk Search query to track the OS migration on the host in Splunk Search

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

srlakshm — Fri, 28 Oct 2022 14:15:13 GMT

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).

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

Re: Splunk Search query to track the OS migration on the host

gcusello — Thu, 27 Oct 2022 14:17:44 GMT

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:

Ciao.

Giuseppe

Re: Splunk Search query to track the OS migration on the host

richgalloway — Thu, 27 Oct 2022 14:19:43 GMT

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?

Re: Splunk Search query to track the OS migration on the host

srlakshm — Thu, 27 Oct 2022 14:30:55 GMT

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

Re: Splunk Search query to track the OS migration on the host

srlakshm — Thu, 27 Oct 2022 14:32:06 GMT

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

Re: Splunk Search query to track the OS migration on the host

gcusello — Thu, 27 Oct 2022 15:37:08 GMT

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

Re: Splunk Search query to track the OS migration on the host

richgalloway — Thu, 27 Oct 2022 15:41:45 GMT

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

Re: Splunk Search query to track the OS migration on the host

srlakshm — Fri, 28 Oct 2022 08:59:15 GMT

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.