topic Re: join two queries depends on common fields in Splunk Search

join two queries depends on common fields

sarit_s — Wed, 15 May 2019 07:22:32 GMT

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

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 09:26:41 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 10:34:29 GMT

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

Re: join two queries depends on common fields

DavidHourani — Wed, 30 Sep 2020 00:34:23 GMT

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 ))

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 10:42:37 GMT

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

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 10:44:47 GMT

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.

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 10:46:44 GMT

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"

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 10:47:55 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 10:49:51 GMT

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"

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 10:53:59 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 10:58:24 GMT

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

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 11:00:38 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 11:08:28 GMT

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

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 11:15:43 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 11:20:02 GMT

still no results 😞

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 11:24:16 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 11:29:11 GMT

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"

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 11:36:28 GMT

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)

Re: join two queries depends on common fields

DavidHourani — Wed, 15 May 2019 11:43:06 GMT

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

Re: join two queries depends on common fields

sarit_s — Wed, 15 May 2019 11:49:31 GMT

no results..