topic Re: Combining fields from 2 sourcetypes in a stats block in Splunk Search

Combining fields from 2 sourcetypes in a stats block

akelbr — Fri, 18 Jan 2019 13:28:35 GMT

All,
my query below just returns the values from the first sourcetype (first 3 lines in |stats). The fields from the second sourcetype seems that are not being loaded.
Any clue? I`m sure is something really simple but I can not see it:

index=uberagent (sourcetype=uberAgent:Application:SoftwareUpdateInventory host=*) OR sourcetype=uberAgent:System:SystemPerformanceSummary2
| stats
latest(DisplayName) as "Patch Name"
latest(ProductName) as "Product name"
values(State) as State
avg(CPUUsagePercent) as "CPU"
avg(RAMUsagePercent) as "RAM"
avg(IOPercentDiskTime) as "IO"
by host, InstallDate
| rename host as "Machine Name"
| mvexpand InstallDate
| mvexpand host
| eval "% of CPU" = round((CPU),2)
| eval "% of RAM" = round((CPU),2)
| eval "% of IO" = round((CPU),2)
| eval sortfield=lower(InstallDate)
| table
InstallDate
"Machine Name"
"Patch Name"
"Product name"
State
"% of CPU"
"% of RAM"
"% of IO"
sortfield
| sort limit=0 sortfield
| fields - sortfield

Re: Combining fields from 2 sourcetypes in a stats block

akelbr — Fri, 18 Jan 2019 13:30:59 GMT

Forgot to add: My ultimate goal is to have a list with the KBs installed on the machines and the avg resource used on that day. Trying to find any outstanding value during the patching process.

Re: Combining fields from 2 sourcetypes in a stats block

renjith_nair — Fri, 18 Jan 2019 13:36:25 GMT

Do you have host & InstallDate fields in sourcetype=uberAgent:System:SystemPerformanceSummary2 and are non null?

Try the below and see if you have some events in common.

index=uberagent (sourcetype=uberAgent:Application:SoftwareUpdateInventory host=*) OR sourcetype=uberAgent:System:SystemPerformanceSummary2 
|stats dc(sourcetype) as c by host,InstallDate  |where c>1

Re: Combining fields from 2 sourcetypes in a stats block

akelbr — Fri, 18 Jan 2019 13:43:08 GMT

Of course! that is the point! 🙂

The InstallDate is not on sourcetype 2.

Said that, I need my calculations based on the InstallDate values.

How can I do that?

Maybe in this case I should use the JOIN with HOST field that is common to both?

Re: Combining fields from 2 sourcetypes in a stats block

renjith_nair — Fri, 18 Jan 2019 14:10:31 GMT

Can not really say without seeing the events. But you may try including the InstallDate in existing stats as values(InstallDate ) as InstallDate . In between, in the currrent search are you getting host and InstallDate as multivalue field or wha'ts the use of mvexpand host and mvexpand InstallDate ?

Re: Combining fields from 2 sourcetypes in a stats block

akelbr — Fri, 18 Jan 2019 15:54:01 GMT

Thank you for you help on this!

I`ve tried to use values(InstallDate ) as InstallDate in the current stats but got the following error:
Error in 'stats' command: The output field 'InstallDate' cannot have the same name as a group-by field.

if use a separated stats, nothing comes up.

About the mvexpand, I`m using because was the way a found to get the same result as the splitrow in pivot.

See below an example of the events:

InstallDate Machine Name Patch Name ProducName %of CPU %of RAM % of IO
2018-01-01 KB1234 Office

2018-01-01 KB1234 Office

2018-01-02 KB1234 Office

Re: Combining fields from 2 sourcetypes in a stats block

akelbr — Fri, 18 Jan 2019 15:55:18 GMT

InstallDate Machine Name Patch Name ProducName %of CPU %of RAM % of IO
2018-01-01 SERVERA KB1234 Office null null null
2018-01-01 SERVERB KB1234 Office null null null
2018-01-02 SERVERC KB1234 Office null null null

Re: Combining fields from 2 sourcetypes in a stats block

renjith_nair — Sat, 19 Jan 2019 02:52:18 GMT

values(InstallDate ) as InstallDate by host (exclude the InstallDate from by clause)

Re: Combining fields from 2 sourcetypes in a stats block

akelbr — Mon, 04 Feb 2019 10:24:59 GMT

The solution was to have the data from sourcetype 2 exported to a file and then using a lookup with sourcetype 1.
It works like a charm! The only thing is that I from time to time I need to run the export search to update the file.

Thank you renjithnair for help me on this.