Highlighted
Frequent Contributor.. Frequent Contributor..
Frequent Contributor..
747 views

Case-insensitive query result on logger

Jump to solution

Hi all

Is there a way to build report queries in case-insensitive format? It is already case-insensitive in search but I cannot do it in report queries.

Thank you in advance

Regards

Labels (1)
0 Likes
Reply
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

Hi Erdem,

It is quite simple. Let me take a example.

You want a report off all the logs from a server given by you while running the report. So the server name is not fixed here, it is based on the value provided by you when you go the "run"  option in the report. In this case what we need to do,

  • Go to Reports--> Parameters

  • Create a new parameter.
  • Enter the name to be used in sql query. Let us take "deviceip".
  • Enter the prompt name to be displayed while running the report. Choose this in such a way that when somebody else run the report (i.e. operator/analyst) he/she should understand that what value is being asked for. so let us enter "Enter Device IP". Now there are multiple options to choose.

          How to run time paramete should appear in the report while running. In the form of text box, combo or option. Please refer help file for details.

  • Let us choose "Text box" and side window options are grayed out.

  • Now save it.

  • Go to your sql query and simply put the condition in the where clause,

  • WHERE events.arc_deviceAddress = <%deviceip%>

  • Save it. Go to the report, click on run and you will see a text box prompting you 'Enter Device IP'. Enter a device ip from which you want logs. Same will be assigned to <%deviceip%> and compared with the left side field. If matches. you will get the logs.

Hope it helps.

View solution in original post

0 Likes
Reply
8 Replies
Highlighted
Visitor.

Erdem,

me too searching for this..culdnt get yet..if u got any idea, pls share..

Here a workaround, which might be helpful.

:- convert both LHS and RHS to upper or lower cases.

eg: select field1, field2
    from events
    where upper(fieldname) like '%STRING_IN_UPPER_CASE%'

OR  where lower(fieldname) like '%string_in_lower_case%'

0 Likes
Reply
Highlighted
Absent Member.
Absent Member.

@zarysh, I believe this is the only method available in logger as for built-in queries also they use the same method.

0 Likes
Reply
Highlighted
Frequent Contributor.. Frequent Contributor..
Frequent Contributor..

Thank you guys,

Yes this method works, still I will be looking for a constant solution. It sounds nonsense to me since SQL itself can make search in case-insensitive manner.

Regards

0 Likes
Reply
Highlighted
Visitor.

@Amit,

but, wat to do in the case of compairing a field and a custom parameter.

I tried below condition, which is not working.

codn:

WHERE  (upper(events.arc_sourceUserName) like 'upper(<%inputName%>)'

%inputName% is a parameter,

0 Likes
Reply
Highlighted
Frequent Contributor.. Frequent Contributor..
Frequent Contributor..

Hi Zarysh

I am quite new on logger, so I did not know if I can use parameters in logger queries. Could you please give me a little example of this usage? Thank you so much in advance.

0 Likes
Reply
Highlighted
Absent Member.
Absent Member.

Hi Erdem,

It is quite simple. Let me take a example.

You want a report off all the logs from a server given by you while running the report. So the server name is not fixed here, it is based on the value provided by you when you go the "run"  option in the report. In this case what we need to do,

  • Go to Reports--> Parameters

  • Create a new parameter.
  • Enter the name to be used in sql query. Let us take "deviceip".
  • Enter the prompt name to be displayed while running the report. Choose this in such a way that when somebody else run the report (i.e. operator/analyst) he/she should understand that what value is being asked for. so let us enter "Enter Device IP". Now there are multiple options to choose.

          How to run time paramete should appear in the report while running. In the form of text box, combo or option. Please refer help file for details.

  • Let us choose "Text box" and side window options are grayed out.

  • Now save it.

  • Go to your sql query and simply put the condition in the where clause,

  • WHERE events.arc_deviceAddress = <%deviceip%>

  • Save it. Go to the report, click on run and you will see a text box prompting you 'Enter Device IP'. Enter a device ip from which you want logs. Same will be assigned to <%deviceip%> and compared with the left side field. If matches. you will get the logs.

Hope it helps.

View solution in original post

0 Likes
Reply
Highlighted
Absent Member.
Absent Member.

zarysh,

I will check for the condition as you tried and update you.

But one thought.

We need the case sensitiveness in the query for events because we do not have control over the particular field whether it will contain the value in lower case or upper case. That is why we need to explicitly mention in our query that compare with only specific case ( Since the facility is not available, we are using Upper function).

Now coming to the run time. Here if we are choosing text box and assigning value, then we can simply provide the value in the case as we need either in upper or lower. We have control over this. In the case of combo, we can simply list all the possible values in the case as we need.

So I really don't see any requirement to use case sensitiveness with custom parameter.

Tell me if I am making some sense and correct me if I am wrong.

0 Likes
Reply
Highlighted
Frequent Contributor.. Frequent Contributor..
Frequent Contributor..

Thank you so much Amit indeed. This was what I did not know about logger.

By the way you are right since we use the UPPER function in the query and enter the parameter in uppercase while running the report, it becomes case-insensitive.

Thanks again

0 Likes
Reply
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.