I have a simple query like below, where I am looking for tickets created by a group of people and then passing it to a chart for visualization.
sourcetype=incident (openedBy="user1" OR openedBy="user2" OR openedBy="user3" OR openedBy="user4" OR openedBy="user5" OR openedBy="user6" OR openedBy="user7") | chart count over assignmentGroup by status
I would like to know if I can use some feature in splunk where I can store the complete openedBy part of the query and call it in my search.
Please help..
Does the column name in myopenedByList.csv is openedBy? Do the values match exactly (no extra space before or after)?
Another option would be (in case list of openedBy user list is small) to create search macro to store your OR clause.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/Knowledge/Definesearchmacros
Macro name: openedByFilter
Macro definition: (openedBy="user1" OR openedBy="user2" OR openedBy="user3" OR openedBy="user4" OR openedBy="user5" OR openedBy="user6" OR openedBy="user7")
Updated search:
sourcetype=incident `openedByFilter` | chart count over assignmentGroup by status
Thank You somesonei2
Tried macro and it worked.!!
Regarding the inputcsv approach, I do not have any column in the csv file. As suggested in the answer, I created the .csv file as below.
|makeresults
2. | eval OpenedBy="user1 user2 user3 user4 user5 user6 user7"
3. | makemv OpenedBy
4. | mvexpand OpenedBy
5. | table OpenedBy
6. | outputcsv myOpenedByList.csv
If you used the above query to generate the lookukp, it should've a column name openedBy. Try running following and paste the result with header here.
| inputlookup LookupDefinitionYouCreated
|makeresults↕
|eval openedBy="rh1h mea7 esrs"
| makemv openedBy
| mvexpand openedBy
| table openedBy
| outputcsv myopenedByList.csv
This is the result am getting.. The first row is showing up as column.
Sorry I meant first row is showing up as header.. 😞
Does the column name in myopenedByList.csv is openedBy? Do the values match exactly (no extra space before or after)?
Another option would be (in case list of openedBy user list is small) to create search macro to store your OR clause.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/Knowledge/Definesearchmacros
Macro name: openedByFilter
Macro definition: (openedBy="user1" OR openedBy="user2" OR openedBy="user3" OR openedBy="user4" OR openedBy="user5" OR openedBy="user6" OR openedBy="user7")
Updated search:
sourcetype=incident `openedByFilter` | chart count over assignmentGroup by status
Macro approach suggested by somesoni2 worked for me.
I've moved somesoni2's comment to answer. Please accept it if your problem is solved.
Thank You for the reply...!
Looks like I am missing something here... Below is what I did as per the answer posted. Let me know if am missing something.
But am getting "No results" as the return. I know there are results to return, because when I do the standard search using "openedBy=user1" am getting the result.
Please suggest if I am missing something here.
Thanks again for your time..!!
1) Put it in a csv file
|makeresults
| eval OpenedBy="user1 user2 user3 user4 user5 user6 user7"
| makemv OpenedBy
| mvexpand OpenedBy
| table OpenedBy
| outputcsv myOpenedByList.csv
2) Read in the csv file inside of braces
sourcetype=incident [ | inputcsv myOpenedByList.csv | table OpenedBy]
| chart count over assignmentGroup by status
Because of the braces, the implicit format command will convert the values to read...
(openedBy="user1" OR openedBy="user2" OR openedBy="user3" OR
openedBy="user4" OR openedBy="user5" OR openedBy="user6" OR
openedBy="user7")
3) To see how that implicit format command works, you can do it explicitly ...
| inputcsv myOpenedByList.csv | table OpenedBy | format
...or, just for fun, even like this ...
| inputcsv myOpenedByList.csv | table OpenedBy | format "(" "squiggle" "squaggle" "argle" "bargle" ")"