How to join two searches and table it?

[janitka]

Hello,

I'm trying to join two searches, and i need to use host in the other one, to be able to table it by DesktopGroupName and installed apps. (| table host DisplayName DisplayVersion DesktopGroupName)

host = MachineName, that fields contains same values, in same format.

index = "windows" sourcetype="Script:InstalledApps" - host used
sourcetype="xendesktop:7:machine" xd_index - MachineName used

I can do advanced dashboards, but never tried to do this, any help appreciated.

Jan

[janitka]

Found the solution.

index = "windows" sourcetype="Script:InstalledApps" | join host [ search sourcetype="xendesktop:7:machine" | eval host=MachineName ]
| table MachineName DesktopGroupName DisplayName DisplayVersion

[gcusello]

HI @janitka,
what's the relation between the two searches? I don't see common fields.
If the common field is host you can use something like this:

(index = "windows" sourcetype="Script:InstalledApps" host="$MachineName$") OR (sourcetype="xendesktop:7:machine" 'xd_index' MachineName="$XXX$")
| stats values(DisplayName) AS DisplayName values(DisplayVersion) AS DisplayVersion values(MachineName) AS MachineName values(DesktopGroupName) AS DesktopGroupName BY host

Ciao.
Giuseppe

[janitka]

values returned for host are the same and in same format as for MachineName.. so i would like to use host=$variable$ to be passed to the other source, MachineName, to display DesktopGroupName, from xendesktop... joining two searches into one panel, as i can do it for each search alone.

[gcusello]

HI @janitka,
Ok try this

 (index = "windows" sourcetype="Script:InstalledApps" ) OR (sourcetype="xendesktop:7:machine" 'xd_index' MachineName="$XXX$") host="$MachineName$"
 | stats values(DisplayName) AS DisplayName values(DisplayVersion) AS DisplayVersion values(MachineName) AS MachineName values(DesktopGroupName) AS DesktopGroupName BY host

Ciao.
Giuseppe

[gcusello]

HI @janitka,
join isn't a performant command, I hint you to explore an approach as the one described in my solution.
Ciao.
Giuseppe

[janitka]

hello,

your search is resulting in bellow

https://imgur.com/d6u6Dxm

because for the second source, host is used for server names, in first for every machine.

When i sort it by MachineName, and not host, i miss DisplayName and DisplayVersion.

Regards,
Jan

[gcusello]

HI @janitka,
I don't like join because it's slow, try something like this:

(index = "windows" sourcetype="Script:InstalledApps") OR sourcetype="xendesktop:7:machine"
| eval host=if(sourcetype="Script:InstalledApps",MachineName,host)
| stats values(MachineName) AS MachineName values(DesktopGroupName) AS DesktopGroupName values(DisplayName) AS DisplayName values(DisplayVersion) AS DisplayVersion BY host
| table MachineName DesktopGroupName DisplayName DisplayVersion

Ciao.
Giuseppe

[gcusello]

HI @janitka,
could you share your two searches?

Ciao.
Giuseppe

[janitka]

Important stuff is in this topic, one search is
index = "windows" sourcetype="Script:InstalledApps" host="$MachineName$" | table host DisplayName DisplayVersion

other one
sourcetype="xendesktop:7:machine" 'xd_index' MachineName="$XXX$" | table MachineName DesktopGroupName