best practices vs my code

[jip31]

hello all

i use this code but he has not good performances
following splunk best practices, is it possible to give me ideas of the way to optimize it?? many thanks

`| inputlookup append=t NZDL-Out.csv

| search ComputerName=$tok_filterhost$

| rename ComputerName as host, Online as Ping_Status

| eval Ping_Status =if(Ping_Status=="True","OK","KO")

| join type=outer host [search index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space"

| eval perc_free = if(counter="% Free Space",Value,null)

| eval mb_free = if(counter="Free Megabytes",Value,null)

| stats latest(mb_free) as mb_free latest(perc_free) as perc_free by instance,host

| eval total_space = mb_free / (perc_free) * 100 | eval Disk_(Space_vs_Capacity) = round(mb_free,0)."MB / ".round(total_space,0)."MB"]

| join type=outer host [search index="windows-wmi" sourcetype="WMI:LastLogon" LastLogon | rex field=LastLogon mode=sed "s/..*$//" | eval LastLogon = strftime(strptime(LastLogon,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")]

| join type=outer host [search index="windows-wmi" sourcetype="WMI:LastReboot" LastRebootUpTime | rex field=LastRebootUpTime mode=sed "s/..*$//" | eval LastRebootUpTime = strftime(strptime(LastRebootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")]

| join type=outer host [search index="windows-wmi" sourcetype="wmi:MemorySize" | eval Physical_Memory =round(TotalPhysicalMemory, 0). " Bytes"]

| join type=outer host [search earliest=-120d index=windows sourcetype=winregistry key_path="\registry\machine\software\wow6432node\airbus\master\PatchLevel"

| stats first(data) as PatchLevel by host

]

| join type=outer host [search index="windows-wmi" sourcetype="wmi:videosignal"

| rename SystemName as host

| lookup Availability.csv Availability

]

| table host, TimeStamp, Ping_Status, Status, Disk_(Space_vs_Capacity), Physical_Memory, PatchLevel, LastLogon, LastRebootUpTime | rename Status as Video_Signal_Status

| sort -TimeStamp, +host

| dedup host
`

[MuS · Accepted solution]

Hi jip31,

to start with:
you are using 6 times join which is causing the performance issues and a lot other problems you probably not even notice you have them 😉

As a start combine all you searches into one single base search:

 ( index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space" ) OR 
 ( index="windows-wmi" sourcetype="WMI:LastLogon" LastLogon ) OR ( index="windows-wmi" sourcetype="WMI:LastReboot" LastRebootUpTime ) OR 
 ( index="windows-wmi" sourcetype="wmi:MemorySize" ) OR 
 ( earliest=-120d index=windows sourcetype=winregistry ) OR 
 ( index="windows-wmi" sourcetype="wmi:videosignal" )

and do what ever needs to be done in the next SPL steps. I you want to use the first lookup file as filter for the base searches you can actually just do something like this:

 ( index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space" ) OR 
( index="windows-wmi" sourcetype="WMI:LastLogon" LastLogon ) OR ( index="windows-wmi" sourcetype="WMI:LastReboot" LastRebootUpTime ) OR 
( index="windows-wmi" sourcetype="wmi:MemorySize" ) OR 
( earliest=-120d index=windows sourcetype=winregistry ) OR 
( index="windows-wmi" sourcetype="wmi:videosignal" ) 
[| inputlookup append=t NZDL-Out.csv 
| search ComputerName=$tok_filterhost$ 
| rename ComputerName as host, Online as Ping_Status 
| eval Ping_Status =if(Ping_Status=="True","OK","KO") 
| table host Ping_Status 
| format ]

The sub search here is okay, because it uses a lookup file and will return a OR search pattern like ((host=foo1 AND Ping_Status="KO") OR (host=foo2 AND Ping_Status="OK"))

Once you get the base search sorted, you can do all the rename, eval and/or stats to get the result you need.

Hope this helps ...

cheers, MuS

[jip31]

HI Mus and thanks

when you say "As a start combine all you searches into one single base search" does i have to create 2 search (one for search and one for next steps)? Or just one serarch?
when i execute your code i have no results pearhaps because inputlookup append=t NZDL-Out.csv has to be put at the beginning???

[jip31]

it works until here but after i dont succeed............

( index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space" ) OR
( index="windows-wmi" sourcetype="WMI:LastLogon" LastLogon ) OR ( index="windows-wmi" sourcetype="WMI:LastReboot" LastBootUpTime ) OR
( index="windows-wmi" sourcetype="wmi:MemorySize" ) OR
( earliest=-120d index=windows sourcetype=winregistry ) OR
( index="windows-wmi" sourcetype="wmi:videosignal" )

| inputlookup append=t NZDL-Out.csv
| rename ComputerName as host, Online as Ping_Status
| eval Ping_Status =if(Ping_Status=="True","OK","KO")
| table host Ping_Status | dedup host

[MuS]

okay, what are you trying to achieve with the inputlookup? I f you just want to get the Ping_Status added as column based on host do something like this:

( index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space" ) OR 
( index="windows-wmi" sourcetype="WMI:LastLogon" LastLogon ) OR ( index="windows-wmi" sourcetype="WMI:LastReboot" LastBootUpTime ) OR 
( index="windows-wmi" sourcetype="wmi:MemorySize" ) OR 
( earliest=-120d index=windows sourcetype=winregistry ) OR 
( index="windows-wmi" sourcetype="wmi:videosignal" ) 
| inputlookup append=t NZDL-Out.csv 
| eval Ping_Status = if(Online=="True","OK","KO"), host = case(isnotnull(ComputerName), ComputerName, isnotnull(host), host, 1=1, "unknown")
| stats values(*) AS * by host | do more SPL-fu here 

[jip31]

HI Mus and thanks

when you say "As a start combine all you searches into one single base search" does i have to create 2 search (one for search and one for next steps)? Or just one serarch?
when i execute your code i have no results pearhaps because inputlookup append=t NZDL-Out.csv has to be put at the beginning???

[MuS]

The examples just show you possible ways to to do it, they are not bullet proof. No you don't need to create two searches. And no, the second example will work if you adjust it to your real world events.

cheers, MuS

[jip31]

yes mus, but i have difficulty to adapt it.....