Splunk Search

How to create a dashboard of XML file?

ns102
Explorer

Hi,

I have the following event (XML) in Splunk, how can I create a dashboard of this XML?

<JOB
APPLICATION="AFT-DTA"
CREATION_DATE="20191119"
JOBNAME="T-JOBA"
NODEID="10067"
<VARIABLE NAME="%%FTP-ACCOUNT" VALUE="FIC+DBD"/>
<VARIABLE NAME="%%FTP-LOSTYPE" VALUE="Unix"/>
<VARIABLE NAME="%%FTP-CONNTYPE1" VALUE="SFTP"/>
<VARIABLE NAME="%%FTP-LHOST" VALUE="61021"/>
<VARIABLE NAME="%%FTP-LUSER" VALUE="aft"/>
<VARIABLE NAME="%%FTP-ROSTYPE" VALUE="Windows"/>
<VARIABLE NAME="%%FTP-RHOST" VALUE="dbd7006"/>
<VARIABLE NAME="%%FTP-RUSER" VALUE="aftp"/>
<VARIABLE NAME="%%FTP-LPATH1" VALUE="DIRA"/>
<VARIABLE NAME="%%FTP-RPATH1" VALUE="DIRB"/>
</JOB>
<JOB
APPLICATION="AFT-DTA"
CREATION_DATE="20200113"
JOBNAME="A-JOBB"
NODEID="10007"
<VARIABLE NAME="%%FTP-ACCOUNT" VALUE="SDP+FIC"/>
<VARIABLE NAME="%%FTP-LOSTYPE" VALUE="Unix"/>
<VARIABLE NAME="%%FTP-CONNTYPE1" VALUE="SFTP"/>
<VARIABLE NAME="%%FTP-LHOST" VALUE="sdp9009"/>
<VARIABLE NAME="%%FTP-LUSER" VALUE="aftp"/>
<VARIABLE NAME="%%FTP-ROSTYPE" VALUE="Unix"/>
<VARIABLE NAME="%%FTP-CONNTYPE2" VALUE="SFTP"/>
<VARIABLE NAME="%%FTP-RHOST" VALUE="61021"/>
<VARIABLE NAME="%%FTP-RUSER" VALUE="aft"/>
<VARIABLE NAME="%%FTP-LPATH1" VALUE="DIRA"/>
<VARIABLE NAME="%%FTP-RPATH1" VALUE="DIRB"/>
<VARIABLE NAME="%%FTP-LPATH2" VALUE="DIRC"/>
<VARIABLE NAME="%%FTP-RPATH2" VALUE="DIRD"/>
</JOB>

 

Table should look like:

ENV JOBNAME NODEID LCON LHOST LPATH RCON RHOST RPATH
TST T-JOBA 10067 FIC 61021 DIRA DBD dbd7006 DIRB
ACC A-JOBB 10007 SDP sdp9009

DIRA
DIRC

FIC 61021 DIRB
DIRC

 

Where ENV depends on first letter of JOBNAME

Where LCON is the value of FTP-ACCOUNT" before the + sign.
Where RCON is the value of FTP-ACCOUNT" after the + sign.

LPATH / RPATH can have multiple values where 

Labels (1)
0 Karma
1 Solution

Gr0und_Z3r0
Contributor

Hi @ns102 

To get you started,  here is something you can do to achieve a table of information you want.
There'll be better ways to do it, but this yields what you are after.

This is assuming, you are seeing 2 jobs as a single event. Ideally, each job should be treated as a separate event with its own timestamp. This can be done by updating the sourcetype configuration in props.conf, as part of the data ingestion process.
Secondly, to speed things up I would suggest extracting fields during ingestion period, rather than doing it on search time. 
For the ENV value, I would suggest creating a lookup, that checks the jobname and returns the environment value. Furthermore, you can setup an automatic lookup so that ENV field is already present even during search time. This way you can just update and maintain the lookup values to accommodate more environments and jobs as and when you have.

Also, while building dashboards I would recommend building a base-search and using those to get primary set of information to design panels and get insights out of. It will reduce the number of searches with field extractions and get you results as fast as it could.

 

source="xml.log" host="Beast" sourcetype="test-xml" 
| rex field=_raw "APPLICATION\=\"(?P<app>.*)\"" 
| rex mode=sed "s/[\r\n]+/ /g" 
| rex mode=sed "s/[\%]+//g" 
| eval job=trim(split(_raw,"<JOB")) 
| fields _time job 
| stats values(_time) as _time by job 
| rex field=job "JOBNAME=\"(?P<JOBNAME>[\w\-]+)" 
| rex field=job "NODEID=\"(?P<NODEID>[\w\-]+)" 
| rex field=job "FTP\-ACCOUNT\"\sVALUE\=\"(?P<LCON>[\w]+)\+(?P<RCON>[\w]+)" 
| rex field=job "FTP\-LHOST\"\sVALUE\=\"(?P<LHOST>[\w]+)" 
| rex field=job "FTP\-RHOST\"\sVALUE\=\"(?P<RHOST>[\w]+)" 
| rex field=job "FTP-LPATH1\"\sVALUE\=\"(?P<LPATH1>[\w]+)\"\/\>\s\<VARIABLE\sNAME\=\"FTP-RPATH1\"\sVALUE\=\"(?P<RPATH1>[\w]+)\"\/\>" 
| rex field=job "FTP-LPATH2\"\sVALUE\=\"(?P<LPATH2>[\w]+)\"\/\>\s\<VARIABLE\sNAME\=\"FTP-RPATH2\"\sVALUE\=\"(?P<RPATH2>[\w]+)\"\/\>" 
| fillnull LPATH2,RPATH2 value=null 
| eval LPATH = LPATH1+","+LPATH2 
| eval LPATH =replace(LPATH,",null","") 
| eval RPATH = RPATH1+","+RPATH2 
| eval RPATH =replace(RPATH,",null","") 
| eval ENV=if(JOBNAME="T-JOBA","TST",if(JOBNAME="A-JOBB","ACC","OTHER_ENV")) 
| table ENV JOBNAME NODEID LCON LHOST LPATH RCON RHOST RPATH 
| where isnotnull(JOBNAME)

 

 

Gr0und_Z3r0_0-1680761059846.png

 


~ If the reply helps, an upvote would be appreciated.

 

View solution in original post

woodcock
Esteemed Legend

Your Q is truncated so I could not do everything but this should get you far enough to finish:

| makeresults 
| eval _raw="<JOB
APPLICATION=\"AFT-DTA\"
CREATION_DATE=\"20191119\"
JOBNAME=\"T-JOBA\"
NODEID=\"10067\"
<VARIABLE NAME=\"%%FTP-ACCOUNT\" VALUE=\"FIC+DBD\"/>
<VARIABLE NAME=\"%%FTP-LOSTYPE\" VALUE=\"Unix\"/>
<VARIABLE NAME=\"%%FTP-CONNTYPE1\" VALUE=\"SFTP\"/>
<VARIABLE NAME=\"%%FTP-LHOST\" VALUE=\"61021\"/>
<VARIABLE NAME=\"%%FTP-LUSER\" VALUE=\"aft\"/>
<VARIABLE NAME=\"%%FTP-ROSTYPE\" VALUE=\"Windows\"/>
<VARIABLE NAME=\"%%FTP-RHOST\" VALUE=\"dbd7006\"/>
<VARIABLE NAME=\"%%FTP-RUSER\" VALUE=\"aftp\"/>
<VARIABLE NAME=\"%%FTP-LPATH1\" VALUE=\"DIRA\"/>
<VARIABLE NAME=\"%%FTP-RPATH1\" VALUE=\"DIRB\"/>
</JOB>
<JOB
APPLICATION=\"AFT-DTA\"
CREATION_DATE=\"20200113\"
JOBNAME=\"A-JOBB\"
NODEID=\"10007\"
<VARIABLE NAME=\"%%FTP-ACCOUNT\" VALUE=\"SDP+FIC\"/>
<VARIABLE NAME=\"%%FTP-LOSTYPE\" VALUE=\"Unix\"/>
<VARIABLE NAME=\"%%FTP-CONNTYPE1\" VALUE=\"SFTP\"/>
<VARIABLE NAME=\"%%FTP-LHOST\" VALUE=\"sdp9009\"/>
<VARIABLE NAME=\"%%FTP-LUSER\" VALUE=\"aftp\"/>
<VARIABLE NAME=\"%%FTP-ROSTYPE\" VALUE=\"Unix\"/>
<VARIABLE NAME=\"%%FTP-CONNTYPE2\" VALUE=\"SFTP\"/>
<VARIABLE NAME=\"%%FTP-RHOST\" VALUE=\"61021\"/>
<VARIABLE NAME=\"%%FTP-RUSER\" VALUE=\"aft\"/>
<VARIABLE NAME=\"%%FTP-LPATH1\" VALUE=\"DIRA\"/>
<VARIABLE NAME=\"%%FTP-RPATH1\" VALUE=\"DIRB\"/>
<VARIABLE NAME=\"%%FTP-LPATH2\" VALUE=\"DIRC\"/>
<VARIABLE NAME=\"%%FTP-RPATH2\" VALUE=\"DIRD\"/>
</JOB>"
| rename COMMENT AS "EVERYTHING ABOVE IS CREATING FAKE DATA; EVERYTHING BELOW IS YOUR SOLUTION"
| rex field=_raw mode=sed "s/\<VARIABLE NAME=\"//g s/\"\s+VALUE=/=/g s/\/>//g"
| kv
| rex field=JOBNAME "^(?<ENV>\w+)-(?<JOBNAME>.*)$"
| rex field=FTP_ACCOUNT "^(?<LCON>[^+]+)\+(?<RCON>.*)$"
| eval LPATH="" | foreach FTP_LPATH* [ eval LPATH=mvappend(LPATH, <<FIELD>>) | fields - <<FIELD>> ]
| eval RPATH="" | foreach FTP_RPATH* [ eval RPATH=mvappend(RPATH, <<FIELD>>) | fields - <<FIELD>> ]
| eval LHOST="" | foreach FTP_LHOST* [ eval LHOST=mvappend(LHOST, <<FIELD>>) | fields - <<FIELD>> ]
| eval RHOST="" | foreach FTP_RHOST* [ eval RHOST=mvappend(RHOST, <<FIELD>>) | fields - <<FIELD>> ]
| table ENV JOBNAME NODEID LCON LHOST LPATH RCON RHOST RPATH TST T-JOBA 10067 FIC 61021 DIRA DBD dbd7006 DIRB ACC A-JOBB 10007 SDP sdp9009 *
0 Karma

Gr0und_Z3r0
Contributor

Hi @ns102 

What have you tried so far?

0 Karma

ns102
Explorer

Hi,

Not much 😉 I'm new to Splunk and really struggling to add <VARIABLE NAME> to my output.
At the moment I have the following. So that's quite basic;

 | table DEFTABLE.FOLDER.JOB* |
rename DEFTABLE.FOLDER.JOB{@*} as * |
eval temp=mvzip(JOBNAME,mvzip(NODEID,APPLICATION,"&"),"&")
| table temp
| mvexpand temp
| rex field=temp "(?<JOBNAME>.*)&(?<NODEID>.*)&(?<APPLICATION>.*)" | fields - temp

0 Karma

Gr0und_Z3r0
Contributor

Hi @ns102 

To get you started,  here is something you can do to achieve a table of information you want.
There'll be better ways to do it, but this yields what you are after.

This is assuming, you are seeing 2 jobs as a single event. Ideally, each job should be treated as a separate event with its own timestamp. This can be done by updating the sourcetype configuration in props.conf, as part of the data ingestion process.
Secondly, to speed things up I would suggest extracting fields during ingestion period, rather than doing it on search time. 
For the ENV value, I would suggest creating a lookup, that checks the jobname and returns the environment value. Furthermore, you can setup an automatic lookup so that ENV field is already present even during search time. This way you can just update and maintain the lookup values to accommodate more environments and jobs as and when you have.

Also, while building dashboards I would recommend building a base-search and using those to get primary set of information to design panels and get insights out of. It will reduce the number of searches with field extractions and get you results as fast as it could.

 

source="xml.log" host="Beast" sourcetype="test-xml" 
| rex field=_raw "APPLICATION\=\"(?P<app>.*)\"" 
| rex mode=sed "s/[\r\n]+/ /g" 
| rex mode=sed "s/[\%]+//g" 
| eval job=trim(split(_raw,"<JOB")) 
| fields _time job 
| stats values(_time) as _time by job 
| rex field=job "JOBNAME=\"(?P<JOBNAME>[\w\-]+)" 
| rex field=job "NODEID=\"(?P<NODEID>[\w\-]+)" 
| rex field=job "FTP\-ACCOUNT\"\sVALUE\=\"(?P<LCON>[\w]+)\+(?P<RCON>[\w]+)" 
| rex field=job "FTP\-LHOST\"\sVALUE\=\"(?P<LHOST>[\w]+)" 
| rex field=job "FTP\-RHOST\"\sVALUE\=\"(?P<RHOST>[\w]+)" 
| rex field=job "FTP-LPATH1\"\sVALUE\=\"(?P<LPATH1>[\w]+)\"\/\>\s\<VARIABLE\sNAME\=\"FTP-RPATH1\"\sVALUE\=\"(?P<RPATH1>[\w]+)\"\/\>" 
| rex field=job "FTP-LPATH2\"\sVALUE\=\"(?P<LPATH2>[\w]+)\"\/\>\s\<VARIABLE\sNAME\=\"FTP-RPATH2\"\sVALUE\=\"(?P<RPATH2>[\w]+)\"\/\>" 
| fillnull LPATH2,RPATH2 value=null 
| eval LPATH = LPATH1+","+LPATH2 
| eval LPATH =replace(LPATH,",null","") 
| eval RPATH = RPATH1+","+RPATH2 
| eval RPATH =replace(RPATH,",null","") 
| eval ENV=if(JOBNAME="T-JOBA","TST",if(JOBNAME="A-JOBB","ACC","OTHER_ENV")) 
| table ENV JOBNAME NODEID LCON LHOST LPATH RCON RHOST RPATH 
| where isnotnull(JOBNAME)

 

 

Gr0und_Z3r0_0-1680761059846.png

 


~ If the reply helps, an upvote would be appreciated.

 

ns102
Explorer

Wow, I can really go further with this and when I get more experience with Splunk I will make improvements. for now it's a perfect ! Thank you so much!

LPATH and RPATH are still empty  but that's because the value can also contain / _ and \ characters. hopefully I'll can figure this out.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...