Splunk Search

join two queries depends on common fields

sarit_s
Communicator

Hello
i have this query :

(index=ssys_*_pj OR index=other) NOT source=*Bio_Mimics* 
(Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
(("Serial Number") OR ("Head Type") OR ("Maintenance counter"))
|`SerialNumber`
| search SerialNumber=*
|`Region`
| search Region=*
|`PrinterType`
| rex "Serial Number = (?<HeadSerialNumber>\S+)"
| rex field=HeadSerialNumber mode=sed "s/,//"
| rex "Head Type = (?<HeadType>[\w\ ]+)"
| rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
| rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
| rex "physical Head Index = (?<head>\d+)"
| rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
| rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Head Optimization Wizard: (?<wizard_ended>ended)"
| rex "Head Optimization Wizard: (?<wizard_started>started)"
| rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
| eval startedTime=if(isnotnull(wizard_started),_time,null())
| eval startedSource=if(isnotnull(wizard_started),source,null())
| eval endedTime=if(isnotnull(wizard_ended),_time,null())
| eval endedSource=if(isnotnull(wizard_ended),source,null())
| rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
| eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
| where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
| sort 0 _time 
| streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
| stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
| search HeadSerialNumber=* HeadType=*
| lookup internal_Printers SN as SerialNumber OUTPUT  Type
| eval Internal=if(isnotnull(Type),"T","F")
| table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
| rename Calibrated_voltage as Calibrated_voltage_after_optimization

| join type=left Region SerialNumber MachineMode head 
    [search (index=ssys_*_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel )
| `SerialNumber`
|`Region`
| rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
| eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
| rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
| rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
| stats latest(*) as * max(_time) as headVoltageDate by SerialNumber Region
| table HeadVoltageDate Region SerialNumber WizardMode head*
| eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
| table id head* 
| rename head* as *
| untable id head HeadVoltage
| eval id=split(id,"@")
| eval Region=mvindex(id,0)
| eval SerialNumber=mvindex(id,1)
| eval WizardMode=mvindex(id,2)
| eval HeadVoltageDate=mvindex(id,3)
| table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate]
| convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"

i have few issues with that:
1. there are some cases that i will have SerialNumber that has data from the second search and not from the first one, in that cases the query does not return any result.
2. in the dashboard above this query i have text boxes the their default value is *. I have cases where some SerialNumber does not have value in HeadSerialNumber or HeadType. in that cases the query returns no results. is there a way to give different default value ?
3. i have cases that same SerialNumber appears in more than one PrinterType. in such cases the query returns only one PrinterType and i can see that both PrinterTypes are joined together
4. Join is expensive command. maybe there is a way to replace it with some other command ?
i guess that the join part is the main reason of all the issues i mentioned

thank you very much for your help

Tags (2)
0 Karma

DavidHourani
Super Champion

Hi again @sarit_s,

Avoid using join whenever possible. In your case you want to first call all the data and then combine it using a stats command, that would actually solve all the problems you mentioned here.

Your search should then look something like this :

( (index=ssys_*_pj OR index=other) NOT source=*Bio_Mimics* 
 (Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
 (("Serial Number") OR ("Head Type") OR ("Maintenance counter")) )
OR
((index=ssys_*_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))
|....all you rex and evals go here....
|stats values(valuesyouneed) as valuesyouneed ... by Region SerialNumber MachineMod

First you grab all the events from both parts you want to join, then you make your evals and rex to get ur fields ready, and finally you run a stats by the joining fields and get the values of the fields you need in the table.

Let me know if you got the point.

Cheers,
David

0 Karma

sarit_s
Communicator

Hi @DavidHourani
thanks for all your help 🙂

this is what i did and it returns no results..

 (index=ssys_*_pj OR index=other) NOT source=*Bio_Mimics* 
    (Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
    (("Serial Number") OR ("Head Type") OR ("Maintenance counter"))
    OR
    (index=ssys_*_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel )
    |`SerialNumber`
    | search SerialNumber=IL01-R9JVNZD
    |`Region`
    | search Region=*
    |`PrinterType`
    | rex "Serial Number = (?<HeadSerialNumber>\S+)"
    | rex field=HeadSerialNumber mode=sed "s/,//"
    | rex "Head Type = (?<HeadType>[\w\ ]+)"
    | rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
    | rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
    | rex "physical Head Index = (?<head>\d+)"
    | rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Head Optimization Wizard: (?<wizard_ended>ended)"
    | rex "Head Optimization Wizard: (?<wizard_started>started)"
    | rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
    | eval startedTime=if(isnotnull(wizard_started),_time,null())
    | eval startedSource=if(isnotnull(wizard_started),source,null())
    | eval endedTime=if(isnotnull(wizard_ended),_time,null())
    | eval endedSource=if(isnotnull(wizard_ended),source,null())
    | rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
    | eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
    | where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
    | sort 0 _time 
    | streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
    | stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
    | search HeadSerialNumber=* HeadType=*
    | lookup internal_Printers SN as SerialNumber OUTPUT  Type
    | eval Internal=if(isnotnull(Type),"T","F")
    | table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
    | rename Calibrated_voltage as Calibrated_voltage_after_optimization

    | rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
    | eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
    | rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
    | rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
    | stats latest(*) as * max(_time) as headVoltageDate by SerialNumber Region
    | table HeadVoltageDate Region SerialNumber WizardMode head*
    | eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
    | table id head* 
    | rename head* as *
    | untable id head HeadVoltage
    | eval id=split(id,"@")
    | eval Region=mvindex(id,0)
    | eval SerialNumber=mvindex(id,1)
    | eval WizardMode=mvindex(id,2)
    | eval HeadVoltageDate=mvindex(id,3)
    | table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate
    | convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"
    |stats values(*) as * by Region SerialNumber MachineMod
0 Karma

DavidHourani
Super Champion

Awesome, first make sure your base search gives results. I see you're missing a couple of parentheses, should be like this :
((index=ssys__pj OR index=other) NOT source=*Bio_Mimics
(Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
(("Serial Number") OR ("Head Type") OR ("Maintenance counter")))
OR
( (index=ssys_*_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))

0 Karma

sarit_s
Communicator

the base search gives results.
i think the problem starts after the first table

0 Karma

DavidHourani
Super Champion

this one ? | table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource

Skip using tables are you data is already in tabular format.

0 Karma

sarit_s
Communicator

removed all tables, still nothing.. 😞

  ((index=ssys_pj OR index=other) NOT source=Bio_Mimics 
    (Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
    (("Serial Number") OR ("Head Type") OR ("Maintenance counter")))
    OR
    ( (index=ssys_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))
    |`SerialNumber`
    | search SerialNumber=*
    |`Region`
    | search Region=*
    |`PrinterType`
    | rex "Serial Number = (?<HeadSerialNumber>\S+)"
    | rex field=HeadSerialNumber mode=sed "s/,//"
    | rex "Head Type = (?<HeadType>[\w\ ]+)"
    | rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
    | rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
    | rex "physical Head Index = (?<head>\d+)"
    | rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
    | rex "Head Optimization Wizard: (?<wizard_ended>ended)"
    | rex "Head Optimization Wizard: (?<wizard_started>started)"
    | rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
    | eval startedTime=if(isnotnull(wizard_started),_time,null())
    | eval startedSource=if(isnotnull(wizard_started),source,null())
    | eval endedTime=if(isnotnull(wizard_ended),_time,null())
    | eval endedSource=if(isnotnull(wizard_ended),source,null())
    | rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
    | eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
    | where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
    | sort 0 _time 
    | streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
    | stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
    | search HeadSerialNumber=* HeadType=*
    | lookup internal_Printers SN as SerialNumber OUTPUT  Type
    | eval Internal=if(isnotnull(Type),"T","F")
    | rename Calibrated_voltage as Calibrated_voltage_after_optimization
    | rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
    | eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
    | rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
    | rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
    | stats latest(*) as * max(_time) as headVoltageDate by SerialNumber Region
    | eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
    | table id head* 
    | rename head* as *
    | untable id head HeadVoltage
    | eval id=split(id,"@")
    | eval Region=mvindex(id,0)
    | eval SerialNumber=mvindex(id,1)
    | eval WizardMode=mvindex(id,2)
    | eval HeadVoltageDate=mvindex(id,3)
    |stats values(*) as * by Region SerialNumber  MachineMode head
    | convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"
0 Karma

DavidHourani
Super Champion

can you add your lines one by one to identify where you're losing the results ?

0 Karma

sarit_s
Communicator

until here i have results:

((index=ssys_pj OR index=other) NOT source=Bio_Mimics 
(Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
(("Serial Number") OR ("Head Type") OR ("Maintenance counter")))
OR
( (index=ssys_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))
|`SerialNumber`
| search SerialNumber=*
|`Region`
| search Region=*
|`PrinterType`
| rex "Serial Number = (?<HeadSerialNumber>\S+)"
| rex field=HeadSerialNumber mode=sed "s/,//"
| rex "Head Type = (?<HeadType>[\w\ ]+)"
| rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
| rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
| rex "physical Head Index = (?<head>\d+)"
| rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
| rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Head Optimization Wizard: (?<wizard_ended>ended)"
| rex "Head Optimization Wizard: (?<wizard_started>started)"
| rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
| eval startedTime=if(isnotnull(wizard_started),_time,null())
| eval startedSource=if(isnotnull(wizard_started),source,null())
| eval endedTime=if(isnotnull(wizard_ended),_time,null())
| eval endedSource=if(isnotnull(wizard_ended),source,null())
| rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
| eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
| where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
| sort 0 _time 
| streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
| stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
| search HeadSerialNumber=* HeadType=*
| lookup internal_Printers SN as SerialNumber OUTPUT  Type
| eval Internal=if(isnotnull(Type),"T","F")
| table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
| rename Calibrated_voltage as Calibrated_voltage_after_optimization

| rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
| eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
| rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
| rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
| stats latest(*) as * max(_time) as headVoltageDate by SerialNumber Region

when adding this part i lose data:

| table HeadVoltageDate Region SerialNumber WizardMode head*
| eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
| table id head* 
| rename head* as *
| untable id head HeadVoltage
| eval id=split(id,"@")
| eval Region=mvindex(id,0)
| eval SerialNumber=mvindex(id,1)
| eval WizardMode=mvindex(id,2)
| eval HeadVoltageDate=mvindex(id,3)
| table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate
|stats values(*) as * by Region SerialNumber  MachineMode head
| convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"
0 Karma

DavidHourani
Super Champion

Why are you using the "latest()" in the stats command and no "values()" to maintain all ur values ?

0 Karma

sarit_s
Communicator

since i need to take the last time it appears in the logs

0 Karma

DavidHourani
Super Champion

so it should be : | stats values(*) as * latest(_time) as headVoltageDate by SerialNumber Region

0 Karma

sarit_s
Communicator

this is not the only date field i should take care of. i need to take into account few fields
i need to correlate between the "head" in all the events and combine them so for each head i will have all the relevant data

0 Karma

DavidHourani
Super Champion

ummm in that case stick to this : | stats latest(*) as * by SerialNumber Region you don't need the max(_time) as it's already there in latest(*) as *. check if that give you any results. Then you can rename _time as headVoltageDate

0 Karma

sarit_s
Communicator

still no results 😞

0 Karma

DavidHourani
Super Champion

no results after the stats yeah ? or when you add the other chunk of code ?

0 Karma

sarit_s
Communicator

for all the code

((index=ssys_pj OR index=other) NOT source=Bio_Mimics 
(Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
(("Serial Number") OR ("Head Type") OR ("Maintenance counter")))
OR
( (index=ssys_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))
|`SerialNumber`
| search SerialNumber=*
|`Region`
| search Region=*
|`PrinterType`
| rex "Serial Number = (?<HeadSerialNumber>\S+)"
| rex field=HeadSerialNumber mode=sed "s/,//"
| rex "Head Type = (?<HeadType>[\w\ ]+)"
| rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
| rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
| rex "physical Head Index = (?<head>\d+)"
| rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
| rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Head Optimization Wizard: (?<wizard_ended>ended)"
| rex "Head Optimization Wizard: (?<wizard_started>started)"
| rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
| eval startedTime=if(isnotnull(wizard_started),_time,null())
| eval startedSource=if(isnotnull(wizard_started),source,null())
| eval endedTime=if(isnotnull(wizard_ended),_time,null())
| eval endedSource=if(isnotnull(wizard_ended),source,null())
| rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
| eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
| where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
| sort 0 _time 
| streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
| stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
| search HeadSerialNumber=* HeadType=*
| lookup internal_Printers SN as SerialNumber OUTPUT  Type
| eval Internal=if(isnotnull(Type),"T","F")
| table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
| rename Calibrated_voltage as Calibrated_voltage_after_optimization

| rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
| eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
| rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
| rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
| stats latest(*) as *  by SerialNumber Region
| table HeadVoltageDate Region SerialNumber WizardMode head*
| eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
| table id head* 
| rename head* as *
| untable id head HeadVoltage
| eval id=split(id,"@")
| eval Region=mvindex(id,0)
| eval SerialNumber=mvindex(id,1)
| eval WizardMode=mvindex(id,2)
| eval HeadVoltageDate=mvindex(id,3)
| table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate
|stats values(*) as * by Region SerialNumber  MachineMode head
| convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"
0 Karma

sarit_s
Communicator

i tries also something like this :

(index=ssys_*_pj OR index=other) NOT source=*Bio_Mimics* 
(Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
(("Serial Number") OR ("Head Type") OR ("Maintenance counter"))
|`SerialNumber`
| search SerialNumber=8500519
|`Region`
| search Region=*
|`PrinterType`
| rex "Serial Number = (?<HeadSerialNumber>\S+)"
| rex field=HeadSerialNumber mode=sed "s/,//"
| rex "Head Type = (?<HeadType>[\w\ ]+)"
| rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
| rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
| rex "physical Head Index = (?<head>\d+)"
| rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
| rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
| rex "Head Optimization Wizard: (?<wizard_ended>ended)"
| rex "Head Optimization Wizard: (?<wizard_started>started)"
| rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
| eval startedTime=if(isnotnull(wizard_started),_time,null())
| eval startedSource=if(isnotnull(wizard_started),source,null())
| eval endedTime=if(isnotnull(wizard_ended),_time,null())
| eval endedSource=if(isnotnull(wizard_ended),source,null())
| rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
| eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
| where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
| sort 0 _time 
| streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
| stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
| search HeadSerialNumber=* HeadType=*
| lookup internal_Printers SN as SerialNumber OUTPUT  Type
| eval Internal=if(isnotnull(Type),"T","F")
| table startedTime endedTime Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
| rename Calibrated_voltage as Calibrated_voltage_after_optimization

| append
    [search (index=ssys_*_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel )
| `SerialNumber`
|`Region`
| rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
| eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
| rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
| rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
| stats latest(*) as * max(_time) as headVoltageDate by SerialNumber Region
| table HeadVoltageDate Region SerialNumber WizardMode head*
| eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
| table id head* 
| rename head* as *
| untable id head HeadVoltage
| eval id=split(id,"@")
| eval Region=mvindex(id,0)
| eval SerialNumber=mvindex(id,1)
| eval WizardMode=mvindex(id,2)
| eval HeadVoltageDate=mvindex(id,3)
| stats values(*) as * by  Region SerialNumber MachineMode head
| table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate]

| convert ctime(endedTime) ctime(startedTime) ctime(HeadVoltageDate) timeformat="%F %T"

and it gives me only part of the results. (nothing from the second part of the query)

0 Karma

DavidHourani
Super Champion

does this give you results ?

   ((index=ssys_pj OR index=other) NOT source=Bio_Mimics 
     (Head Optimization Wizard ((started) OR (ended) OR (was selected) OR (num of missing) OR (Save voltage) OR (Support voltage) OR (Model voltage) OR (Target layer Net))) OR
     (("Serial Number") OR ("Head Type") OR ("Maintenance counter")))
     OR
     ( (index=ssys_pj OR index=other) source=*HQ.cfg OR source=*HM.cfg OR source=*HS.cfg AND (RequestedHeadVoltagesSupport OR RequestedHeadVoltagesModel ))
     |`SerialNumber`
     | search SerialNumber=*
     |`Region`
     | search Region=*
     |`PrinterType`
     | rex "Serial Number = (?<HeadSerialNumber>\S+)"
     | rex field=HeadSerialNumber mode=sed "s/,//"
     | rex "Head Type = (?<HeadType>[\w\ ]+)"
     | rex "Head Optimization Wizard: (?<WizardMode>\S+) was selected"
     | rex "Nozzles for head Head (?<head>\d+) is (?<missing_nozzles>\d+)"
     | rex "physical Head Index = (?<head>\d+)"
     | rex "Save voltage (?<Calibrated_voltage>[\d\.]+) for head Head (?<head>\d+)"
     | rex "Support voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
     | rex "Model voltage (?<Voltage_before_optimization>[\d\.]+) for head Head (?<head>\d+)"
     | rex "Head Optimization Wizard: (?<wizard_ended>ended)"
     | rex "Head Optimization Wizard: (?<wizard_started>started)"
     | rex "Target layer Net\s+\=\s+(?<Layer>[\d\.]+)"
     | eval startedTime=if(isnotnull(wizard_started),_time,null())
     | eval startedSource=if(isnotnull(wizard_started),source,null())
     | eval endedTime=if(isnotnull(wizard_ended),_time,null())
     | eval endedSource=if(isnotnull(wizard_ended),source,null())
     | rex "Maintenance counter \"H(?<head>\d+)\" Value is: (?<head_lifetime>\d+)"
     | eventstats max(startedTime) as startedTime max(endedTime) as endedTime by SerialNumber
     | where isnotnull(HeadSerialNumber) OR isnotnull(HeadType) OR (_time>=startedTime AND _time<=endedTime)
     | sort 0 _time 
     | streamstats last(Layer) as Layer  last(WizardMode) as WizardMode last(startedTime) as startedTime last(startedSource) as startedSource last(endedTime) as endedTime last(endedSource) as endedSource by SerialNumber
     | stats last(endedSource) as endedSource last(startedSource) as startedSource max(startedTime) as startedTime max(endedTime) as endedTime last(*) as * by SerialNumber head
     | search HeadSerialNumber=* HeadType=*
     | lookup internal_Printers SN as SerialNumber OUTPUT  Type
     | eval Internal=if(isnotnull(Type),"T","F")
     | table startedTime endedTime Type Region Internal SerialNumber PrinterType head HeadSerialNumber HeadType missing_nozzles HeadVoltage HeadVoltageDate  Voltage_before_optimization Calibrated_voltage head_lifetime Layer WizardMode startedSource endedSource
     | rename Calibrated_voltage as Calibrated_voltage_after_optimization

     | rex field=source "PerMachine_(?<WizardMode>[^\.]+)" 
     | eval WizardMode=if(WizardMode=="HM","HS/HM",WizardMode) 
     | rex "RequestedHeadVoltagesSupport=(?<head0>[^,]+),(?<head1>[^,]+)," 
     | rex "RequestedHeadVoltagesModel=[^,]+,[^,]+,(?<head2>[^,]+),(?<head3>[^,]+),(?<head4>[^,]+),(?<head5>[^,]+),(?<head6>[^,]+),(?<head7>[^,]+)"
     | stats latest(*) as *  by SerialNumber Region
     | table HeadVoltageDate Region SerialNumber WizardMode head*
     | eval id=Region+"@"+SerialNumber+"@"+WizardMode+"@"+headVoltageDate
     | table id head* 
     | rename head* as *
     | untable id head HeadVoltage
     | eval id=split(id,"@")
     | eval Region=mvindex(id,0)
     | eval SerialNumber=mvindex(id,1)
     | eval WizardMode=mvindex(id,2)
     | eval HeadVoltageDate=mvindex(id,3)
     | table  Region SerialNumber WizardMode head HeadVoltage HeadVoltageDate
0 Karma

sarit_s
Communicator

no results..

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...