Splunk Search

Combining fields from 2 sourcetypes in a stats block

akelbr
Explorer

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

0 Karma

akelbr
Explorer

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.

0 Karma

akelbr
Explorer

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.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!
0 Karma

akelbr
Explorer

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?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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 ?

Happy Splunking!

akelbr
Explorer

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

akelbr
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...