topic Search - joining results in table in Splunk Search

Search - joining results in table

janitka — Thu, 29 Oct 2020 16:36:00 GMT

Hello all,

How would I join bellow results by common field -> host? Same index is used.

I was able to create advanced and big dashboards / searches, but cannot use join or other stuff lately, because I'm not working with Splunk daily, and forgot almost everything..

My original intention was to add BuildNumber into this search:

sourcetype="WinHostMon" Type="Disk" Name="C:" host="*" NOT host="*dc.dhl.com*" NOT host="*czchows*" NOT host="*MYKULWS*" NOT host="*czstlws*" NOT host="*usqasws*" | dedup host | eval FreeSpaceKB = round((FreeSpaceKB/1024/1024),2) | eval TotalSpaceKB = round ((TotalSpaceKB/1024/1024),2) | eval percentage=(FreeSpaceKB/TotalSpaceKB*100) | join host [ search sourcetype="xendesktop:7:machine" | eval host=MachineName ] | table MachineName DesktopGroupName FreeSpaceKB TotalSpaceKB percentage | rename FreeSpaceKB AS "Free Space GB" MachineName AS Machine TotalSpaceKB AS "Total Space GB" percentage AS "% Free Space" | sort Machine

Re: Search - joining results in table

kennetkline — Fri, 30 Oct 2020 23:51:12 GMT

Hello,

1. First setup an OR search:
2. make sure your value for join (host) exists in each sourcetype eval host= a, b, or c
3. use stats to pass through and select data points; via values, first, last, earliest, latest depending on if every record has the data point; when not fall back to values; can be a problem if searching intervals where host shows up more than once; might need event status to get the latest events by host too.

4. piece it together;

(sourcetype="WinHostMon" Type="Disk" Name="C:" host="*" NOT host="*dc.dhl.com*" NOT host="*czchows*" NOT host="*MYKULWS*" NOT host="*czstlws*" NOT host="*usqasws*" ) OR ( sourcetype="xendesktop:7:machine" )
| eval MachineName=if(isnotnull(host), host, isnotnull(MachineName), MachineName, null()))
| stats values(DesktopGroupName) as DesktopGroupName, values(FreeSpaceKB) as FreeSpaceKB, values(TotalSpaceKB) as TotalSpaceKB, values(BuildNumber) as BuildNumber by MachineName
| eval percentage=(FreeSpaceKB/TotalSpaceKB*100)
| eval FreeSpaceGB = round((FreeSpaceKB/1024/1024),2)
| eval TotalSpaceGB = round ((TotalSpaceKB/1024/1024),2)
| table MachineName DesktopGroupName FreeSpaceGB TotalSpaceGB percentage
| rename FreeSpaceGB AS "Free Space GB" MachineName AS Machine TotalSpaceGB AS "Total Space GB" percentage AS "% Free Space"

Re: Search - joining results in table

janitka — Mon, 02 Nov 2020 08:55:19 GMT

Hello,

that query is failing on IF

"Error in 'eval' command: The arguments to the 'if' function are invalid."

Also, I don't need to use "sourcetype="xendesktop:7:machine" " that much, main target is to get data for both Type=Disk Name=C: and Type=Operatingsystem, in same table, as I can get the result on separate lines, when I remove | dedup host.

Thanks for your time

Re: Search - joining results in table

inventsekar — Mon, 02 Nov 2020 10:04:24 GMT

Hi @janitka
"Error in 'eval' command: The arguments to the 'if' function are invalid."

Not sure why.. the eval and if looks fine... ok, Please check this if it works -

| eval MachineName=if(isnotnull(host), "host", "MachineName")

Re: Search - joining results in table

janitka — Mon, 02 Nov 2020 11:52:58 GMT

This one works, but the result is that it creates one line, with host as Machine, and then all DesktopGroupNames next to it.

I'm totally dumb and not able to think, because I sorted it once already

I want something like bellow, but to have both space and build number on one line for each respective machine. (xendesktop source is not needed necessary, it only provides additional data for Desktop Group Name)