Append command is not working

[uagraw01]

Hello Splunkers!!

I want to combined both the queries by using append but it doesnot work. its always giving me only one section of the results. Please help me to fix it.

(index=si_error source=scada (error_status=CAME_IN OR error_status=WENT_OUT) (_time=Null OR NOT virtual))
| fields - _raw
| fields + area, zone, equipment, element, isc_id, error, error_status, start_time
| search (area="*"), (zone="*"), (equipment="*"), (isc_id="*")
| eval _time=exact(if(isnull(start_time),'_time',max(start_time,earliest_epoch))), _virtual_=if(isnull(virtual),"N","Y"), _cd_=replace('_cd',".*:","")
| sort 0 -_time _virtual_ -"_indextime" -_cd_
| dedup isc_id error _time
| fields - _virtual_, _cd_
| fillnull value="" element
| sort 0 -_time -"_indextime"
| streamstats window=2 global=false current=true earliest(_time) AS start latest(_time) AS stop, count AS count by area zone equipment element error
| search error_status=CAME_IN
| lookup isc id AS isc_id OUTPUTNEW statistical_subject mark_code
| lookup new_ctcl_21_07.csv JoinedAttempt1 AS statistical_subject, mis_address AS error OUTPUTNEW description, operational_rate, technical_rate, alarm_severity
| fillnull value=0 technical_rate operational_rate
| fillnull value="-" alarm_severity mark_code
| eval description=coalesce(description,("Unknown text for error number " . error)), error_description=((error . "-") . description), location=((mark_code . "-") . isc_id), stop=if((count == 1),null,stop), start=exact(coalesce(start_time,'_time')), start_window=max(start,earliest_epoch), stop_window=min(stop,if((latest_epoch > now()),now(),latest_epoch)), duration=round(exact((stop_window - start_window)),3)
| fields + start, error_description, isc_id, duration, stop, mark_code, technical_rate, operational_rate, alarm_severity , area, zone, equipment
| dedup isc_id error_description start
| sort 0 start isc_id error_description asc
| eval operational_rate=(operational_rate * 100), technical_rate=(technical_rate * 100) ,"Start time"= strftime(start,"%d-%m-%Y %H:%M:%S"), "Stop time (within window)"= strftime(stop,"%d-%m-%Y %H:%M:%S"), "Duration (within window)"=tostring(duration,"duration")
| dedup "Start time","Stop time (within window)", isc_id, error_description, mark_code
| search NOT error_description="*Unknown text for error*"
| search technical_rate>* AND operational_rate>* (alarm_severity="*") (mark_code="*")
| rename error_description as "Error ID", isc_id as Location, mark_code as "Mark code", technical_rate as "Technical %", operational_rate as "Operational %", alarm_severity as Severity
| lookup mordc_Av_full_assets.csv Area as area, Zone as zone, Section as equipment output TopoID
| lookup mordc_topo ID as TopoID output Description as Area
| search Area="Depalletizing, Decanting"
| stats count as Scada_count by Area
| table Scada_count

Search 2:

index=internal_statistics_1h
[| inputlookup internal_statistics
| where (step="Defoil and decanting" OR step="Defoil and depalletising")
AND report="Throughput" AND level="step" AND measurement IN("Case")
| fields id
| rename id AS statistic_id]
| eval value=coalesce(value, sum_value)
| fields statistic_id value group_name location
| eval _virtual_=if(isnull(virtual), "N", "Y"), _cd_=replace(_cd, ".*:", "")
| sort 0 -_time _virtual_ -"_indextime" -_cd_
| dedup statistic_id _time group_name
| fields - _virtual_ _cd_
| lookup internal_statistics id AS statistic_id OUTPUTNEW report level step measurement
| stats sum(value) AS dda_count

[uagraw01]

I got the solutions. Thanks all the expertise for involving in this. 

Issue was with the second search which was showing last 30 days only ( before 15th july only )

[yuanliu]

In addition to what @PickleRick already points out, you need to clearly describe your use case - as opposed to throwing complex SPL searches at volunteers if you want to receive concrete help.  What kind of data are in indices si_error and internal_statistics? (Exemplify dataset.) What exactly do you expect as a result of "append" - or any other command? (Expected results.) What is the logic that connects your dataset to expected results?  As @SanjayReddy notes, there is no reason to expect "append" to not "always giving me only one section of the results."  You didn't even show sample results from each search to prove that "append" or any other command should give you more than "only one section of the results."

I want to dig deeper on logic.  @SanjayReddy assumes that you want two fields (columns) Scada_count and dda_count in the same rows.  However, your first search, if it has output, is dominated by | stats count as Scada_count by Area.  This means that if there are more than one values for Area, this search will have more than one row of Scada_count. (If there is ever only one value of Area, why bother group by Area?) On the other hand, the second search can ever only produce one row of dda_count.  What exactly do you expect append to achieve?

[uagraw01]

@yuanliu I want to divide Scada_count/dda_count. This is my use case.

[yuanliu]

---

 I want to divide Scada_count/dda_count. This is my use case.

---

You haven't answered the real question.  To quote myself:

> However, your first search, if it has output, is dominated by | stats count as Scada_count by Area.  This means that if there are more than one values for Area, this search will have more than one row of Scada_count. (If there is ever only one value of Area, why bother group by Area?) On the other hand, the second search can ever only produce one row of dda_count.  What exactly do you expect append to achieve?

Here is another quote:

To ask an answerable data analytics question, follow these golden rules; nay, call them the four commandments:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at).
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

[PickleRick]

Append uses a subsearch. Subsearches have their limits. That's one thing. Most probably, especially since you're doing a lot of funky stuff like sorting, your subsearch simply takes too much time and is silently finalized.

Another thing - those searches are probably suboptimal (don't know your data but they don't seem right in some places). I'm always cautious if I see dedup and too many sortings.

Also - you're listing a bunch of fields

| fields statistic_id value group_name location

Then use a field not listed (and not being a default field like _raw and _time)

| eval _virtual_=if(isnull(virtual), "N", "Y"), _cd_=replace(_cd, ".*:", "")

And you must not use field names beginning with underscore for your own fields - they are reserved for Splunk's internal fields.

[uagraw01]

@PickleRick Hi removed all the highlighted attributes from the query. But still I am not getting any results.

(index=si_error source=scada (error_status=CAME_IN OR error_status=WENT_OUT) (_time=Null OR NOT virtual))
| fields - _raw
| fields + area, zone, equipment, element, isc_id, error, error_status, start_time
| search (area="*"), (zone="*"), (equipment="*"), (isc_id="*")
| eval _time=exact(if(isnull(start_time),'_time',max(start_time,earliest_epoch)))
| dedup isc_id error _time
| fields - _virtual_, _cd_
| fillnull value="" element
| sort 0 -_time -"_indextime"
| streamstats window=2 global=false current=true earliest(_time) AS start latest(_time) AS stop, count AS count by area zone equipment element error
| search error_status=CAME_IN
| lookup isc id AS isc_id OUTPUTNEW statistical_subject mark_code
| lookup new_ctcl_21_07.csv JoinedAttempt1 AS statistical_subject, mis_address AS error OUTPUTNEW description, operational_rate, technical_rate, alarm_severity
| fillnull value=0 technical_rate operational_rate
| fillnull value="-" alarm_severity mark_code
| eval description=coalesce(description,("Unknown text for error number " . error)), error_description=((error . "-") . description), location=((mark_code . "-") . isc_id), stop=if((count == 1),null,stop), start=exact(coalesce(start_time,'_time')), start_window=max(start,earliest_epoch), stop_window=min(stop,if((latest_epoch > now()),now(),latest_epoch)), duration=round(exact((stop_window - start_window)),3)
| fields + start, error_description, isc_id, duration, stop, mark_code, technical_rate, operational_rate, alarm_severity , area, zone, equipment
| dedup isc_id error_description start
| sort 0 start isc_id error_description asc
| search technical_rate>* AND operational_rate>* (alarm_severity="*") (mark_code="*")
| rename isc_id as Location, mark_code as "Mark code", technical_rate as "Technical %", operational_rate as "Operational %", alarm_severity as Severity
| lookup mordc_Av_full_assets.csv Area as area, Zone as zone, Section as equipment output TopoID
| lookup mordc_topo ID as TopoID output Description as Area
| search Area="Depalletizing, Decanting"
| stats count as Scada_count by Area
| table Scada_count
| appendcols
[ search index=internal_statistics_1h
[| inputlookup internal_statistics
| where (step="Defoil and decanting" OR step="Defoil and depalletising")
AND report="Throughput" AND level="step" AND measurement IN("Case")
| fields id
| rename id AS statistic_id]
| eval value=coalesce(value, sum_value)
| fields statistic_id value group_name location
| eval _virtual_=if(isnull(virtual), "N", "Y"), _cd_=replace(_cd, ".*:", "")
| sort 0 -_time _virtual_ -"_indextime" -_cd_
| dedup statistic_id _time group_name
| fields - _virtual_ _cd_
| lookup internal_statistics id AS statistic_id OUTPUTNEW report level step measurement
| stats sum(value) AS dda_count]

Note : While executing both the queries individually. I a, getting the results.

[ITWhisperer]

@uagraw01 Your screen capture is showing a green dot by the Job report; this means there is a message associated with the job. Click on the dropdown to reveal the message. What does it say?

[uagraw01]

@ITWhisperer I am reinitiates my question loop.

Problem : The second sub search is always giving me the repetitive counts. Please guide me to fix it.

Thanks in advance !!

[PickleRick]

1. This is a different problem than the original one. It'd be better if you created a separate thread for it.

2. I suspect you wanted the eval with the subsearch to be evaluated separately for each results row. It doesn't work that way. A subsearch is evaluated first, then its result is substituted into the outer search as a constant. So your outer search effectively has 

| eval dda_count=3251235

(or whatever number you get from the subsearch). So the repeated results are what you asked for.

3. The functionality that _does_ work this way (iterating through results and spawning subsearches for each results row) is the map command.

4. But the map command is hardly ever the right way for the problem. There are very very few use cases for which the map command is the right solution. Usually if you think you should use map it means you need to rethink your approach to the problem.

[ITWhisperer]

And I repeat my question, what does the job report say?

[uagraw01]

Hi @ITWhisperer ,

Firstly thanks for the responses.

Below I am attaching the screenshot of the job detail dashboard for the complete search.

For more information I am splitting both the searches:

First search :

(index=si_error source=scada (error_status=CAME_IN OR error_status=WENT_OUT) (_time=Null OR NOT virtual))
| fields area zone equipment isc_id error error_status start_time
| eval _time=coalesce(start_time, _time)
| lookup isc_mordc id as isc_id OUTPUTNEW mark_code statistical_subject
| lookup isc_areaname.csv isc_id as isc_id OUTPUTNEW area_name cell cluster subsystem
| search subsystem="DEP/*" OR subsystem="DEC/*"
| stats count(subsystem) as scada_count by cell cluster
| table scada_count cell cluster

Second search :

index=internal_statistics_1h
[ | inputlookup internal_statistics
| where report="Throughput" AND level="step" AND measurement="Case"
AND (step="Defoil and decanting" OR step="Defoil and depalletising")
| fields id | rename id AS statistic_id ]
| eval value=coalesce(value, sum_value)
| stats sum(value) AS dda_count
| table dda_count

Complete search :

(index=si_error source=scada (error_status=CAME_IN OR error_status=WENT_OUT) (_time=Null OR NOT virtual))
| fields area zone equipment isc_id error error_status start_time
| eval _time=coalesce(start_time, _time)
| lookup isc_mordc id as isc_id OUTPUTNEW mark_code statistical_subject
| lookup isc_areaname.csv isc_id as isc_id OUTPUTNEW area_name cell cluster subsystem
| search subsystem="DEP/*" OR subsystem="DEC/*"
| stats count(subsystem) as scada_count by cell cluster
| table scada_count cell cluster
| eval dda_count = [
search index=internal_statistics_1h
[ | inputlookup internal_statistics
| where report="Throughput" AND level="step" AND measurement="Case"
AND (step="Defoil and decanting" OR step="Defoil and depalletising")
| fields id | rename id AS statistic_id ]
| eval value=coalesce(value, sum_value)
| stats sum(value) AS dda_count
| return $dda_count
]
| eval Faults = (scada_count/dda_count)*1000
| table cell cluster Faults
| sort cell cluster | chart count(Faults) as Faults

[ITWhisperer]

That is not what I asked for. What do you get when you click on the job drop-down with the green dot?

[SanjayReddy]

Hi @uagraw01 

I just skim thorugh it
can you try appendcols command insteaed of append

let us know if that works