help spl query

[anissabnk]

Hello,

I need some help for a query. I have to do this : 

At the moment I haven't managed to get exactly what I've asked for, I can't place the dates on the last few days in the column, I've tried several things but to no avail.

All I've managed to do is this:

index=aws_app_corp-it_datastage
| spath input=_raw
| eval Country=INVOCATIONID
| eval StartTime=strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q")
| eval EndTime=strptime(RUNENDTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q")
| eval Duration=round(abs(EndTime - StartTime)/60, 2)
| eval Status = case(
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW", "Completed with Warnings",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK", "Successful Launch",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWF", "Failure",
RUNMAJORSTATUS="STA" AND RUNMINORSTATUS="RUN", "In Progress",
1=1, "Unknown"
)
| eval StartTimeFormatted=strftime(StartTime, "%H:%M")
| eval EndTimeFormatted=strftime(EndTime, "%H:%M")
| eval StartTimeDisplay=if(isnotnull(StartTimeFormatted), "Start time: ".StartTimeFormatted, "Start time: N/A")
| eval EndTimeDisplay=if(isnotnull(EndTimeFormatted), "End time: ".EndTimeFormatted, "End time: N/A")
| table JOBNAME PROJECTNAME Country _time StartTimeDisplay EndTimeDisplay Status
| rename JOBNAME as Job, PROJECTNAME as App
| sort -_time
|search Country="*" App="*" Status="*"

[tscroggins]

Here's a straightforward hack that uses a zero width space as a padded value prefix to determine a cell's status. For example, a status of Unknown is one zero width space. The SPL uses the urldecode() eval function to convert URL-encoded UTF-8 characters to strings.

      <table id="table2">
        <search>
          <query>| makeresults format=csv data="
_time,HOSTNAME,PROJECTNAME,JOBNAME,INVOCATIONID,RUNSTARTTIMESTAMP,RUNENDTIMESTAMP,RUNMAJORSTATUS,RUNMINORSTATUS,RUNTYPENAME
2025-01-20 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-19 20:18:25.0,,STA,RUN,Run
2025-01-19 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-18 20:18:25.0,2025-01-18 20:18:29.0,FIN,FWF,Run
2025-01-18 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-17 20:18:25.0,2025-01-17 20:18:29.0,FIN,FOK,Run
2025-01-17 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-16 20:18:25.0,2025-01-16 20:18:29.0,FIN,FWW,Run
2025-01-16 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-15 20:18:25.0,2025-01-15 20:18:29.0,FIN,HUH,Run
"
``` use zero width space as pad ```
| eval status_unknown=urldecode("%E2%80%8B")
| eval status_success=urldecode("%E2%80%8B%E2%80%8B")
| eval status_failure=urldecode("%E2%80%8B%E2%80%8B%E2%80%8B")
| eval status_warning=urldecode("%E2%80%8B%E2%80%8B%E2%80%8B%E2%80%8B")
| eval status_running=urldecode("%E2%80%8B%E2%80%8B%E2%80%8B%E2%80%8B%E2%80%8B")
| eval _time=strptime(_time, "%Y-%m-%d %H:%M:%S.%Q")
| search PROJECTNAME="*" INVOCATIONID="*" RUNMAJORSTATUS="*" RUNMINORSTATUS="*"
| eval status=case(RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW", status_warning, RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK", status_success, RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWF", status_failure, RUNMAJORSTATUS="STA" AND RUNMINORSTATUS="RUN", status_running, 1=1, status_unknown)
| eval tmp=JOBNAME."|".INVOCATIONID
| eval date=strftime(strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%Y-%m-%d")
| eval value=status.if(status==status_unknown, "Unknown", "start time: ".coalesce(strftime(strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), "").urldecode("%0a").if(status==status_running, "Running", "end time: ".coalesce(strftime(strptime(RUNENDTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), "")))
| xyseries tmp date value
| eval tmp=split(tmp, "|"), Job=mvindex(tmp, 0), Country=mvindex(tmp, 1)
| fields - tmp
| table Job Country *</query>
        </search>
        <option name="drilldown">none</option>
        <option name="wrap">true</option>
        <format type="color">
          <colorPalette type="expression">case(match(value, "^\\u200b{1}[^\\u200b]"), "#D3D3D3", match(value, "^\\u200b{2}[^\\u200b]"), "#90EE90", match(value, "^\\u200b{3}[^\\u200b]"), "#F0807F", match(value, "^\\u200b{4}[^\\u200b]"), "#FEEB3C", match(value, "^\\u200b{5}[^\\u200b]"), "#ADD9E6")</colorPalette>
        </format>
      </table>

[tscroggins · Accepted solution]

Hi @anissabnk,

As a quick workaround in a classic dashboard, you can use colorPalette elements with type="expression" to highlight cells if the cell value also includes the status:

<dashboard version="1.1" theme="light">
  <label>anissabnk_table</label>
  <row depends="$hidden$">
    <html>
      <style>
        #table1 th, #table1 td {
          text-align: center !important
        }
      </style>
    </html>
  </row>
  <row>
    <panel>
      <table id="table1">
        <search>
          <query>| makeresults format=csv data="
_time,HOSTNAME,PROJECTNAME,JOBNAME,INVOCATIONID,RUNSTARTTIMESTAMP,RUNENDTIMESTAMP,RUNMAJORSTATUS,RUNMINORSTATUS,RUNTYPENAME
2025-01-20 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-19 20:18:25.0,,STA,RUN,Run
2025-01-19 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-18 20:18:25.0,2025-01-18 20:18:29.0,FIN,FWF,Run
2025-01-18 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-17 20:18:25.0,2025-01-17 20:18:29.0,FIN,FOK,Run
2025-01-17 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-16 20:18:25.0,2025-01-16 20:18:29.0,FIN,FWW,Run
2025-01-16 04:38:04.142,AEW1052ETLLD2,AQUAVISTA_UAT,Jx_104_SALES_ORDER_HEADER_FILE,HES,2025-01-15 20:18:25.0,2025-01-15 20:18:29.0,FIN,HUH,Run
"
| eval _time=strptime(_time, "%Y-%m-%d %H:%M:%S.%Q")
| search PROJECTNAME="*" INVOCATIONID="*" RUNMAJORSTATUS="*" RUNMINORSTATUS="*"
| eval status=case(RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW", "Completed with Warnings", RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK", "Successful Launch", RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWF", "Failure", RUNMAJORSTATUS="STA" AND RUNMINORSTATUS="RUN", "In Progress", 1=1, "Unknown")
| eval tmp=JOBNAME."|".INVOCATIONID
| eval date=strftime(strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%Y-%m-%d")
| eval value=if(status=="Unknown", "Unknown", "start time: ".coalesce(strftime(strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), "").urldecode("%0a").if(status=="In Progress", "Running", "end time: ".coalesce(strftime(strptime(RUNENDTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), ""))).urldecode("%0a").status
| xyseries tmp date value
| eval tmp=split(tmp, "|"), Job=mvindex(tmp, 0), Country=mvindex(tmp, 1)
| fields - tmp
| table Job Country *</query>
        </search>
        <option name="drilldown">none</option>
        <option name="wrap">true</option>
        <format type="color">
          <colorPalette type="expression">case(like(value, "%Unknown"), "#D3D3D3", like(value, "%Successful Launch"), "#90EE90", like(value, "%Failure"), "#F0807F", like(value, "%Completed with Warnings"), "#FEEB3C", like(value, "%In Progress"), "#ADD9E6")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

There may be arcane methods for formatting cells without using JavaScript or including the status in the value, but I don't have them readily available.

[anissabnk]

Hello @tscroggins 

I have a problem with your spl request because some results are truncated,

with your help, i tested this : index=aws_app_corp-it_datastage earliest=-5d@d latest=@d
| spath input=_raw
| search PROJECTNAME="*" INVOCATIONID="*" RUNMAJORSTATUS="*" RUNMINORSTATUS="*"
| eval status=case(
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW", "Completed with Warnings",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK", "Successful Launch",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWF", "Failure",
RUNMAJORSTATUS="STA" AND RUNMINORSTATUS="RUN", "In Progress",
1=1, "Unknown")
| eval tmp=JOBNAME."|".PROJECTNAME."|".INVOCATIONID."|".strftime(_time, "%Y-%m-%d %H:%M:%S")
| eval date=strftime(_time, "%Y-%m-%d")
| eval value=if(status=="Unknown", "Unknown",
"start time: ".coalesce(strftime(strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), "").urldecode("%0a").
if(status=="In Progress", "Running",
"end time: ".coalesce(strftime(strptime(RUNENDTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q"), "%H:%M"), ""))).urldecode("%0a").status
| xyseries tmp date value
| eval tmp=split(tmp, "|"),
Job_Name=mvindex(tmp, 0),
Project_Name=mvindex(tmp, 1),
Geographical_Zone=mvindex(tmp, 2)
| fields - tmp
| table Job_Name Project_Name Geographical_Zone *
|search Geographical_Zone="EMEA" Job_Name="*" Project_Name="*"
| fillnull value="Unknown"

1306 results

With the first request I send you,

index=aws_app_corp-it_datastage earliest=-5d@d latest=@d
| spath input=_raw
| eval StartTime=strptime(RUNSTARTTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q")
| eval EndTime=strptime(RUNENDTIMESTAMP, "%Y-%m-%d %H:%M:%S.%Q")
| eval Date=strftime(_time, "%Y-%m-%d")
| eval Geographical_Zone=INVOCATIONID
| eval Duration=round(abs(EndTime - StartTime)/60, 2)
| eval Status = case(
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW", "Completed with Warnings",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK", "Completed",
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWF", "Failure",
RUNMAJORSTATUS="STA" AND RUNMINORSTATUS="RUN", "In Progress",
1=1, "Unknown")
| eval StartTimeFormatted=strftime(StartTime, "%H:%M:%S.%1N")
| eval EndTimeFormatted=strftime(EndTime, "%H:%M:%S.%1N")
| eval StartTimeDisplay=if(isnotnull(StartTimeFormatted), "Start time: ".StartTimeFormatted, "Start time: N/A")
| eval EndTimeDisplay=if(isnotnull(EndTimeFormatted), "End time: ".EndTimeFormatted, "End time: N/A")
| table JOBNAME PROJECTNAME Geographical_Zone _time Date RUNSTARTTIMESTAMP StartTimeDisplay RUNENDTIMESTAMP EndTimeDisplay Status
| rename JOBNAME as Job_Name, PROJECTNAME as Project_Name
|search Job_Name="*" Geographical_Zone="EMEA" Date="*" Project_Name="*" Status="*"
|sort -Date
| table Job_Name Project_Name Geographical_Zone Date StartTimeDisplay EndTimeDisplay Status
| dedup Job_Name Project_Name Geographical_Zone Date StartTimeDisplay EndTimeDisplay Status

2352 results

so it doesn't work because some failed jobs don't appear, for example

[tscroggins]

I included this:

| search PROJECTNAME="*" INVOCATIONID="*" RUNMAJORSTATUS="*" RUNMINORSTATUS="*"

as a placeholder for filtering using Simple XML inputs.

The most likely cause of the difference in the number of results is one of the fields above not being present after spath extracts fields. In your second search, the events missing from the first search would have Status=="Unknown".

Have you compared the results at the event level to look for differences other than simple truncation?

[anissabnk]

@tscroggins , is there an alternative to using xyseries because the results are limited and the results displayed are therefore truncated? 

[tscroggins]

Hi @anissabnk,

Can you describe what's limited?

@PickleRick showed a value length example. The spath command is limited to the first 5,000 bytes of the event by default. What is your maximum event length from | stats max(eval(len(_raw))) as max_len?

If you meant the number of results, and the xyseries command returns no more than 50,000 results, you may be hitting a limit in an early search command, although I don't see a limited command in your original example.

[PickleRick]

What do you mean? I don't see no truncation.

|makeresults format=csv data="field1,field2,raw_data
1,1,\"Very long string we don't want truncated because we don't think it's necessary or even desired in our particular use case. So we're trying to insert some rubbish text here to make it longer. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enimaad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum\"
1,2,\"Very long string we don't want truncated because we don't think it's necessary or even desired in our particular use case. So we're trying to insert some rubbish text here to make it longer. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enimaad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum\"
2,1,\"Very long string we don't want truncated because we don't think it's necessary or even desired in our particular use case. So we're trying to insert some rubbish text here to make it longer. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enimaad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum\"
2,2,\"Very long string we don't want truncated because we don't think it's necessary or even desired in our particular use case. So we're trying to insert some rubbish text here to make it longer. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enimaad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum\"
"
| xyseries field1 field2 raw_data

[PickleRick]

OK. You can't visualize it like this without additional non-SPL logic (like custom JS in your dashboard).

Apparently the colour of the grid cell depends on another factor (job status) which is not contained within the cell itself.

That's one thing.

Two other things you're facing (but those can be solved with SPL) are:

1) You need to combine two values - start time and end time - into a single string value. Splunk cannot "merge cells" so you need to have a single value for a single grid cell. That's relatively easy. Just use concatenation on two string fields with a "\n" char to split the line in two or combine two values into multivalued field.

2) This is more tricky - you can "wrap" your data set to single days by means of timechart but you can only have one field to split your timechart by. So you can't do this timechart over both job _and_ country. You'd need to firstly combine both job and country into a single field to categorize your jobs, do a timechart over this field and finally split that field back again into two separate fields.

[anissabnk]

please help me adapt my current request

[anissabnk]

someone to help me please on this subject ? ;(

[ITWhisperer]

Please provide some sample events (anonymised appropriately) and a non-SPL description of what you are trying to achieve. It would also help to know what it is about your current search that does not provide the information you require.

[anissabnk]

that a log : 

2025-01-20 04:38:04.142, HOSTNAME="AEW1052ETLLD2", PROJECTNAME="AQUAVISTA_UAT", JOBNAME="Jx_104_SALES_ORDER_HEADER_FILE", INVOCATIONID="HES", RUNSTARTTIMESTAMP="2025-01-19 20:18:25.0", RUNENDTIMESTAMP="2025-01-19 20:18:29.0", RUNMAJORSTATUS="FIN", RUNMINORSTATUS="FWW", RUNTYPENAME="Run"

[ITWhisperer]

It is still not clear what data you are dealing with. For example, does each job run at most once for each app in each country each day? Which day do you want to use, the day from _time or the day from the RUNSTARTTIMESTAMP or the day from the RUNENDTIMESTAMP? Your original table doesn't show App, is this not required?

Please provide a mock up of your expected results using events like the one you have shared. Also, please explain how the data in the events is related to the expected results.

[anissabnk]

-> "Completed with Warnings"
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FWW"
-> "Successful Launch"
RUNMAJORSTATUS="FIN" AND RUNMINORSTATUS="FOK"
-> "Failure"
RUNMAJORSTATUS="FIN"   AND RUNMINORSTATUS="FWF"
-> "In Progress"
RUNMAJORSTATUS="STA"   AND RUNMINORSTATUS="RUN"

[ITWhisperer]

This I can work out from your case function in your SPL. What I can't work out is what you want your results to look like based on the sample data you have shared. The first graphic bears only passing resemblance to the data you have shown. Please try and explain what you are trying to do.

[anissabnk]

I'm trying to represent this:

,but I can't quite do it.

I can't manage to display the last 5 days before the current date in a column.

I've managed to do this, but I'd have to manage to extract the current date automatically via _time, and place it in a column, and have the start time and end time values in these columns:

[ITWhisperer]

Using the data in the second picture, please show how you want it displayed in the layout of the first picture. It is not clear what the relationship between the two sets of data is.