Array possibility in Splunk

[vishal9023]

Hello,

I am new to Splunk and wanted to create a dashboard. I have 8 ORs coming through log but the problem is if any OR is missing then its details are not present in log file. I wanted to put status of all ORs whether received or not in tabular form.

You can get an idea what I'm trying to do,
ORs Status
AU YES
NY YES
SI YES
VN YES
TD YES
ID YES
KE NO
JP NO

What I'm able to do is, I'm fetching only those ORs which are present in log file and giving status as "YES" but unable to specify "NO" for those which are not present in log file.
Is there any possibility of arrays in this problem.

Thanks in advance.

[memarshall63 · Accepted solution]

Here's an approach I've used for a similar problem.

Look at this example:

| makeresults 
| eval Possible_ORs="2E,PE,PN,TE,TN,XX,SA,AE" | eval Possible_ORs = split(Possible_ORs,",") | mvexpand Possible_ORs | eval Count=0 | rename Possible_ORs as ORs 
| fields - _time
| append 
    [ | makeresults 
    | eval ORs="AU,NY,PN,TE,AE,XX,ID" | eval ORs = split(ORs,",") | mvexpand ORs 
    | eval Count = 20 | fields - _time ] 
| dedup ORs sortby - Count

So the first 'makeresults' section just sets up a default list of results in your list. In this case, it would be all the possible ORs with a Count of 0. You can add extra default fields as needed. If you have a list of ORs in an index, you can create a 'real' search that just returns the possible ORs and adds a default of 0.

The subsearch after the append is where you put your "real" search to pull back all of the available counts for different ORs that exist in the data. In my example, I just did another 'makeresults' and gave each a count of 20.

So both of these results sets (the original default Count of 0, and the set of real counts) is appended together, and all that's left is to dedup the set. With a 'sortby - Count' clause if the OR appears twice in the set, the dedup will preserve the higher real Count value and drop the one with 0.

[diogofgm]

If you know before have all the possible OPF / ORs you can just create a csv with those an then join them

Example:
save a csv with all possible ORs as a lookup file
or.csv
"or"
"AU"
"NY"
etc..

Then you can use this search:

your base search 
| commands to prep the data
| table or status
| join or type=left [inputlookup or.csv | eval status = "No" ]

This will add the missing Ors with the status of "No"

This approach will allow you to edit just this single csv lookup file if you need to update, add or remove items from the list. The search/spl only based approaches are nice but you'll end up doing a lot more "leg work" and running into the risk of missing some place to change or even messing up your spl in case something changes.

[Sukisen1981]

define a new field for each event
|eval newstatus=if(isnull(or),"null",or)

you can have null or 0 or whatever field value you want to assign to the newstatus field, use this field in place of status.
Take care of the field names and double quotes , if needed in the if condition

[vishal9023]

Hello,

The problem is how will I assign new-status if I didn't got that event?

We have 7 OPF which are fixed i.e. "2E, PE, PN, TE, TN, SA, AE". But if any of the OPF not generated in log file that information also we have put in column and status should be "NO" . I'm extracting OPF through Rex and it will extract only those which are present. It won't give the names of those OPF's which are not in the log file.

I am using like this,

index="test-trail" 
| rex "File\sgeneration\sfor\sMISAOR\sOPF\s(?P<OPF_NAME>[A-Z]{2})File\s(?P<FILE_NAME>A11.*A1.*).txt\scompleted"
| rex "File\sgeneration\sfor\sMISAOR\sOPF\s(?P<OPF_NAME>2E)File\s(?P<FILE_NAME>A11.*A1.*).txt\scompleted"
| rex "File\sgeneration\sfor\sMISAOR\sOPF\s(?P<OPF_NAME>AE)File\s(?P<FILE_NAME>JK.*).txt\scompleted"
| eval Date = strftime(_time, "%Y-%m-%d %H:%M:%S")
| stats count by FILE_NAME Date
| eval name="2E, PE, PN, TE, TN, SA, AE" 
| eval OR_NAME=substr(FILE_NAME, 1, 2)
| eval OPF_NAME=substr(FILE_NAME, 9, 2)
| eval Status=case(count>0, "Yes", count=0, "No")
| fields OR_NAME OPF_NAME FILE_NAME count Date Status name
| rename count as "Files Received"

[Sukisen1981]

| eval OPF_NAME_STATUS=if(isnull(OPF_NAME),"NA",OPF_NAME)
and then while doing a field or table have this
| fields OR_NAME OPF_NAME FILE_NAME count Date Status name,OPF_NAME_STATUS

[Sukisen1981]

you have to ensure that your initial stats do not trim off any events
which i think is reasonable to assume since you count by Date as well which i presume is present in all the events?

[Sukisen1981]

hi @vishal9023
Any progress?