Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

join two queries depends on common fields

sarit_s
Communicator
‎05-15-2019 12:22 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 02:26 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 03:34 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 03:38 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 03:42 AM

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

0 Karma
Reply

DavidHourani
Super Champion
‎05-15-2019 03:44 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 03:46 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 03:47 AM

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

0 Karma
Reply

sarit_s
Communicator
‎05-15-2019 03:49 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 03:53 AM

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

0 Karma
Reply

sarit_s
Communicator
‎05-15-2019 03:58 AM

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

0 Karma
Reply

DavidHourani
Super Champion
‎05-15-2019 04:00 AM

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

0 Karma
Reply

sarit_s
Communicator
‎05-15-2019 04:08 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 04:15 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 04:20 AM

still no results 😞

0 Karma
Reply

DavidHourani
Super Champion
‎05-15-2019 04:24 AM

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

0 Karma
Reply

sarit_s
Communicator
‎05-15-2019 04:29 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 04:36 AM

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
Reply

DavidHourani
Super Champion
‎05-15-2019 04:43 AM

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
Reply

sarit_s
Communicator
‎05-15-2019 04:49 AM

no results..

0 Karma
Reply
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...