Help on complex request in order to improve the performances

[jip31]

Hello

I need help on a complex request with different indexes and with a "primary key" (host which is the name of the computer)
The code is :
| inputlookup append=t NZDL-Out.csv

| rename ComputerName as host, Online as Ping
| search host=$tok_filterhost$ 
| eval Ping =if(Ping=="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 _time, host, instance 
| eval total_space = mb_free / (perc_free) * 100 
| eval diff=round((total_space-mb_free)/1024,2) 
| eval DiskSize = round(mb_free/1024,2)." MB / ".round(total_space/1024,2)." MB"
| eval time = strftime(_time, "%d-%m-%y %H:%M") 
| sort -time]

| 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" LastBootUpTime 
| rex field=LastReBootUpTime mode=sed "s/\..*$//" 
| eval LastBoot = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")] 

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

| join type=outer host [search earliest=-120d index=windows sourcetype=winregistry 
key_path="\\registry\\machine\\software\\wow6432node\\xx\\master\\PatchLevel" 
OR
key_path="\\registry\\machine\\software\\wow6432node\\xx\\master\\WindowsVersion" 
OR
key_path="\\registry\\machine\\software\\microsoft\\windows nt\\currentversion\\ReleaseId"
| rex field=key_path "(?<type>\w+)$" 
| chart first(data) by host type]

| join type=outer host [search index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode
| rename ConfigManagerErrorCode as ErrorCode 
| lookup  Availability.csv ErrorCode 
| table host ErrorCode Meaning] 

| join type=outer host [inputlookup append=t NZDL.csv]

| join type=outer host [search index=windows sourcetype=tools:flags filename=*ABDM-TOUPDATE* 
| eval filename=if(filename=="ABDM-TOUPDATE.$$w$$", "Yes", "No")]

| table host, Building, Floor, Gate, Ping, Meaning, DiskSize, diff, MemorySize, PatchLevel, 
LastLogon, LastBoot, WindowsVersion,  ReleaseId, filename
| rename Meaning as Video
| sort  +host
| dedup host

In order to habe better performances, i try to improve the structure of the request
So first i put the different searches, after i put the Eval, Stats and Rename functions and at the end the Table and Sort functions
The code is below but there i habe no results
Could you help me please??

(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 key_path="\\registry\\machine\\software\\wow6432node\\xx\\master\\PatchLevel" 
OR
key_path="\\registry\\machine\\software\\wow6432node\\xx\\master\\WindowsVersion" 
OR
key_path="\\registry\\machine\\software\\microsoft\\windows nt\\currentversion\\ReleaseId"
) OR (index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode) OR (index=windows sourcetype=tools:flags filename=*ABDM-TOUPDATE*) 
| inputlookup append=t NZDL-Out.csv 
| inputlookup append=t NZDL.csv

| search host=$tok_filterhost$ 

| eval Online =if(Online=="True","OK","KO") 
| rename ComputerName as host, Online as Ping

| 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 _time, host, instance 
| eval total_space = mb_free / (perc_free) * 100 
| eval diff=round((total_space-mb_free)/1024,2) 
| eval DiskSize = round(mb_free/1024,2)." MB / ".round(total_space/1024,2)." MB"
| eval time = strftime(_time, "%d-%m-%y %H:%M") 
| sort –time

| rex field=LastLogon mode=sed "s/\..*$//" 
| eval LastLogon = strftime(strptime(LastLogon,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")

| rex field=LastReBootUpTime mode=sed "s/\..*$//" 
| eval LastBoot = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")

| eval MemorySize =round(TotalPhysicalMemory/1024000000, 0). " GB"

| rex field=key_path "(?<type>\w+)$" 
| chart first(data) by host type
| rename ConfigManagerErrorCode as ErrorCode 
| lookup  Availability.csv ErrorCode 
| rename Meaning as Video

| eval filename=if(filename=="ABDM-TOUPDATE.$$w$$", "Yes", "No")

| table host, Building, Floor, Gate, Ping, DiskSize, diff, LastLogon, LastBoot, MemorySize, PatchLevel, WindowsVersion, ReleaseId, ErrorCode, Video
| sort  +host
| dedup host

[HiroshiSatoh]

I think that if you add the following subsearch to all joined search sentences and filter it, it will be faster.
I think that it can be modified comfortably if it further macrosizes.

[| inputlookup append=t NZDL-Out.csv
 | rename ComputerName as host
 | search host=$tok_filterhost$
 | table host] 

ex.)

 | join type=outer host [search index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: 
 counter="Free Megabytes" OR counter="% Free Space"
↓
 | join type=outer host [search index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: 
 counter="Free Megabytes" OR counter="% Free Space"  [| inputlookup append=t NZDL-Out.csv
     | rename ComputerName as host
     | search host=$tok_filterhost$
     | table host] 

[renjith_nair]

Hi @jip31,

You might need to take a step by step approach to transform your searches to a performing one - look at each step what's available in the field list and then do the transformation on the fields.

For e.g. from the second section of SPL, your fields are restricted to mb_free, perc_free . _time, host, instance by the stats command at line 17. So the eval operations you are doing from 24 onwards until 32 will not have any effect or they don't have the fields on which they are supposed to be acted upon. Again on line number 33, you are filtering out the fields by using chart first(data) by host type to first(data),host,type.

So suggest you to do the restructuring in a step by step method , confirming the field and data availability at each step.

[jip31]

Hi Renjith

I begin to understand ut it's not easy
I have done this :
(index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space") OR (index="windows-wmi" sourcetype="WMI:LastLogon") OR (index="windows-wmi" sourcetype="WMI:LastReboot" LastBootUpTime) OR (index="windows-wmi" sourcetype="wmi:MemorySize") OR (earliest=-120d index=windows sourcetype=winregistry key_path="\registry\machine\software\wow6432node\xx\master\PatchLevel"
OR
key_path="\registry\machine\software\wow6432node\xx\master\WindowsVersion"
OR
key_path="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId"
) OR (index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode) OR (index=windows sourcetype=tools:flags filename=ABDM-TOUPDATE)
| inputlookup append=t NZDL-Out.csv
| inputlookup append=t NZDL.csv

| eval perc_free = if(counter="% Free Space",Value,null) 
| eval mb_free = if(counter="Free Megabytes",Value,null) 
| eval total_space = mb_free / (perc_free) * 100 
| eval diff=round((total_space-mb_free)/1024,2) 
| eval DiskSize = round(mb_free/1024,2)." MB / ".round(total_space/1024,2)." MB"


| rex field=LastLogon mode=sed "s/\..*$//" 
| eval LastLogon = strftime(strptime(LastLogon,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")

| rex field=LastBootUpTime mode=sed "s/\..*$//" 
| eval LastBootUpTime = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")
| eval MemorySize =round(TotalPhysicalMemory/1024000000, 0). " GB"
| eval filename=if(filename=="ABDM-TOUPDATE.$w$", "Yes", "No")| rename ConfigManagerErrorCode as ErrorCode 
| lookup  Availability.csv ErrorCode 

| stats latest(mb_free) as mb_free latest(perc_free) as perc_free latest(diff) as diff latest(LastLogon) as LastLogon latest(LastBootUpTime) as LastBootUpTime latest(MemorySize) as MemorySize latest(filename) as filename first(data) as data  by host

I am able to collect some data but I encounter a lot of issues
For example :
1 - In my report I need to display the data in | inputlookup append=t NZDL.csv but I don't succeed
2 - I am able to stats mb_free and perc_free | stats latest(mb_free) as mb_free latest(perc_free) as perc_free latest(diff) but I'm not able to stats disksize latest(disksize) as disksize
3 - I am able to stats one registry key path but for the other key it doesn' t works because data is the same field name for the 3 fields
Could you help me please???

[renjith_nair]

1. | inputlookup append=t NZDL.csv , try executing it as a separate search and see if it could fetch it.
2. try eval DiskSize=round((mb_free/total_space)/1024,2)." MB"
3. You probably need to split (by clause) also on the key

[renjith_nair]

1. Remove the stats command and check if the field is displayed with values and if its displayed , check values(DiskSize ) and see if you are able to get those. Field name is case sensitive
2. I dont see key in your current stats. What you want to do with the key ?

[jip31]

1 - OK I m going to test this
2 - I done this - It works in a request apart but in my global code it says "Error in Eval" An idea please??
index=windows sourcetype=winregistry key_path="\\registry\\machine\\software\\wow6432node\\XX\\master\\ConfigurationCountry" OR key_path="\\registry\\machine\\software\\wow6432node\\XX\\master\\WindowsVersion" OR key_path="\\registry\\machine\\software\\microsoft\\windows nt\\currentversion\\ReleaseId" | eval OS=if(key_path=="\\registry\\machine\\software\\wow6432node\\xx\\master\\WindowsVersion",data, null), NATCO=if(key_path=="\\registry\\machine\\software\\wow6432node\\xx\\master\\ConfigurationCountry",data,null), Build=if(key_path=="\\registry\\machine\\software\\microsoft\\windows nt\\currentversion\\ReleaseId",data,null)|stats values(NATCO) as NATCO, values(OS) as OS values(Build) as Build by host

[renjith_nair]

Below works for me,

|makeresults|eval keys="\\registry\\machine\\software\\wow6432node\\XX\\master\\ConfigurationCountry,\\registry\\machine\\software\\wow6432node\\XX\\master\\WindowsVersion,\\registry\\machine\\software\\microsoft\\windows nt\\currentversion\\ReleaseId"|makemv delim="," keys|mvexpand keys
|eval COMMENTS="Until this, its just a dummy data creation and actual eval statements are below"
|eval OS=if(keys="\registry\machine\software\wow6432node\XX\master\WindowsVersion","OS", null),
      NATCO=if(keys="\registry\machine\software\wow6432node\XX\master\ConfigurationCountry","NATCO",null),
      BUILD=if(keys="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId","BUILD",null)
|fields - COMMENTS
|stats values(NATCO) as NATCO, values(OS) as OS values(BUILD) as BUILD

[jip31]

Hi Renjith

I use your example in order to adapt the concerned piece of my code. Thanks.
But I have many other little bugs with my request
Please find below the entire request:

(index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space") OR (index="windows-wmi" sourcetype="WMI:LastLogon") OR (index="windows-wmi" sourcetype="WMI:LastReboot" LastBootUpTime) OR (index="windows-wmi" sourcetype="wmi:MemorySize") OR index=windows sourcetype=winregistry key_path="\registry\machine\software\wow6432node\xx\master\ConfigurationCountry" OR key_path="\registry\machine\software\wow6432node\xx\master\WindowsVersion" OR key_path="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId" OR (index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode) OR (index=windows sourcetype=tools:flags filename=ABDM-TOUPDATE)

| join type=outer host [inputlookup append=t NZDL.csv]
| lookup VideoSignalStatus.csv ConfigManagerErrorCode

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

| eval MBfree_space = if(counter="Free Megabytes",Value,null)
| eval Percfree_space = if(counter="% Free Space",Value,null)

| eval FreeSpace = round(MBfree_space/1024,2)
| eval TotalSpace = round(((100-Percfree_space)*FreeSpace)+FreeSpace,2)
| eval Disk = FreeSpace."MB". "/". TotalSpace."MB"

| rex field=LastLogon mode=sed "s/..*$//"
| eval LastLogon = strftime(strptime(LastLogon,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")

| rex field=LastBootUpTime mode=sed "s/..*$//"
| eval LastBootUpTime = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")

| eval MemorySize =round(TotalPhysicalMemory/1024000000, 0). " GB"

| eval OS=if(key_path=="\registry\machine\software\wow6432node\xx\master\WindowsVersion",data, null), NATCO=if(key_path=="\registry\machine\software\wow6432node\xx\master\ConfigurationCountry",data,null), Build=if(key_path=="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId",data,null)

| eval filename=if(filename=="ABDM-TOUPDATE.$w$", "Yes", "No")

| stats latest(Online) as Online latest(Disk) as Disk latest(diff) as diff latest(LastLogon) as LastLogon latest(LastBootUpTime) as LastReBoot latest(MemorySize) as MemorySize latest(Meaning) as VideoSignal latest(filename) as StayAwake values(NATCO) as NATCO values(OS) as OS values(Build) as Build by host, Building, Floor, Gate

As you can see I use a CSV file in input
The CSV file is available here: https://www.cjoint.com/c/HHpjFjeDuEL

BUGS :
1) When i use | join type=outer host [inputlookup append=t NZDL.csv], I display all the fields informations but just for my host. If I replace it by | inputlookup append=t NZDL.csv I display all the host but I have no fields information. How to do for having all the field information’s for all the host please?
2) I am not able to display values with this :
| eval MBfree_space = if(counter="Free Megabytes",Value,null)
| eval Percfree_space = if(counter="% Free Space",Value,null)

| eval FreeSpace = round(MBfree_space/1024,2)
| eval TotalSpace = round(((100-Percfree_space)*FreeSpace)+FreeSpace,2)
| eval Disk = FreeSpace."MB". "/". TotalSpace."MB"
An idea please?
3) With this piece of code | eval filename=if(filename=="ABDM-TOUPDATE.$w$", "Yes", "No"), I display always “No” even if I have the filename

Many thanks again, I am a SPLUNK rookie (4 mounths) and I need sometimes expertise in order to improve myself 😉

[renjith_nair]

@jip31,
No worries. Only constraint is we have to assume/simulate whole events which might not be accurate. Anyway, lets try

1. What do you want to achieve here? If i have a lookup, I normally try to match my events with the information in lookup. So | join type=outer host [inputlookup append=t NZDL.csv should work for you. Do you mean to say that you want to display all information from NZDL regardless of any match in your events ? If your events are just a subset of information from your csv, then you can reverse the order of join, i.e. |inputlookup NZDL |join "your event search]

2. This is because , you have MBfree_space and Percfree_space in alternative rows because of if conditions. If you are always looking for C; drive, then add
   | eventstats first(MBfree_space) as MBfree_space,first(Percfree_space) as Percfree_space after the first two evals, i.e.

   | eval MBfree_space = if(counter="Free Megabytes",Value,null)
   | eval Percfree_space = if(counter="% Free Space",Value,null)
   | eventstats first(MBfree_space) as MBfree_space,first(Percfree_space) as Percfree_space
   | eval FreeSpace = round(MBfree_space/1024,2)
   | eval TotalSpace = round(((100-Percfree_space)*FreeSpace)+FreeSpace,2)
   | eval Disk = FreeSpace."MB". "/". TotalSpace."MB"
   | table MBfree_space,Percfree_space,FreeSpace,TotalSpace,Disk

3. Try escaping the $ or use | eval filename=if(match(filename,"ABDM-TOUPDATE.\$w\$"),"Y","N")

[jip31]

Renjith

1) In fact, I need to recover the host, Building, Floor and Gate fields from the CSV file and to cross these fields with my different searches
So you can see the result in the capture below: https://www.cjoint.com/c/HHqeonkqutk
This capture comes from the original code I have done (you can see it in annex) but this code uses too many “join” search so I need to do a cleaner request following SPLUNK best practices. What I try to do now….

2) I have to put “| eventstats first(MBfree_space) as MBfree_space,first(Percfree_space) as Percfree_space” just after this?

| eval MBfree_space = if(counter="Free Megabytes",Value,null)
| eval Percfree_space = if(counter="% Free Space",Value,null)

| eval FreeSpace = round(MBfree_space/1024,2)
| eval TotalSpace = round(((100-Percfree_space)*FreeSpace)+FreeSpace,2)
| eval Disk = FreeSpace."MB". "/". TotalSpace."MB"

3) When I do "index=windows sourcetype=tools:flags filename=ABDM-TOUPDATE | eval filename=if(filename=="ABDM-TOUPDATE.$w$", "Yes", "No") | table host filename" I have well "Yes" instead "No"....
I have also tried with "| eval filename=if(match(filename,"ABDM-TOUPDATE.\$w\$"),"Y","N")" but I have "No" instead "Yes"

Annex

| inputlookup append=t NZDL-Out.csv

| rename ComputerName as host, Online as Ping | search host=$tok_filterhost$
| eval Ping =if(Ping=="True","OK","KO")

| join type=outer host [search index="perfmon" sourcetype="perfmon:logicaldisk" instance=c: counter="Free Megabytes" OR counter="% Free Space"
| search host=$tok_filterhost$
| 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 _time, host, instance
| eval total_space = mb_free / (perc_free) * 100
| eval diff=round((total_space-mb_free)/1024,2)
| eval DiskSize = round(mb_free/1024,2)." MB / ".round(total_space/1024,2)." MB"
| eval time = strftime(_time, "%d-%m-%y %H:%M")
| sort -time]

| 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" LastBootUpTime
| rex field=LastReBootUpTime mode=sed "s/..*$//"
| eval LastBoot = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M")]

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

| join type=outer host [search earliest=-120d index=windows sourcetype=winregistry
key_path="\registry\machine\software\wow6432node\xx\master\PatchLevel"
OR
key_path="\registry\machine\software\wow6432node\xx\master\WindowsVersion"
OR
key_path="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId"
| rex field=key_path "(?\w+)$"
| chart first(data) by host type]

| join type=outer host [search index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode
| rename ConfigManagerErrorCode as ErrorCode
| lookup Availability.csv ErrorCode
| table host ErrorCode Meaning]

| join type=outer host [inputlookup append=t NZDL.csv]

| join type=outer host [search index=windows sourcetype=tools:flags filename=ABDM-TOUPDATE
| eval filename=if(filename=="ABDM-TOUPDATE.$$w$$", "Yes", "No")]

| table host, Building, Floor, Gate, Ping, Meaning, DiskSize, diff, MemorySize, PatchLevel, LastLogon, LastBoot, WindowsVersion, ReleaseId, filename
| rename Meaning as Video, ReleaseId as Build
| sort +host
| dedup host

[jip31]

For the point 2 it works now!! thanks a lot!

[jip31]

Renjith
Fin below a synthesis on the remaining issues :
1) I have no value in my report for the field "Meaning"
But when i do "index="windows-wmi" sourcetype="WMI:PeriphIssue" Caption=Mobile ConfigManagerErrorCode | lookup VideoSignalStatus.csv ConfigManagerErrorCode | table host ConfigManagerErrorCode Meaning" apart, I have a value....

2) Same thing for the field "LastBootUpTime". I have no value but when I do "index="windows-wmi" sourcetype="WMI:LastReboot" LastBootUpTime| rex field=LastBootUpTime mode=sed "s/..*$//"
| eval LastBootUpTime = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M") | table host LastBootUpTime | dedup host" I have value....

3) When I do "index=windows sourcetype=tools:flags filename=ABDM-TOUPDATE | eval filename=if(filename=="ABDM-TOUPDATE.$w$", "Yes", "No") | table host filename" I have well "Yes" instead "No"....
I have also tried with "| eval filename=if(match(filename,"ABDM-TOUPDATE.\$w\$"),"Y","N")" but I have "No" instead "Yes"

[jip31]

No idea please?

[jip31]

hi renijith
1 ok now it works 🙂
2 it doesnt works
3 you mean something like this ??:
OR
(key_path="\registry\machine\software\microsoft\windows nt\currentversion\ReleaseId", "ReleaseId", null) | stats first(ReleaseId) as ReleaseId

[jip31]

nobody for helping me please